Before we put a server into production a set of benchmarks are ran, typically sysbench fileio and OLTP. The baseline benchmark results were outstanding and showed no bottlenecks for the any of the test workloads within our thread count range. However - once the server was put into production the server started to stalls at times. I switch back to a tried and true slave server and the problems disappeared.
I was perplexed, what is going on here? At first it the issue appeared to be related to the well known InnoDB Global Kernel Mutex issue in MySQL 5.0, 5.1 and 5.5 but as I started looking into our Cacti graphing I noticed that the the InnoDB I/O Pending stats on the new server (db3) were much higher than our tried and true server (db1).
Here is the Cacti graph on MySQL InnoDB I/O Pending:
During the same peak time, but on a different day, db1 had less Pending IO than db3. Something must be different between the two servers but what? The best way I know of to get server config info is to run pt-summary on each server and then compare the results. If you are not familier with pt-summary then your missing out! Perocna's pt-summary made the problem obvious and that being db3 was running the default CentOS CFQ IO Scheduler! After making the switch to the deadline scheduler the server's performance has been stable.
There are a plethora of blog post to why CFQ is bad for MySQL workloads and here are a few I found that convenced me that this was the issue:
But why did this happen in the first place?
When I first initially benchmarked the server, I explicitly set the IO scheduler to deadline, its just something in my benchmark script that happens automatically. As a new user of CentOS, I wasn't aware the default scheduler was CFQ. When the server was rebooted, the I/O scheduler was switched back to the default CFQ scheduler...BURN!
If you are running CentOS for a dedicated MySQL server, be sure to set the default I/O scheduler to deadline or noop in your /boot/grub/grub.conf kernel paramaters. Simply add the following line to the end.