Description:
MySQL 5.0.51 show global status reporting wrong statistics for query cache. Its most noticeble when starting the server.
This is how the server looks when you first start it. Notice the free_blocks and total_block both = 1, while free memory = 256 MB.
According to qcache settings the total blocks should be = 256MB / 2048 = 131072
mysql>show global status like "%qcache%";
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 268309960 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+
mysql> show global variables like "%cache%";
+------------------------------+----------------------+
| Variable_name | Value |
+------------------------------+----------------------+
| binlog_cache_size | 32768 |
| have_query_cache | YES |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 18446744073709551615 |
| query_cache_limit | 4194304 |
| query_cache_min_res_unit | 2048 |
| query_cache_size | 268435456 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| table_cache | 4096 |
| thread_cache_size | 64 |
+------------------------------+----------------------+
After server been running for a while
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 221735360 |
| Qcache_hits | 48350 |
| Qcache_inserts | 26303 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 32276 |
| Qcache_queries_in_cache | 24251 |
| Qcache_total_blocks | 48602 |
+-------------------------+-----------+
How to repeat:
this is 64 bit MySQL compiled like so :
CXX=gcc \
./configure --prefix=/usr/local/mysql \
--localstatedir=/var/lib/mysql \
--with-unix-socket-path=/var/lib/mysql/mysql.sock \
--enable-thread-safe-client \
--enable-assembler \
--enable-local-infile \
--with-big-tables \
--with-fast-mutexes \
--with-mysqld-ldflags=-static \
--with-extra-charsets=complex \
--with-innodb \
--with-archive-storage-engine \
--with-federated-storage-engine \
--with-blackhole-storage-engine \
