MySQL | 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.

Drop multiple MySQL tables

Submitted by Geoff on Thu, 2011-02-03 13:17

Since MySQL's DROP TABLE statement requires specifying each table you wish to drop, dropping many tables at once can be a pain; especially if you have InnoDB tables with foreign key constraints. With the following stored procedure, tweaked from some of the helpful comments on the MySQL documentation, you can drop any tables matching a particular regular expression pattern.

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.