In an attempt to reduce downtime on our production servers we are considering using one of the following tools to do schema migrations without any downtime:
- Percona Toolkit — pt-online-schema-change
- GitHub’s Online Schema Migrations for MySQL
- Facebook’s OnlineSchemaChange
Once we had started the process of testing these tools in lower database levels it became clear that some mix of foreign keys and potentially the frequency of locks on certain tables was making percona fail to succeed and in more than one case, corrupt the database.
To give an example of this corruption I will use
- Copies data to
- Start moving foreign keys from
- Timeout midway through
- Tries to rollback but still times out due to queries/locks on
- The tool fails with half foreign keys pointing to
table_a_newand the other half pointing to
Some of these tables need to be fairly transactional as they relate to selling limited inventory with a high level of concurrent users; however, there is the possibility that they are queried more often than necessary dues to inefficient use of an ORM.
Two of the three tools above (gh-ost and fb osc) specifically do not support any form of foreign keys.
Is this an indicator that we should be considering dropping our foreign keys if we want to be able to do schema migrations without downtime?
Are there any other considerations that should be made before exploring this option?