Archive for May, 2012

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.

This post has good information: http://blog.ertesvag.no/post/10720082458

Step 1

Edit nginx.conf file on production server and then restart nginx.

Step 2

Set up distribution on Amazon Cloudfront. Point origin to the cname set up below.

Step 3

Set up a cname on Linode. For instance: origin.example.com –> www.example.com

A phenomenal essay on a systematic approach to interactive visualization.