Performance Zone is brought to you in partnership with:

' ! Moshe Kaplan constantly helps successful firms getting to the next level and he is thrilled to uncover some of his secrets. Mr. Kaplan is a seasoned project management and cloud technologies lecturer. He is also known to be a cloud and SCRUM evangelist Moshe is a Co-Founder. He was a R&D Director at Essence Security, led RockeTier and served as a board member in the IGT and as a department head at a top IDF IT unit. Moshe holds M.Sc and B.Sc from TAU. Moshe is a DZone MVB and is not an employee of DZone and has posted 59 posts at DZone. You can read more from them at their website. View Full User Profile

Some More MySQL Tuning

  • submit to reddit

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.

Bottom Line

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

Published at DZone with permission of Moshe Kaplan, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)