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:
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.
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.
With MySQL endless configuration options, you can always find a great options to better tune your system.
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)