Mysql – Alter large table without downtime

alter-tableMySQLmysql-5.6percona-toolkit

I have a very large table, lets call it example, and I try to execute a little alter command on that table:

ALTER TABLE `example` ADD `example_column` TINYINT(1) NOT NULL DEFAULT 0;

Checking the progress of alter command:

mysql -e 'show engine innodb status \G' | grep 'undo \| log \| entries'

Give me nice info, about the time – more or less it will take 17 days to complete …

Alter block the table, so blocking production table for 17 days is not a good option. 😉

I try to investigate some great tools on the net, like:

  1. Pt-online-schema-change – Percona
  2. gh-ost – GitHub's Online Schema Migrations for MySQL

I have also read the documentation, and the limitation section of the above tools:

gh-ost limitations

  • does not support triggers at all
  • does not support foreign keys at all

pt-online-schema-change limitations

  • The use of triggers means that the tool will not work if any triggers are already defined on the table.

  • Does not provide any good way of altering tables which contain foreign keys

My example table have triggers and foreign keys…

Could you give me some advice, how to deal with this alter?

I have MySQL 5.6.
I use GTID (row-based) replication.

I will be very grateful for advice!

Best Answer

You are much better off running the ALTER TABLE like this

ALTER TABLE `example` ADD `example_column` TINYINT(1) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

What could be the benefit ? Defining a column whose default value is NULL will not attempt to increase the size of the row. This is true for VARCHAR. Honestly, I'm not sure this is true for numeric. (INT, TINYINT, etc). You need to test this (I mentioned testing this 2 years ago (Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows?)). Consequently, there should be no downtime.

The downside to this would be changing your software to deal with a NULL numeric column.

If you concerned about foreign keys, turn them off in your session beforehand

SET foreign_key_checks = 0;
ALTER TABLE `example` ADD `example_column` TINYINT(1) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

If you have triggers and can live without it for the duration of the load, drop the trigger, do the ALTER TABLE, and recreate the trigger.

CAVEAT: Make sure you are using the latest version of MySQL 5.6 (at least 5.6.23) to avoid foreign key issues with ALTER TABLE.

Please note that I did not mention using third party tools. This is just straight up MySQL.

GIVE IT A TRY !!!

Related Question