Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows?

Question

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.

Answer 1

I have mentioned ALGORITHM=INPLACE in two other posts

• Mar 27, 2014 : When tables are locked does MySQL queue queries?
• Feb 12, 2015 : How to evolve MySQL schema while maintaining integrity

In the second post, I named some caveats on using online DDL

The online schema change feature uses the folder mapped in tmpdir. Make sure the copy of the table can fit entirely in tmpdir. For example, if tmpdir is mapped to /tmp and the table you are changing is 7GB, there has to be more than 7GB free in /tmp. If /tmp is not large enough, please consider mounting /tmp on a large volume.

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 and SELECTs

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 during INSERTs and SELECTs.

This new feature of online DDL is Oracle's answer to Percona's pt-online-schema-change.

Answer 2

if you would want to allow read writes when Altering you might want to your percona online schema change tool from percona. This tool is safe to use when altering the table.

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

Hope this helps