MySQL (innodb) performance tuning

Written by Gionatan Danti on . Posted in Linux & Unix

User Rating:  / 5
PoorBest 

Basic server-side parameters

Ideally, we can split mysql server-side options in two group:

  • options that, while influencing server speed, don't affect its reliability (in other words: that don't increase data loss probability);
  • options that affect speed and reliability also.

In the first group, we can find options that mostly deal with reads: for example, the amount of buffer memory to use, the query cache state and size, and similar things. On the other hand, in the second group we can find options that mostly deal with writes: for example, the disk flush policy, the disk access methods, and so on.

The key point to understand is that, while for reads we are basically dealing with read-only caches that, for their very nature, are unable to cause data loss, for writes there is no free lunch: as write speed is generally bound by disk access time, increase writes rate often means to delay some of them, in the hope to collapse multiple writes into a single disk access. This means that any server crash / power outage can lead to greater data loss. (Note: this is the very reason because high-end disk controllers feature a persistent cache that protect against write loss).

So, what options are we going to examine in this article? In the first, read-related (and safe) group we have the following:

  • innodb_buffer_pool_size: this is the size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables;
  • query_cache_size: this is the amount of memory allocated for caching query results;
  • query_cache_type: this value controls if the query cache is disabled (0, default value), always enabled (1) or on-demand enabled (2).

On the other hand, in a effort to increase write speed we are going to explore the following options:

  • innodb_flush_method: this controls what kind of synchronization primitive, and file open options, mysql will use to flush data and log files;
  • innodb_flush_log_at_trx_commit: it determines mysql disk write policy (basically, it the system should try to synch every commit or once per second).

A complete list and detailed explanation of mysql and InnoDB options can be found on mysql web site, here and here.

Comments   

 
#1 David van Enckevort 2012-06-09 00:25
Nice tests. Another good source of information you could mention is the MySQL Performance blog by the people from Percona: http://www.mysqlperformanceblog.com/
 
 
#2 Gionatan Danti 2012-06-11 15:54
Sure. It is a very renowned blog!

Thank you for posting the link here :)
 
 
#3 ykyuen 2012-07-26 11:23
Thanks for your blog post. it is very helpful and the content is easy to follow.

=D
 

You have no rights to post comments