I have just moved servers from a two-server configuration with the application and database server running separately to a one-server LAMP stack.

Unfortunately the issue that I have encountered on the new server is, when I run a query that requires copying the table to a tmp file "Mysql Status: Copying to tmp table" from what I can see in iotop, the mysql process writes up to approximately 100mb and then slows to a crawl until the file completes at about 400mb (this can take up to 20mins)

On the old server, we were able to write the 400mb to the tmp table within 20 seconds. Is there some setting in MySQL that regulates how fast it can write to the file system?

  • Not to my direct knowledge... What kind of filesystem is your tmp dir? Is it really on-disk, is it perhaps tmpfs? Also, did the old server write the table to file as well, or did it create an in-memory temp table? Because that would mean tweaking max_heap_table_size & tmp_table_size. – Wrikken Feb 8 '14 at 18:01

If your new instance of MySQL is creating temporary tables with the InnoDB storage engine, the innodb_io_capacity could be throttling the rate at which it writes pages to disk.

However, even throttled, I would expect a mere 400MB to be pretty quick to write to disk. Have you considered the possibility that the disk drives on your new server are simply faulty?

I would recommend running some benchmarks with sysbench or iozone to see if you can reproduce the slow I/O speed without MySQL.

Re your comment:

The best figures to look at for a database server are random read, random write, and mixed workload. Run a similar benchmark on your old database server, and compare them. If your new database server has significantly lower numbers, that would explain the difference you're seeing.

|improve this answer|||||
  • Hi Bill, Here is the iozone output: "Record size = 4 Kbytes " "Output is in Kbytes/sec" " Initial write " 1518735.34 " Rewrite " 3160978.38 " Read " 4526442.50 " Re-read " 5253990.75 " Reverse Read " 3281538.00 " Stride read " 3285493.00 " Random read " 4364286.12 " Mixed workload " 4067542.50 " Random write " 3727224.38 " Pwrite " 1707682.34 " Pread " 4742008.41 " Fwrite " 5593141.31 " Fread " 11955377.25 iozone test complete. – user207757 Feb 9 '14 at 2:36
  • Thanks for all your help so far bill: here are the comparison of the stats... – user207757 Feb 9 '14 at 3:54
  • You don't have to share the numbers with me -- you're the one who has to interpret them. – Bill Karwin Feb 9 '14 at 3:57
  • Thanks for all your help so far bill: here are the comparison of the stats... New Server vs Old Server : Random read " 4364286.12 " vs Random read " 2723917.88 = diff 1640368.24 Mixed workload " 4067542.50 " vs Mixed workload " 842119.19 = diff 3225423.31 Random write " 3727224.38 " vs Random write " 1306467.34 = diff 2420757.04 From what I can see, the new server is significantly faster than the old server in terms of disk IO? Is that what you get from this also? – user207757 Feb 9 '14 at 4:00

Your Answer

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