Skip navigation links

MySQL Forums :: Performance :: Problematic ALTER (empty) TABLE performance


Advanced Search

Problematic ALTER (empty) TABLE performance
Posted by: Haron Media ()
Date: February 20, 2013 05:50AM

Hello,

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.

Thanks.

Options: ReplyQuote


Subject Views Written By Posted
Problematic ALTER (empty) TABLE performance 778 Haron Media 02/20/2013 05:50AM
Re: Problematic ALTER (empty) TABLE performance 300 Rick James 02/21/2013 11:17PM
Re: Problematic ALTER (empty) TABLE performance 365 Haron Media 02/22/2013 02:58AM
Re: Problematic ALTER (empty) TABLE performance 309 Rick James 02/23/2013 12:16AM
Re: Problematic ALTER (empty) TABLE performance 359 Haron Media 02/23/2013 03:15AM
Re: Problematic ALTER (empty) TABLE performance 303 Rick James 02/23/2013 11:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.