MySQL performance of adding a column to a large table

Question

I have MySQL 5.5.37 with InnoDB installed locally with apt-get on Ubuntu 13.10. My machine is i7-3770 + 32Gb memory + SSD hard drive on my desktop. For a table "mytable" which contains only 1.5 million records the following DDL query takes more than 20 min (!):

ALTER TABLE mytable ADD some_column CHAR(1) NOT NULL DEFAULT 'N';

Is there a way to improve it? I checked

show processlist;

and it was showing that it is copying my table for some reason. It is disturbingly inconvenient. Is there a way to turn off this copy? Are there other ways to improve performance of adding a column to a large table?

Other than that my DB is relatively small with only 1.3Gb dump size. Therefore it should (in theory) fit 100% in memory.

Are there settings which can help? Would migration to Precona change anything for me?

Add: I have

innodb_buffer_pool_size = 134217728

Foxhound

Even though it fits in memory, it has to write everything back to the disk to make it permanent. – Tenorrhaphy

possible duplicate of optimize mySql for faster alter table add column – Homogenous

They are talking about MyISAM vs InnoDB for me – Foxhound

Answer 1

Are there other ways to improve performance of adding a column to a large table?

Short answer: no. You may add ENUM and SET values instantly, and you may add secondary indexes while locking only for writes, but altering table structure always requires a table copy.

Long answer: your real problem isn't really performance, but the lock time. It doesn't matter if it's slow, it only matters that other clients can't perform queries until your ALTER TABLE is finished. There are some options in that case:

1. You may use the pt-online-schema-change, from Percona toolkit. Backup your data first! This is the easiest solution, but may not work in all cases.

2. If you don't use foreign keys and it's slow because you have a lot of indexes, it might be faster for you to create a copy of the table with the changes you need but no secondary indexes, populate it with the data, and create all indexes with a single alter table at the end.

3. If it's easy for you to create replicas, like if you're hosted at Amazon RDS, you may create a master-master replica, run the alter table there, let it get back in sync, and switch instances after finished.

UPDATE

As others mentioned, MySQL 8.0 INNODB added support for instant column adds. It's not a magical solution, it has limitations and side-effects -- the table must not have a full text index, etc -- but should help in many cases.

You can specify explicit ALGORITHM=INSTANT LOCK=NONE parameters, and if an instant schema change isn't possible, MySQL will fail with an error instead of falling back to INPLACE or COPY. Example:

ALTER TABLE mytable
ADD COLUMN mycolumn varchar(36) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/

Heartwhole

"You may use the [pt-online-schema-change][1], from Percona toolkit. Backup your data first! This is the easiest solution, but may not work in all cases." -- this is extremely good advice – Renitarenitent

Answer 2

I know this is a rather old question but today i encountered a similar problem. I decided to create a new table and to import the old table in the new table. Something like:

CREATE TABLE New_mytable  LIKE mytable ;

ALTER TABLE New_mytable  ADD some_column CHAR(1) NOT NULL DEFAULT 'N';

insert into New_mytable  select * from mytable ;

Then

START TRANSACTION;
insert into New_mytable  select * from mytable where id > (Select max(id) from New_mytable) ;

RENAME TABLE mytable TO Old_mytable;

RENAME TABLE New_mytable TO mytable;
COMMIT;

This does not make the update process go any faster, but it does minimize downtime.

Hope this helps.

Glarus

Thank you very much. But is this the best way to go? I am trying other solution but still not found. :) – Infallible

It is the fastest way to add a column to a large table and rebuilding the table. Also there is almost no downtime. If you are running MySQL 8.0 or higher you could try ColinM's solution. – Glarus

Something to keep in mind while using this approach is that at the time of writing this comment the CREATE TABLE ... LIKE statement does not create an exact copy of the table. For example foreign keys configs aren't copied over to the new table. From the docs: dev.mysql.com/doc/refman/5.7/en/create-table-like.html – Joli

What if the original table gets some UPDATEs or DELETEs instead of just INSERTs? – Hoi

Answer 3

MariaDb 10.3, MySQL 8.0 and probably other MySQL variants to follow have an "Instant ADD COLUMN" feature whereby most columns (there are a few constraints, see docs) can be added instantly with no table rebuild.

• MariaDb: https://mariadb.com/resources/blog/instant-add-column-innodb
• MySQL: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/

Mahalia

MySQL 8 supports it now as of May 8 2018 – Outwardbound

Answer 4

What about Online DDL?

http://www.tocker.ca/2013/11/05/a-closer-look-at-online-ddl-in-mysql-5-6.html

Maybe you would use TokuDB instead:

http://www.tokutek.com/products/tokudb-for-mysql/

Sarasvati

Cool feature, but I'm afraid it will make alter table even slower – Foxhound

Answer 5

There is no way to avoid copying the table when adding or removing columns because the structure changes. You can add or remove secondary indexes without a table copy.

Your table data doesn't reside in memory. The indexes can reside in memory.

1.5 million records is not a lot of rows, and 20 minutes seems quite long, but perhaps your rows are large and you have many indexes.

While the table is being copied, you can still select rows from the table. However, if you try to do any updates, they will be blocked until the ALTER is complete.

Ridinger