Optimizing MySQL Query Cache for Drupal

In running a VPS with a few Drupal sites, I ran into the baffling instance of having an underutilized query cache, but a relatively high prune to insert ratio. Initially set up on a 360MiB Linode instance, the query cache was at a size of 20MiB with approximately 4MiB (20%) free. After Linode improved their RAM allocations and the server had 512MiB of RAM available, I correspondingly increased the query cache to 32MiB but a larger portion remained unutilized with 10MiB (30%) now free. I don't have historical numbers on the number of prunes, but the value seemed to remain similarly high.

System Status

The MySQL tuning primer gives very little information on the server's operation, only saying what proportion of the query cache is in use:
QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 22 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 68.96 %
Current query_cache_min_res_unit = 4 K

MySQL Tuner doesn't add any more information, but says that the cache efficiency is adequate, while the prunes are excessive. It correspondingly recommends that the query cache be increased
[OK] Query cache efficiency: 67.7% (1M cached / 2M selects)
[!!] Query cache prunes per day: 33397
Variables to adjust:
query_cache_size (> 32M)

Mysqlreport is more helpful, showing that approximately every second insert into the cache will require a prune, and a query is read from the cache approximately twice after it is inserted. The usage guide says that a value between 10 and 20 percent is acceptable, but "it might be helpful to play around with query_cache_min_res_unit"; no guidance is offered.
__ Query Cache _________________________________________________________
Memory usage 22.15M of 32.00M %Used: 69.20
Block Fragmnt 12.40%
Hits 1.65M 1.8/s
Inserts 757.61k 0.8/s
Insrt:Prune 2.17:1 0.5/s
Hit:Insert 2.18:1

I set up Cacti rather recently, and while it doesn't provide any further insight into the issue, it is helpful to view changes in the server's operation over time either through system usage or configuration changes. Very helpful in this regard are the MySQL Cacti Templates, which provide thoroughly tested and easily installable templates to view MySQL status variables.

A Deeper Look at the Query Cache Status

The often seen recommendation is that you should increase the query cache size until the low memory prunes decrease, ideally to zero. This obviously isn't as ideal a solution for a lower memory VPS, where increasing MySQL's memory usage can take away much needed resources for an Apache web server. Further, it doesn't seem reasonable when so much allocated space is unused. A closer look at the query cache status variables is warranted:
mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 3051 |
| Qcache_free_memory | 10365720 |
| Qcache_hits | 1650400 |
| Qcache_inserts | 757805 |
| Qcache_lowmem_prunes | 348499 |
| Qcache_not_cached | 9146 |
| Qcache_queries_in_cache | 10180 |
| Qcache_total_blocks | 24809 |
+-------------------------+----------+
8 rows in set (0.00 sec)

A couple important numbers from these values:
free space / free mem blocks = average free block size (in bytes)
10365720 / 3051 = 3398
used space / num queries in cache = average result size (in bytes)
(32 * 1024 * 1024 – 10365720) / 10180 = 2278

Query Cache Fragmentation

The average free block size indicates that while there is a large amount of space free it is largely unusable due to the fragmentation. In this instance the average free memory block size is smaller than min_res_unit (which defaults to 4K); when a query is run it is unable to find a contiguous free memory segment of adequate size, and must perform a low memory prune even with several megabytes of total free space. While chances are that a segment of adequate size is now available, the same fragmentation issue remains; the query cache only removes old queries and does not optimize the available space. As a consequence the larger available segments fill up and another prune is soon required. While I can not be certain, this may be why the number of queries stored decreases over a period of time after the server starts, as visible in the next Cacti graph.

Query Cache Variables

The MySQL manual describes the query_cache_min_res_unit_variable:

...data usually is not handled in one big chunk. The query cache allocates blocks for storing this data on demand, so when one block is filled, a new block is allocated. Because memory allocation operation is costly (timewise), the query cache allocates blocks with a minimum size...
and the necessary adjustments:

If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory

The min_res_unit is a performance optimization to require fewer memory allocations when placing a new query in the cache. We can see in this server profile however, it leads to underutilization of the available memory and a high prune rate. More than just causing difficulty by being larger than the average free memory size, it is also almost twice the size of the average query in the cache.

Configuration Changes

I am currently testing the effect of modifications to the min_res_unit variable on query cache performance. Plans are to initially reduce the variable to 3Kib, which is large enough to contain the average query in one allocation but should be less wasteful with extra space. After running the server for some time so that cache usage stabilizes I will lower it again to 2KiB, which will be slightly smaller than the average query and require two allocations for storage. With rough numbers on the improvement of cache usage, a better recommendation on sizing the min_res_unit variable should be possible.