Problematic ALTER (empty) TABLE performance
Posted by: Haron Media
Date: February 20, 2013 05:50AM
I'm trying to figure out the difference in executing a same set of alter (empty) table queries run on two hardware-wise identical servers (Xeon 3050, 2GB RAM, SATA2 HDD sw RAID1, 32-bit CentOS 6.3).
There are 60 alter table queries, each adding a column and relevant index. The table has no data in it.
On one server, with no cPanel (stock 32-bit CentOS 6.3 MySQL 5.1), it runs in about 20 seconds, mysqld writes cca 24MB to disk according to iotop -ao.
On the other server, WITH cPanel (32-bit CentOS 6.3, cpanel's mysql, tried 5.1 and 5.5), it runs in 6 times more time (cca 3 minutes) and mysqld writes 6 times more data to the disk according to iotop -oa.
Both servers have same my.cnf, setting only innodb_file_per_table=1. Hardware failures ruled out, HDD check fine, there are no errors reported by the system or by MySQL. No other processes accessing MySQL.
Show processlist shows the cPanel server hanging at ALTER queries for several seconds per query, no other query running except those that should (alter and create index). CPU usage is minimal, few percent, except very high iowait.
Any ideas why the difference and where to start troubleshooting? I'm not quite sure how to precisely monitor and compare how much data is actually written into which file, but I've ruled out the cpanel MySQL writing eg. to error log or something in addition. The only files accessed are the relevant MYD, MYI, frm, and ibd, as well as ibdata1 and ib_logfiles, according to both lsof and ftop.
The only difference that makes sense to me is that one runs CentOS' stock MySQL, the other (slower) is compiled by cpanel.
I'm probably missing something blatantly obvious, but need help figuring out what.