I am running MySQL 5.5.9 x86_64 RPM as downloaded from mysql.com. Running on CentOS 5.5 Xen DomU.
I have enabled the Query_cache however MySQL NEVER uses it. All of my tables are InnoDB. Why is the Qcache never hit?
UPDATE 2: I have found this is limited to schemas with - in the name. Creating a new Schema eg new-db, query cache fails. Unfortunatly I have 148 existing Schama, all with '-' in their names.
UPDATE this appears to be limited to schemas that were dumped and imported from a pervious version of MySQL (5.0.32) Creating a new schema and querying tables in this query cache works as expected.
Here are my settings and examples of Qc working and not working.
mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| have_query_cache | YES |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 536870912 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
6 rows in set (0.00 sec)
mysql> USE `existing-schema`;
Database changed
mysql> CREATE TABLE test (
-> `uid` INT AUTO_INCREMENT PRIMARY KEY,
-> `str` VARCHAR(255) NOT NULL
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW CREATE TABLE test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`str` varchar(255) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO test (str) VALUES ('one'),('two'),('three'),('four');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 536852824 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 56725 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test;
+-----+-------+
| uid | str |
+-----+-------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
+-----+-------+
4 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 536852824 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 89824 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> CREATE DATABASE new;
Query OK, 1 row affected (0.00 sec)
mysql> USE new;
Database changed
mysql> CREATE TABLE test (
-> `uid` INT AUTO_INCREMENT PRIMARY KEY,
-> `str` VARCHAR(255) NOT NULL
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW CREATE TABLE test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`str` varchar(255) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO test (str) VALUES ('one'),('two'),('three'),('four');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 536852824 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 89824 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test;
+-----+-------+
| uid | str |
+-----+-------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
+-----+-------+
4 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 536851288 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 109528 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test;
+-----+-------+
| uid | str |
+-----+-------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
+-----+-------+
4 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 536851288 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 126100 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
mysql> SHOW CREATE DATABASE new;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| new | CREATE DATABASE `new` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> SHOW CREATE DATABASE `existing-schema`;
+------------------+---------------------------------------------------------------------------+
| Database | Create Database |
+------------------+---------------------------------------------------------------------------+
| ezlead-live-data | CREATE DATABASE `existing-schema` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+------------------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '[REMOVED]' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
This is a bug in MySQL. I guess the only solution currently is to rename my database schema, so that they don't contain hyphens.
Your query cache settings look fine. See How the Query Cache Operates for details on what types of SELECT queries won't be cached and see if anything applies to you case. Also check the MySQL error log for any relevant messages.
What you can also do is issue a very simple SELECT query that you know should be cached and check the
Qcache_hitsboth before and after the query. Try creating a small test database/table to exclude any possible issues with your existing tables. If that doesn't work you know something more subtle is wrong with MySQL.