MySQL ALTER empty TABLE taking too long

hmcp

Member
Feb 16, 2013
6
0
1
cPanel Access Level
Root Administrator
Hello,

a site has an installer script that executes two SQL files, one to create the database structure, and the other to fill in some generic data. This other also alters tables and adds new columns and indexes, before inserting data. Tables are mixed MyISAM and InnoDB, mostly InnoDB, 91 tables.

The problem is that executing this SQL takes up to 5 minutes. MySQL processlist shows it hanging at alter table and create index queries, against otherwise empty tables, for several seconds per alter query.

On a NON-cpanel server (32-bit CentOS 6.3 with stock MySQL 5.1), with same hardware (Xeon 3050, 2MB RAM, 2x250GB HDD in software RAID-1) the same set of commands run in under 20 seconds, iotop showing up to 10MB written by mysqld. On this cPanel server (32-bit, CentOS 6.3 with MySQL 5.1, and upgraded to 5.5 which didn't fix anything, cPanel 11.36), it runs for 5 minutes, with iotop showing more than 200MB written by mysql, with no other site accessing MySQL during that time. Both servers have the same my.cnf.

Otherwise MySQL runs fine, the sites are snappy and there is no other visible problem, other than this issue with altering the tables, and I even consulted mysqltuner which shows no problems. The disks check fine, there are no errors thrown by MySQL or system, plenty of space and I tried even with and without MySQL tmpdir on tmpfs.

I have no idea what could be wrong. My first thought was faulty discs, but they check fine (no bad SMART attributes, io seems fine) and besides, the non-cpanel server shows much less data written by mysqld for same set of queries, so I'm guessing there's additional processing/monitoring/statistics collection or something going on with this cpanel server.

Please help.

Code:
[mysqld]
bind-address=127.0.0.1
max_connections=32
connect_timeout=10
tmpdir="/tmp/mysql"
thread_cache_size=64

slow_query_log=1
long_query_time=2
slow_query_log_file=mysql-slow.log

innodb_file_per_table=1
innodb_thread_concurrency=5
innodb_log_file_size=16M

# Innodb set to 64MB, required 49MB
innodb_buffer_pool_size=64M
innodb_flush_method=O_DIRECT

# Tried setting this to 2, nothing changed
innodb_flush_log_at_trx_commit=1

# using tmpfs so force "ondisk" tmp tables
# Tried raising, tried without tmpfs (to hit the real disk), no changes
max_heap_table_size=1M
tmp_table_size=1M

max_connect_errors=10
join_buffer_size=16M
query_cache_type=1
query_cache_limit=2M
query_cache_size=96M
key_buffer=32M
read_buffer_size=1M
sort_buffer_size=2M
table_cache=1024
myisam_sort_buffer_size=32M
default-storage-engine=MyISAM
open_files_limit=5704

[myisamchk]
sort_buffer=64M
write_buffer=16M
read_buffer=16M
key_buffer=64M