Jan 1, 2014

Some More MySQL Tuning

thread_handling = one-thread-per-connection
MySQL is designed for lightweight connection creation. Therefore, you may not use connection pooling. However, if you are a connection pooling fan (Java and Ruby devs, please raise your hands), don't forget to configure the MySQL for that:

In this case you should also avoid the thread_cache_size recommendation and use thread_cache_size = 0 (and not 8 for example).

Double Flushing and SSD
Does your data really being written to disk? or does it stuck in the OS caching?
innodb_flush_method = O_DIRECT: bypass the OS caching
innodb_flush_method = O_Sync: Makes sure disk is getting the call
innodb_flush_method = O_DSync: The last two options combined

Large RAM configurations
If you have a lot of memory, and many connections (and threads), you will probably find out that your threads are waiting for your memory. In order to avoid it, you may split the InnoDB buffer pool size to smaller sections. Pros: each one manage its section, so data can be served from multiple memory sections (N times faster). Cons: you may find it memory inefficient, as data may be loaded twice to memory.
In any case, make sure innodb_buffer_pool_size/innodb_buffer_pool_instances > 1GB
Please notice that number selection should be based on actual system bottleneck.

Matching IOPS
SATA and SAS disk were providing 100 IOPS (and multipliers of it when using RAIDs). Therefore innodb_io_capacity default was 200.
If you consider migration to SSD based machines (that can provide up to 150K IOPS) you may find it useful to change it to the actual system IOPS capacity.
Please notice that if you use innodb_buffer_pool_instances, you should divide this number by the number of instances.

IO Threads
Consider adjusting the number of threads writing to/reading from disk to the number of your system CPU cores
innodb_read_io_threads = N
innodb_write_io_threads = N

Bottom Line
With MySQL endless configuration options, you can always find a great options to better tune your system.

Keep Performing,


Intense Debate Comments

Ratings and Recommendations