MySQL 5.5 – determining correct write[read]_io_threads on high-end system

innodbMySQLmysql-5.5performancetuning

Specifically dual-CPU 32 core – 128RAM – RAID 10 SSD . Ubuntu 64 server.

Heavy Innodb load at times – ~ 2000+ queries per sec. Heavy read and write.

Currently we are running on the default write_io_threads of 4.

Are we "nerfing" the power of our server?
Is it safe to run it on max write_io_threads=64 and read_io_threads = 64 ?

How is this number determined?

Best Answer

Back on March 15, 2012, I answered this question : MySql recommended hardware.

In my answer, I mentioned a client who is still in my employer's web hosting company to this very day. They are currently running 3 DB Servers in Circular Replication. Each DB Server has:

  • MySQL 5.5.9
  • 192 GB RAM
  • 162 GB InnoDB Buffer Pool
  • Dual hexacore (that's right, 12 CPUs)
  • 1.8TB Disk Volume
  • 528 multitenant databases
  • 500 GB of InnoDB Data
  • innodb_file_per_table enabled

I have them set with

innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_thread_concurrency = 0
innodb_buffer_pool_size = 162G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 384M

Bottom line: You got the cores, and 128G RAM? What are you waiting for ? Crank it up and have some fun. My client has been all but flabbergasted with these settings against that hardware since March 2011. You will be blown away !!!

CAVEAT

All kidding aside, you should be careful when working with VMs(VMWare, AWS, any other cloud-based MySQL Instances) or commodity hardware. However, with that much firepower in bare metal, by all means, you should exploit those settings because any version of MySQL before MySQL 5.5 works with single threaded InnoDB. If you have MySQL 5.1, the InnoDB plugin (starting from MySQL 5.1.38) would have to be installed and then set. All Percona Server 5.1 binaries already have these in place. Since you have MySQL 5.5, these settings are natively installed and have been tested in many respects. You just have to take responsibility for tuning the new InnoDB Settings.

Please see my other posts on setting InnoDB's new settings:

UPDATE 2013-01-18 21:52 EDT

You need to take the bull by the horns and setup some benchmarks. Let's go back historically. innodb_read_io_threads and innodb_writes_io_threads did not come with MySQL 5.0 or 5.1. Someone created a plugin and made it available in MySQL 5.1.38. The defaults were 4. I also notice that Percona Server implemented these things early in MySQL 5.1 with defaults of 8. Most people who use Percona Server out-of-the-box swear by it and often say that it is better than MySQL. However, this comparison is usually done with tweeking InnoDB. So, of course, Percona Server, with double the number of threads, would be better.

Given this conservative way to get better performance (upgrading to Percona), you will never really know how good, bad, or ugly InnoDB will go unless you tune for it. Keep in mind my post from Nov 24, 2011. Multiple versions of MySQL were benchmarked and, in some cases, InnoDB under MySQL 4.1 outperformed 5.x in a single-threaded environment. That's given the same level playing field.

To tip the scales in your favor, you must tune InnoDB. Rather than give you any single definitive Pick-This-Pick-That guide, you need to read about the settings InnoDB ghas been given to make multiple cores come alive. Not every environment is like my client's dual hexacore servers or your dual hexadecacore.

If I had to give you a good starting point, it would have the be mysqlperformanceblog.com. Everything performance-centric about mysql is there for you to read, select, and benchmark.

Related Question