1

MySQL 5.6 includes functionality called Online DDL which allows ALTER operations to proceed on tables without applying a LOCK to the table (technically referred to as allowing Concurrent DML).

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

From what I can see, the mechanics behind this are that a copy of the table is made, on which the ALTER is made, and the data is copied between the source and destination tables. At the end of the process, the tables are renamed.

Any DML operations (UPDATE/INSERT) are cached during the ALTER process and then applied to the new table once the ALTER process is complete.

Is this correct?

For instance, if my ALTER takes 24 hours, is it the case that I won't see the results of DML operations for 24 hours, or is there some internal mechanism that cached DML operations are updated on the source table and then updated in the new table too?

I understand that the Percona pt-online-schema-change tool

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

does something like this, but this tool can take up to 4 times as long to perform the table change as a native MySQL ALTER operation.

migrated from serverfault.com Mar 16 '16 at 17:34

This question came from our site for system and network administrators.

  • As far as I know, the DDL will not be visible for 24 hours (as per your example), but the DML will 'always' be visible. – Rick James Mar 20 '16 at 18:15

Your Answer

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

Browse other questions tagged or ask your own question.