Over a million developers have joined DZone.

Some More MySQL Tuning

· Java Zone

Microservices! They are everywhere, or at least, the term is. When should you use a microservice architecture? What factors should be considered when making that decision? Do the benefits outweigh the costs? Why is everyone so excited about them, anyway?  Brought to you in partnership with IBM.

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.

Discover how the Watson team is further developing SDKs in Java, Node.js, Python, iOS, and Android to access these services and make programming easy. Brought to you in partnership with IBM.


Published at DZone with permission of Moshe Kaplan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}