The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL ALTER empty TABLE taking too long

Discussion in 'Database Discussions' started by hmcp, Feb 16, 2013.

  1. hmcp

    hmcp Member

    Joined:
    Feb 16, 2013
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    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
    
     
Loading...

Share This Page