Is it normal for disk activity to disturb MySQL performance?

Question

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.

1. Is it normal for a single write process like this to disturb MySQL so profoundly?

2. Should I be looking at problems with the software RAID-1 or the ext4 filesystem?

3. Can MySQL be configured differently? I've read about setting innodb_read_io_threads and innodb_write_io_threads to take advantage of the higher IOPS available with SSDs, but I'm not sure that will solve this problem.
4. Is it necessary to store all MySQL-related files on a dedicated disk to avoid this problem?

Thanks very much for any help.

Answer 1

From my experience, it is better to separate the MySQL data files and log files from other application files. When you don't have dedicated MySQL server, separate the data files and log files to different disks. I have faced the similar issue and we resolved by changing our data directory and log files to a each dedicated separate directories. I suggest you to go through the MOCA (MySQL optimal configuration architecture) for better understanding. I hope this will help you.

Thanks.

Answer 2

@rathishDBA has a legitimate point, but sometimes intensive disk I/O must be performed on the database server. My recommendation is to run the job at reduced priority...

To start a process at reduced priority (default nice is 0 - kernel runs at -20, +20 gives process lowest possible priority)...

nice -n 20 dd if=/dev/zero of=tempfile bs=1M count=3024 conv=fdatasync,notrunc

To change priority of a current running process... obtain the process id then renice the process...

ps -ef | pgrep dd
renice -n 20 -p X  # where XXX is the process id

Answer 3

My "answer," if you can call it that, was finally realized by moving to more modern hardware. Unlike the old system, which used consumer-grade Micron SSDs, the new server uses IBM datacenter SSDs and a faster processor. Otherwise, the software configuration is identical as in the system tested above (same OS, same use case for MySQL, same mdraid RAID-1 config, etc.).

I can only assume that the new system's better SSDs and/or more modern motherboard and chipset are better at handling concurrent operations. I can no longer replicate the results show above. Database operations do not seem to be affected by disk activity -- at least not enough for queries to show up in my slow query log.

So, if you're experiencing strange slowdowns with database queries, don't forget to look at the underlying hardware as a possible source. Worked for me!