Mysql Alter Tables took 5 hours to add 1 column

MySQLmysql-5.6mysql-cluster

Had an issue recently where to add a column on a table with 1 million rows took 5 hours. I added a column with default value of 1. This was done during the night so there was not much activity on the database but it did have many locks once this started.

Looking at the server stats CPU, memory and hard drive space were all well below half of their maximums. So I'm not sure why this took so long the table in question only has 7 columns including the new one. Data types are int(11), datetime or varchar(45).

I'm using MySQL Cluster 7.3.3, with 4 data nodes all of which are in the same datacenter.

Can anyone advise why it would take 5 hours to simply add a column?

Table structure:

CREATE TABLE ddi_t ( 
    id int(11) NOT NULL AUTO_INCREMENT, 
    ddi varchar(45) NOT NULL, 
    is_deleted tinyint(1) NOT NULL DEFAULT '0', 
    created_time datetime NOT NULL, 
    module_group_id int(11) DEFAULT NULL, 
    created_user_id int(11) DEFAULT NULL, 
    supplier_id int(11) DEFAULT '1', 
    PRIMARY KEY (id), 
    UNIQUE KEY uq_ddi_1 (ddi), 
    KEY fk_ddi_1_idx (module_group_id) 
) ENGINE=ndbcluster AUTO_INCREMENT=935352 DEFAULT CHARSET=latin1

Best Answer

In my experience with MySQL Cluster, I found pt-online-schema-change as a very handy tool for appling live schema changes.

It works this way:

  1. it creates a new real table B (with temporary purposes) as clone of the table A that you want to change
  2. then applies the schema change to table B. The operation is fast cause the table is empty and has no traffic on it.
  3. it creates a trigger so that new records inserted in table A will be copied into table B
  4. then slowly copies records from A to B in small chunks of data
  5. them with atomic RENAME TABLE operations B becomes A

I'm not sure if it works with all kind of schema changes.

If the table is big, it will take a long time, but the table will remain available for read/write queries.

The mysql user that runs pt-online-schema-change must have particular privileges, but this is not clearly explained in the documentation (I've opened a bug ticket about this)

Related Question