2

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.

2 Answers 2

1

I have mentioned ALGORITHM=INPLACE in two other posts

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.

0

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

1
  • I know about this tool, but my question is if one can simply use "algorithm=inplace, lock=none" when altering the table and be able to do concurrent read/writes without data loss.
    – dukebody
    May 8, 2015 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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