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:
None 
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
Description:
When I set:
query_cache_size=4G
in conf file then show variable like '%qcache%' show only zeros.
(please see http://forums.mysql.com/read.php?35,414029,414029#msg-414029 for more info)

When I change the line to:
query_cache_size=1G
the counters are working properly.

How to repeat:
set query_cache_size=4G in /etc/mysql/conf.d/myconfig.cnf
[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)