MySQL query cache stats - why Qcache_lowmem_prunes is high?

Question

I have the following query cache settings

query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 32M

After running for one full day, I have the following stats

mysql> show global status like 'qca%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 66       |
| Qcache_free_memory      | 30462784 |
| Qcache_hits             | 1995904  |
| Qcache_inserts          | 2197056  |
| Qcache_lowmem_prunes    | 531214   |
| Qcache_not_cached       | 40683    |
| Qcache_queries_in_cache | 1429     |
| Qcache_total_blocks     | 2946     |
+-------------------------+----------+
8 rows in set (0.00 sec)

The Qcache_lowmem_prunes is relatively high, but Qcache_free_memory is also high, what are the reason? Are there any way to have a high hit rate by tuning the above config?

Answer 1

Cached query results may be evicted because memory was running out, or it may be evicted because the data was updated in the table the cached query result is based on.

Say for example you have many individual query results stored in the query cache for queries like:

SELECT * FROM mytable WHERE id = 1;
SELECT * FROM mytable WHERE id = 2;
SELECT * FROM mytable WHERE id = 3;
SELECT * FROM mytable WHERE id = 4;
...
SELECT * FROM mytable WHERE id = 6060842;

From time to time, there are some lomem prunes, because the memory of the query cache was filling up.

Then someone goes and inserts one new row into that table.

All the query results that depend on that table are immediately purged from the query cache. Thus there is now a lot of free memory that was full only a brief time ago.

Answer 2

Some of those need to be divided by Uptime to be useful. A typical value for Qcache_lowmem_prunes/Uptime is about 5 per second.

Another interesting metric is Qcache_lowmem_prunes/Qcache_inserts, which is 24% for your case. That is a bit high, indicating that the the QC is not very effective.

Keep in mind that every modification to a table causes all QC entries for that table to be purged. If there are a lot of purges, the QC may be costing more effort than benefit.

Most applications have too many writes to make the QC worth having.

A compromise is to set query_cache_type = DEMAND and liberally use SQL_CACHE and SQL_NO_CACHE to avoid some of the wasted QC activity.

These are also useful:

Qcache_not_cached / (Qcache_hits + Com_select + Qcache_not_cached)

Qcache_hits / Qcache_inserts -- Your value is <1, meaning the QC is not very useful

Be aware that the QC has been removed in 8.0 and is not available with any Clustering setup.