2

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.

  • Any given process is always going to battle for resource, be it CPU, disk IO or memory. – Philᵀᴹ Nov 9 '16 at 10:46
  • Hi Phil. Sure, that's definitely true, but the question is to what extent? Should a single sustained file write operation lasting 16 seconds cause MySQL update queries to take up to 12 seconds to finish? Can you reproduce similar behavior on your setups? – aj_potc Nov 9 '16 at 11:09
  • It is absolutely possible but can be many things behind. How many CPU cores do you have? What vmstat or IO stat shows while you write to this big file? What is your bottleneck? Is innodb_log_waits increasing during this time? Does you insert/update cause buffer pool eviction? – Károly Nagy Nov 9 '16 at 14:30
  • Hi Karoly, I don't really see any clear bottleneck. The CPU is a Xeon E3-1270v3 with 4 cores. Using top, I can see IOwait goes no higher than 25% during this write process. innodb_log_waits remains with a value of zero. I'm not sure how to test for buffer pool eviction, but the inserts are very small, and the size of my buffer pool exceeds the size of the InnoDB data by about 30%. – aj_potc Nov 9 '16 at 22:29
0

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.

0

@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
  • Thank you for the suggestion. But wouldn't ionice provide a bigger benefit here? I thought that nice was only concerned with CPU usage and not IO. – aj_potc Mar 17 '17 at 15:20
  • Yes indeed. I've been trying to reduce CPU intensive commands. – RMathis Mar 17 '17 at 17:22
0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.