Experiences with online DDL in Production with 5.6

[rreddy]

Hi, I have upgraded/moved recently from MySQL 5.5 To Percona Server 5.6 and for the first time I am going to execute some online DDL’s while the sites are up and the tables that are being modified are pretty big several of them have 10 million records and the largest one has 30 million records.

Given the situation, I would like to know if anyone did online schema changes in their Production environment, if so did you see any issues specifically when the ALTER runs for a very long time accumulating all the changes in a log file with max default maxsize at 128MB (innodb_online_alter_log_max_size).

1. Is the default size enough for this log file for ALTER’s involving BIG tables and on a busy site.
2. According to the docs adding a column involves copying the table, but it does let me specify the ALGORITHM=INPLACE, LOCK=NONE when adding a new column? does this mean mysql will not do a copy table for adding new columns?
3. Did you ever fill out the online ddl log file and had to specify a bigger size at the session level.

Appreciate any feedback.

Thanks,
Ramki

[rreddy]

Looks like nobody is using online DDL in production yet. Anyway I will post my experience from last night, it was little scary but overall it was ok, not great.

1. Had several website outages when applying these, especially on the busy tables, mysql tries to get an exclusive lock on the table at the begin and end, at this time if you have any long running jobs, it will just sit there and wait to get an lock on that table. Had to kill few BO jobs to get it going.

2. Once the ALTER completes on master, slave starts to act when it cannot acquire a lock on that table as this is customer facing and max out connections all waiting on metedata locks, at this point only option was to kill the ALTER on slave that cleared up the connections, but strange thing happened when restarted the slave, it just Skipped that ALTER and went on to do it’s business as usual, I had to run this ALTER separately on the slave to put it in sync, ALTER was adding a NOT NULL column and it not touched by the app yet.

3. 30 million record table took 30 minutes, couldn’t track the online alter log usage, but looks like 128MB was enough on a pretty busy table and it took 30 seconds to apply all the changes from this log to the table, during this time web sessions started piling up waiting on locks.

4. Overall, it was not a pleasant experience having to deal with all the issues, but MySQL 5.6 online DDL is not 100% online, it does cause locking for a brief period and can cause outages and can cause slave to skip some statements.

[niljoshi]

Hi,

In this situation, I would suggest to use percona tool pt-online-schema-change. http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html
“pt-online-schema-change alters a table’s structure without blocking reads or writes. emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it.”

As this is read-write tool, do not use this tool before reading its documentation and checking your backups carefully. I would suggest to test is on stage server first and then use on prod.