Disk write in iotop when doing mysql "Copy to tmp table" 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?
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.