Bug #60668 | qcache variables don't work properly | ||
---|---|---|---|
Submitted: | 27 Mar 2011 21:23 | Modified: | 28 Mar 2011 8:16 |
Reporter: | Radek Antoniuk | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Query Cache | Severity: | S3 (Non-critical) |
Version: | 5.1.49 | OS: | Linux (debian: 5.1.49-3) |
Assigned to: | CPU Architecture: | Any |
[27 Mar 2011 21:23]
Radek Antoniuk
[28 Mar 2011 3:18]
Valeriy Kravchuk
Is your MySQL server 32-bit or 64-bit binary? Check http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_query_cache_siz.... In any case you probably do not really need query cache 4G in size...
[28 Mar 2011 7:55]
Radek Antoniuk
Doesn't matter, it is unexpected behavior though. mysql> show variables like '%ver%'; +-------------------------+------------------+ | Variable_name | Value | +-------------------------+------------------+ | character_set_server | utf8 | | collation_server | utf8_general_ci | | myisam_recover_options | BACKUP | | protocol_version | 10 | | rpl_recovery_rank | 0 | | server_id | 0 | | version | 5.1.49-3-log | | version_comment | (Debian) | | version_compile_machine | i486 | | version_compile_os | debian-linux-gnu | +-------------------------+------------------+ 10 rows in set (0.00 sec) root@db:~# uname -a Linux db 2.6.32-5-686-bigmem #1 SMP Tue Mar 8 22:14:55 UTC 2011 i686 GNU/Linux root@db:~# ldd /usr/sbin/mysqld linux-gate.so.1 => (0xb77c9000) libpthread.so.0 => /lib/i686/cmov/libpthread.so.0 (0xb6f78000) libz.so.1 => /usr/lib/libz.so.1 (0xb6f64000) libwrap.so.0 => /lib/libwrap.so.0 (0xb6f5b000) libdl.so.2 => /lib/i686/cmov/libdl.so.2 (0xb6f57000) libcrypt.so.1 => /lib/i686/cmov/libcrypt.so.1 (0xb6f25000) libnsl.so.1 => /lib/i686/cmov/libnsl.so.1 (0xb6f0e000) libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0xb6e19000) libm.so.6 => /lib/i686/cmov/libm.so.6 (0xb6df2000) libgcc_s.so.1 => /lib/libgcc_s.so.1 (0xb6dd4000) libc.so.6 => /lib/i686/cmov/libc.so.6 (0xb6c8e000) /lib/ld-linux.so.2 (0xb77ca000) ii libc6 2.11.2-10 Embedded GNU C Library: Shared libraries ii libc6-i686 2.11.2-10 Embedded GNU C Library: Shared libraries [i686 optimized] Last processor from /proc/cpuinfo: processor : 7 vendor_id : GenuineIntel cpu family : 6 model : 2 model name : QEMU Virtual CPU version 0.13.0 stepping : 3 cpu MHz : 2400.416 cache size : 4096 KB physical id : 0 siblings : 8 core id : 0 cpu cores : 8 apicid : 0 initial apicid : 0 fdiv_bug : no hlt_bug : no f00f_bug : no coma_bug : no fpu : yes fpu_exception : yes cpuid level : 4 wp : yes flags : fpu de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 ht syscall nx lm pni cx16 popcnt hypervisor lahf_lm bogomips : 4800.83 clflush size : 64 cache_alignment : 64 address sizes : 40 bits physical, 48 bits virtual
[28 Mar 2011 8:08]
Valeriy Kravchuk
It does matter as on 32-bit system (as yours) the range of acceptable values (according to http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_query_cache_siz...) is 0-4294967295. While 4G is: mysql> select 4*1024*1024*1024; +------------------+ | 4*1024*1024*1024 | +------------------+ | 4294967296 | +------------------+ 1 row in set (0.00 sec) So, out of range. This is NOT a bug, but expected and documented result.
[28 Mar 2011 8:16]
Radek Antoniuk
Oh, I see. Is setting 2G not working is also expected? mysql> set global query_cache_size = 2000000000; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+----------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------+ | Warning | 1282 | Query cache failed to set size 2000000000; new query cache size is 0 | +---------+------+----------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> set global query_cache_size = 2*1024*1024*1024; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> set global query_cache_size = 1*1024*1024*1024; Query OK, 0 rows affected, 1 warning (0.00 sec) only <1G works: mysql> set global query_cache_size = 1000000000; Query OK, 0 rows affected (0.00 sec)