I'm running MySQL 5.5 on a server with two SSDs in software RAID-1 configuration. The filesystem is ext4.
When any process (external to MySQL) produces a large amount of disk IO on the server, I find that MySQL becomes virtually paralyzed, with queries that normally take 30ms taking 10 seconds or more, depending on how long the IO lasts.
I can easily reproduce the problem by running a dd write test as follows:
dd if=/dev/zero of=tempfile bs=1M count=3024 conv=fdatasync,notrunc
This writes a 3GB test file to the same disk and partition where MySQL's data and log files are stored. Here is the output from the command:
3024+0 records in 3024+0 records out 3170893824 bytes (3.2 GB) copied, 16.0938 s, 197 MB/s
When I execute the command, I can immediately see queries showing up in the MySQL slow query log. Not surprisingly, they are all insert/update queries, which I assume are waiting to be committed. However, to my huge surprise, the query times are enormous: some are nearly as long as the execution time of the dd command itself!
It seems as if MySQL is paused or locked during any intense IO happening on the server. I thought that running MySQL on SSDs in RAID-1 would be exceptionally fast, with other IO operations unlikely to have any large effect on MySQL.
Is it normal for a single write process like this to disturb MySQL so profoundly?
Should I be looking at problems with the software RAID-1 or the ext4 filesystem?
- Can MySQL be configured differently? I've read about setting
innodb_write_io_threadsto take advantage of the higher IOPS available with SSDs, but I'm not sure that will solve this problem.
- Is it necessary to store all MySQL-related files on a dedicated disk to avoid this problem?
Thanks very much for any help.