MySQL query cache is enabled but not being used

Question

I've checked the query cache is enabled

mysql> SHOW VARIABLES LIKE 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ 1 row in set (0.00 sec) 

But seems it is not being used

mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16759648 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 21555882 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+ 8 rows in set (0.00 sec) 

Any reason?

Answer 1

The system variable query_cache_type needs to be set to non-zero as well, because the presence of the cache doesn't mean it can be used.

Answer 2

Please also be aware that not all queries are cacheable.

For example, if a query contains function like NOW(), it would not be cached. Please find the detailed description of which queries cannot be cached: https://dev.mysql.com/doc/refman/5.7/en/query-cache-operation.html

Answer 3

You should also check at which position you have written your settings in the configuration file.

My problem was that i placed my qchace settings at the end of the file. Although the settings were reflected by executing mysql> SHOW STATUS LIKE 'Qcache%'; no queries were cached actually.

After placing the settings at a different position in the file and restarting the mysql server it was working.

Here you can see my configuration for MySQL 5.6:

[mysqld] bind-address=* #skip-networking # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock query_cache_type = 1 query_cache_size =256M thread_concurrency=2 sort_buffer_size=25M key_buffer=128M # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid 

Answer 4

There was a MySQL 5.6 bug, fixed in 5.6.9: "if the db name (or table name) has a '-' (minus), it does not work", on InnoDB only.