MySQL InnoDB on Amazon RDS – Maintaining a Production Big Table

amazon-rdsinnodbMySQL

I have a very large table: Table1 in a production environment which I need to alter a column in.

What's the best preferred way to do so without down time?

Option 1:

CREATE TABLE Table1_new LIKE Table1;
INSERT INTO Table1_new SELECT * FROM Table1;
ALERT TABLE Table1_new ADD COLUMN a;

ALERT TABLE Table1 RENAME Table1_bu;
ALERT TABLE Table1_new RENAME Table1;

###And then
INSERT IGNORE INTO Table1 SELECT ... FROM Table1 WHERE id > ..;

MariaDB: 5.6.44

InnoDB: 5.6.44

Best Answer

CREATE TABLE Table1_new LIKE Table1;
INSERT INTO Table1_new SELECT * FROM Table1;
ALERT TABLE Table1_new ADD COLUMN a;         --slow because of big table

is not optimal and has a problem. Better:

CREATE TABLE Table1_new LIKE Table1;
ALERT TABLE Table1_new ADD COLUMN a;          -- fast because table empty
INSERT INTO Table1_new SELECT *, NULL FROM Table1; -- needs value for `a`

The problem is -- What if Table1 is being written to? Such DML would be lost.

ALERT TABLE Table1 RENAME Table1_bu;
ALERT TABLE Table1_new RENAME Table1;

Should be done as a single statement (atomically):

RENAME TABLE Table1 TO Table1_bu,
             Table1_new TO Table1;

Plan A: (Your code, after fixes): Viable only if no modifications will happen.

Plan B: pt-online-schema-change (assuming you have not Triggers on the table).

Plan C: oak... -- No; it seems not tok be maintained

Plan D: Upgrade to MySQL 8.0 (perhaps MariaDB 10.3) so you can get ALTER TABLE .. ADD COLUMN .. ALGORITHM=INSTANT

Related Question