Optimizing MySQL performance

  • SumoMe

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 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:

query_cache_size=128M
query_cache_limit=1M

To

query_cache_size=2G
query_cache_limit=1M

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:

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 use Varnish (which why wouldn’t you?) this is very similar to N 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 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.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Author: Luis Tineo

Husband, Father, performance improvement junkie, biker and video gamer, Linux user and in my day job I'm a Systems Architect at Blue Acorn.

Share This Post On
  • http://rentalbookingsoftware.com Kevin Javitz

    Excellent post and very clear, thx

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • http://www.kingletas.com Luis Tineo

      Thanks Kevin – I am glad I could help!

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)