I have a large (3M rows) InnoDB table in MySQL 5.6.21 in production and I want to add some columns.
From the MySQL Documentation on Overview of Online DDL
I understand that, even if this is an expensive operation that might degrade performance, concurrent DML is allowed as long as I don't add auto-increment columns, and concurrent queries (SELECTs) are always allowed.
Is this right? Do I have to explicitly state ALGORITHM=INPLACE
and LOCK=NONE
in the alter operation for this to be possible?
I'll be adding mainly unindexed, not null VARCHAR columns.
Best Answer
I have mentioned
ALGORITHM=INPLACE
in two other postsMar 27, 2014
: When tables are locked does MySQL queue queries?Feb 12, 2015
: How to evolve MySQL schema while maintaining integrityIn the second post, I named some caveats on using online DDL
What I have mentioned my seem like overkill to discuss. Notwithstanding, the point I am making, to answer your question, is yes you can do
INSERTs
andSELECTs
CAUTION: You should test adding
VARCHAR
that has a default of NULL, an INT with a default and without a default in a Staging server just to see if you can live with any slippage in performance duringINSERTs
andSELECTs
.This new feature of online DDL is Oracle's answer to Percona's pt-online-schema-change.