Rick James Wrote:
-------------------------------------------------------
> Some wild guesses...
>
> * What is the name of the table?
There were several names over time as I kept copying the table. Some of these included "xxx", "yyy" and something like "alerts_2010_08-01-01".
>
> * How big is the resultset that it decides not to
> cache? Does it refuse to cache even a tiny
> resultset, such as SELECT COUNT(*) WHERE ..., with
> the where clause forces a table scan.
It was always SELECT COUNT(*) without a group clause. So just one row and just one column.
>
> * Are there comments in the SELECT?
>
http://bugs.mysql.com/bug.php?id=55016
No comments.
>
> * Don't know what I might find here:
> SHOW VARIABLES LIKE 'query_%';
> SHOW GLOBAL STATUS LIKE 'Qc_%';
On the server that fails:
mysql> SHOW VARIABLES LIKE 'query_%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+----------+
7 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Qc_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 8 |
| Qcache_free_memory | 65597208 |
| Qcache_hits | 13442486 |
| Qcache_inserts | 22729173 |
| Qcache_lowmem_prunes | 155974 |
| Qcache_not_cached | 16607625 |
| Qcache_queries_in_cache | 1014 |
| Qcache_total_blocks | 2042 |
+-------------------------+----------+
8 rows in set (0.04 sec)
On the server that acts as expected:
mysql> SHOW VARIABLES LIKE 'query_%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+----------+
7 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Qc_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 14 |
| Qcache_free_memory | 418088 |
| Qcache_hits | 18384173 |
| Qcache_inserts | 24511879 |
| Qcache_lowmem_prunes | 771205 |
| Qcache_not_cached | 19530995 |
| Qcache_queries_in_cache | 43193 |
| Qcache_total_blocks | 86413 |
+-------------------------+----------+
8 rows in set (0.00 sec)
I did a slightly simpler test and got the same results. I started with a simple table.
CREATE TABLE `test` (
`id` bigint(20) NOT NULL auto_increment,
`r` double default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6007125 DEFAULT CHARSET=latin1;
I repeated the following statement about 6,000,000 times to fill the table:
insert into test values(null, rand());
Here you can see my test on the bad server. The first flush was right before I called myisampack. The second flush was right after myisamchk -rq finished. The test table has fewer columns than my original, and the tests went faster, but otherwise the results are the same. I didn't bother to look at Qc_% because other things are happening on this server. But it's clear that the query caching is working before the pack, and not working after.
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 6007124 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from test where r < 0.2;
+----------+
| count(*) |
+----------+
| 1200767 |
+----------+
1 row in set (0.71 sec)
mysql> select count(*) from test where r < 0.2;
+----------+
| count(*) |
+----------+
| 1200767 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from test where r < 0.2;
+----------+
| count(*) |
+----------+
| 1200767 |
+----------+
1 row in set (0.00 sec)
mysql> flush tables
-> ;
Query OK, 0 rows affected (0.08 sec)
mysql> flush tables;
Query OK, 0 rows affected (3.55 sec)
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 6007124 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from test where r < 0.2;
+----------+
| count(*) |
+----------+
| 1200767 |
+----------+
1 row in set (1.59 sec)
mysql> select count(*) from test where r < 0.2;
+----------+
| count(*) |
+----------+
| 1200767 |
+----------+
1 row in set (1.57 sec)
mysql> select count(*) from test where r < 0.2;
+----------+
| count(*) |
+----------+
| 1200767 |
+----------+
1 row in set (1.56 sec)