Optimizing MySQL performance
Optimizing MySQL performance
Here is a little trick I learned recently on how to optimize MySQL performance or rather how to think when trying to get the most out of MySQL performance.
There are 2 variables or settings in MySQL you want to pay attention to:
query_cache_size which is the maximum amount of data that may be stored in the cache
query_cache_limit which is the maximum size of a single resultset in the cache.
In other words, the query_cache_size is the bucket you want to fill with the query_cache_limit.
Now if you were to run something like mysqltuner.pl and gives you something like the following tuning hints:
[OK] Query cache efficiency: 31.3% (39K cached / 125K selects) [!!] Query cache prunes per day: 2300654 Variables to adjust: query_cache_size (> 128M)
or if you were to do something like:
mysql -e "show status like 'qc%';"
You should get something like this:
+-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 2105 | | Qcache_free_memory | 28552504 | | Qcache_hits | 124244 | | Qcache_inserts | 40719 | | Qcache_lowmem_prunes | 2300654 | | Qcache_not_cached | 90 | | Qcache_queries_in_cache | 9415 | | Qcache_total_blocks | 21119 | +-------------------------+----------+ 8 rows in set (0.00 sec)
In both cases you might want to play with or tweak both the query_cache_size and the query_cache_limit values. Just keep in mind that high values are discouraged and this is a per installation basis.
Recommended values could range from:
It all depends on your query cache efficiency. Now to me this is very different from the MySQL’s key cache hit ratio which you should probably keep in mind when optimzing mysql performance.
Now, lets explain a little what some of these values mean:
Is the memory free for new queries to be cached
Query cache hits, shows how many queries have been served by the cache, keep an eye on these number, obviously the bigger the better.
Tells you how many queries have been created and added to the cached pool, these value is not saved from during a reboot
From a purely optimization standpoint this may be the single most important number, if this number grows too fast or too large, it means you are running out of memory for the cache pool, as it is filling out too quickly, this indicates how many queries have been discarded from cache in order to cache new ones, increase the query cache size (query_cache_size) to avoid this. If you use Varnish (which why wouldn’t you?) this is very similar to N LRU nuked objects
The queries actually in cache.
Ok, what else can I do to optimize MySQL Performance?
This is more like a homework but you’d be surprise what a great difference it makes to adjust the join_buffer_size and sort_buffer_size values. Careful these values are per connection so setting them up too high can and will eat all of your RAM memory. Play with them at your own peril!
However if you are seeing that MySQL is creating too many temporary tables (and worse some of them in disks) then adjusting these values may help. Also using a ramdrive to save the temporary tables may help optimizing and tuning MySQL.