Mysql Alter Tables took 5 hours to add 1 column

Question

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

Answer 1

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)

Answer 2

Adding a column requires an exclusive lock on the entire table (per node, not per cluster). There's no queue, so the DB will wait until there are no writes happening before it locks the table. If the application is performing a lot of writes, it may be impossible to grab that lock.

If you have control over which node the app connects to (with HAProxy et al), direct all traffic away from the node you are trying to update. Otherwise, you will need to shut down your app before performing tasks like this.

Answer 3

MySQL Cluster supports ALTER TABLE algorithm=inplace when adding a column which isn't using STORAGE DISK. It might be however that when running this in a MySQL Server that have traffic against the table that you have to wait to get the lock on the table. However you can actually perform this ALTER TABLE in a separate MySQL Server and it will be perfectly online and there will be no effect to your other queries.

The problem is that the MySQL Server have a fairly strict view on how changes to metadata can occur, NDB supports a much more flexible view, so by running it from a separate MySQL Server you can avoid the strict view of the MySQL Server.