0

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

  • What do you mean by very large? And what version of MySQL? – Joe W Nov 4 at 13:32
  • About 40GB, version updated in question. – omri Nov 4 at 13:44
  • You should use the [Percona Toolkit](You should use the Percona Toolkit, in particular the pt-online-schema-change tool, see here. – Vérace Nov 4 at 14:26
  • You could also look at Shlomi Noach's kit - the oak-online-alter-table - no longer actively developed AFAIK. – Vérace Nov 4 at 14:35
  • @Vérace I am using AWS RDS instance, is it possible to use the percona toolkit on AWS RDS? – omri Nov 4 at 15:11
1
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

  • thx Rick this is very interesting, ALGORITHM=INSTANT looks amazing. I think i will use pt-online-schema-change, looks solid. – omri Nov 5 at 8:36
  • I am trying to use pt-online-schema-change, adding a column works great, but change column type encounters an error, I cant find any explanation in the docs, is changing the data type is not possible using pt-online..? – omri Nov 7 at 8:36
  • Ok i had a syntax error, changing column type works as expected – omri Nov 7 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.