MySQL query cache overview – Enabling and checking status

Enabling query cache

Edit your my.cnf

Below you will find some example values to get you started. (Paste them after the [mysqld] declaration)

query_cache_size    = 96M
tmp_table_size      = 16M
max_heap_table_size = 16M
table_open_cache    = 256
thread_cache_size   = 32
query_cache_limit   = 8M

Now log onto MySQL from CLI:

mysql -u root -p

Check cache settings:

SHOW VARIABLES LIKE 'query%';

Result example:

+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_alloc_block_size       | 8192      |
| query_cache_limit            | 8388608   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 100663296 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
| query_prealloc_size          | 8192      |
+------------------------------+-----------+

The important part here is that query_cache_type is ON and that query_cache_size is not 0.

Check cache performance

To see how the cache is performing (hits/misses and free space), run:

SHOW STATUS LIKE 'Qc%';

Result example:

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 105      |
| Qcache_free_memory      | 98688872 |
| Qcache_hits             | 2946     |
| Qcache_inserts          | 1581     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 13       |
| Qcache_queries_in_cache | 1357     |
| Qcache_total_blocks     | 2850     |
+-------------------------+----------+

More reading

Speed Up Your Web Site With MySQL Query Caching
MySQL.com Query Cache Configuration