Mysql – Is it normal for disk activity to disturb MySQL performance

MySQLmysql-5.5performancessd

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.

Best Answer

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.

Related Question