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.

• Yoga: According to the doc: The information provided by the Qcache_lowmem_prunes status variable can help you tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache., so it seems it is not related to the variable Qcache_lowmem_prunes?
• Bill Karwin: Think of it this way: You might need to remove food from your refrigerator to make room for other groceries, or because the food had spoiled. Those are different reasons to remove the food, but either way it results in some free space. If all you see is the amount of free space and the number of items that had been removed over the past few months, how can you know why the current free space is free?

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.