Updating Piwik and MySQL 5.6 Online DDL

General questions
1

I have been toying around with an idea to leverage Online DDL for near-hitless update.

MySQL 5.6 has a new feature called Online DDL that allows schema changes without locking the table for writes. Internally it works by copying the table row by row while making the schema change (in the canonical “ALTER TABLE … ADD” example) and at the end just rename the file over:
http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

We have a rather large Piwik installation so the database is large too. Even on SSD storage updating Piwik is painful due to the multi-hour schema change (ALTER TABLE) to two of the biggest tables. I would like to avoid this long downtime. Does anyone have feedback on whether this could work or maybe even experience trying it:

  1. Extract schema changes from core/Updates/*.php between current version and destination version.
  2. Combine all schema changes to the same tables in different versions to single statements if updating multiple versions.
  3. If there are schema changes to only one of the two large tables and the change(s) can be done in a single statement this update is a candidate for “manual” update.
  4. Prepare statements to do schema changes to the tables: order schema change to the large table in the beginning (after this statement finishes tracking is lost until the whole process is done) and rest (taking negligible amount of time) at the end.
  5. “Nop” out all schema changes in the update files.
  6. Run the prepared schema changes.
  7. Disable tracking & user interface at the end of the first schema change (to one of the large tables). Perhaps by scripting the schema changes as separate calls to mysql binary and disabling Piwik after mysql for the first statement returns.
  8. After all the schema changes are finished update Piwik files and run updater.
  9. Enable tracking & UI.

Any other ideas for hitless update? We do use replication but if I remember right Piwik used some SBR-unsafe statements which caused me to enable RBR/mixed mode - that would mean (ab)using circular replication for online schema changes cannot be used either. The online schema change tools I have considered already.

inb4 “see paid consulting”…