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:

http://www.mysqlperformanceblog.com/2010/10/27/mysql-limitations-part-4-one-thread-per-connection/

http://dev.mysql.com/doc/refman/5.6/en/thread-pool-plugin.html

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

http://stackoverflow.com/questions/2762697/mysql-innodbinnodb-flush-method

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.

http://mysqlha.blogspot.com/2013/05/configuring-innodb-for-mysql-56.html

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
http://dba.stackexchange.com/questions/33125/mysql-5-5-determining-correct-writeread-io-threads-on-high-end-system

Bottom Line

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

Keep Performing,

Moshe Kaplan

Some Best Practices for your Next MySQL Installation with SSD

Do you plan on installing a new MySQL server? 
Did you ask yourself what is the best file system for it? 
What modification are recommended? 
What MySQL version to use?
Do you consider using SSD?

You got to the right place.

Linux, SSD and MySQL. The Best Practices

1. Up to date CentOS (6.3). MySQL RPM are easily available for this platform w/o the need to compile them.
2. ext4 file system. It is has some goodies over ext3 and is even more recommended if you plan to use SSD (and you may need SSD if your system is resource demanding).
3. File system discard option. Recommended for SSD to avoid the need to read before write.
4. Consider some extra cheaper disks. SSD disks are highly expensive and can have a relatively short life if they are not used properly. Consider placing some cheaper disks (SAS or SATA) to handle tasks that do not require the high end SSD disks.
5. Some more recommendations about Linux and SSD from Patrick's:
1. File system layer: remove 'relatime' if present and add 'noatime,nodiratime,discard' to reduce writes and improve performance and disk life expectancy.
2. Scheduler: use the 'deadline' scheduler instead of 'CFQ' to match SSD behavior.
3. Swap: set a small swappiness
4. tmp: move /tmp and /var/tmp to RAM disk to improve performance and avoid unneeded writes to disk.
5. Partition Alignment: when creating a partition, enter a start sector of at least 2,048 and divisible by 512 to align pages.
6. MySQL 5.6. 5.6 if finally in GA and it good idea to start working with it before 5.5 become obsolete.

Bottom Line
Few decisions can enhance your MySQL performance. Make your decisions right.

Keep Performing,
Moshe Kaplan