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

and

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 likemysqltuner.pland gives you something like the following tuning hints:

[OK] Query cache efficiency: 31.3% (39K cached / 125K selects)[!!] Query cache prunes per day: 2300654Variables 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:

query_cache_size=128Mquery_cache_limit=1M

To

query_cache_size=2Gquery_cache_limit=1M

It all depends on your query cache efficiency. Now to me this is very different from theMySQL’s key cache hit ratiowhich you should probably keep in mind when optimzing mysql performance.

Now, lets explain a little what some of these values mean:

Qcache_free_memory

Is the memory free for new queries to be cached

Qcache_hits

Query cache hits, shows how many queries have been served by the cache, keep an eye on these number, obviously the bigger the better.

Qcache_inserts

Tells you how many queries have been created and added to the cached pool, these value is not saved from during a reboot

Qcache_lowmem_prunes

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 useVarnish(which why wouldn’t you?) this is very similar toN LRU nuked objects

Qcache_queries_in_cache

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 thejoin_buffer_sizeandsort_buffer_sizevalues. 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 aramdriveto save the temporary tables may help optimizing and tuning MySQL.

Rating: 0.0/10(0 votes cast)

Rating:0(from 0 votes)