Performance | Gold Apple Software

Tuning the MySQL Query Cache - Part 2

Submitted by Geoff on Wed, 2011-10-19 21:19

It's been quite some time since I wrote my original post on optimizing the query cache, which set out a plan for testing improvements. Unfortunately I can't recall the dates of all the changes I made, and the server workload hasn't been consistent, but I think some trends can still be recognized.

The big question I had initially was if whether changing the value of query_cache_min_res_unit could have a significant effect on the query cache's memory utilization, and correspondingly reduce a high prune rate. The default value of 4K was almost twice the average size of a stored result, so I planned to reduce the setting in 1K increments and observer any effects.

Reducing Drupal's On-Disk Temporary Table Usage

Submitted by Geoff on Sat, 2011-05-14 13:39

MySQL frequently creates temporary tables to store intermediary query data, such as when sorting a result set. These tables are stored in memory until they grow past the tmp_table_size or max_heap_table_size configuration variables, unless they contain a BLOB or TEXT column (among some other factors), in which case they will be placed directly on disk. This can have a significant performance impact due to the extra IO operations, which are particularly important to avoid in a virtualized environment if possible.

Optimizing MySQL Query Cache for Drupal

Submitted by Geoff on Thu, 2010-07-22 23:14

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.