Archive for May 16th, 2012

16th May
2012
written by simplelight

Spent some time tuning my MySQL database for a small website (~2K users per day). MySQL Tuner was recommending that we increase the size of the query cache above 16M but we were dubious. The relevant metrics according to this article are:

  • Hit rate     = Qcache_hits / (Qcache_hits + Com_select)
  • Insert rate = Qcache_inserts / (Qcache_hits + Com_select)
  • Prune rate = Qcache_lowmem_prunes / Qcache_inserts

In our case we had gathered the following stats over a 48 hour period:

| Com_select                        | 1163740   |
| Qcache_hits                       |   531650   |
| Qcache_inserts                   | 1021165   |
| Qcache_lowmem_prunes     |    82507   |

| Qcache_not_cached             | 142575    |
| Qcache_queries_in_cache    | 2145        |
| Qcache_total_blocks           | 5643        |
| Qcache_free_blocks            | 1175        |
| Qcache_free_memory         | 11042672  |

So for our database:

  • Hit rate     = 24%
  • Insert rate = 60%
  • Prune rate =   8%

We’re not too sure what to make of this. A hit rate of 24% doesn’t seem to bad but our insert rate is also quite high. For now, we’re leaving the query cache as is. Especially since the comments in the post mentioned above suggest that making it larger than 20M is futile.

Tags: