Special consideration must be given when making schema changes to large tables in MySQL that use the InnoDB or MyISAM storage engines. Specifically, both engines by default will lock the table entirely while schema changes are being performed. We’re often asked by clients how changes to large tables can be made without blocking end user experiences (most other types of traffic, such as batch update processes and replication, can usually be paused). Just the thought of unnecessary delays, added costs, and other associated nuisances are causing me anxiety as I type this sentence, so I’m excited to share some solutions to help ease the burden. As always, please perform your own due diligence before trying any of these methods:
Option: Perform change on master, allow replication to slaves
This is the most traditional and native method of making schema changes to large MySQL tables. An
ALTER executed on the master will apply the changes to the table (taking anywhere from a few minutes to a few days depending on the size of the table and the speed of the underlying hardware). During this application time, the tables will be blocked for both reads and writes. Depending on the number of database connections and the configuration of your application, you’ll either reach max_connections (which will block any further new connections/queries), or you’ll experience noticeable performance degradation with queries on other databases/tables. If you can afford this downtime, or the combination of your table size and hardware performance makes you confident the changes will apply quickly, this method may work for you.
Option: Perform change on slave(s), perform a master/slave swap, apply change to old master
This method requires replication (availability of at least one master and one slave) to work. An unused slave is best, but if you’re currently using all of your slaves, try to free them up one by one before proceeding with this method. It’s always best to run schema changes on servers that are out of production to avoid user experience interruptions and increase the speed of changes by reducing contention. Once you’ve applied the schema change to at least one slave by stopping replication and running your
ALTER command(s) and allowing the slave to recover, promote that slave to master, and demote the old master to slave. Apply the changes to the other remaining slaves if necessary, including the old master. In this situation, your likely maximum possible downtime, provided the schema changes are never made on a slave that is in production for read queries, is the time it takes to perform the master/slave swap. If you can afford the technical pieces involved in a master/slave swap, the savings in downtime may be to your benefit.
Option: Use an online schema change tool Difficulty: Medium Downtime: Short This method requires the use of an external tool not native to MySQL, such as pt-online-schema-change, part of the Percona Toolkit.
While this is an incomplete list of all possible options, I hope these schema change methods are helpful. As always, I’d recommend testing in a true QA environment first. We’ve seen some users test changes on an unused slave before applying to a master. If you consider this a reliable testing method, keep in mind that replication will temporarily break as the changes are further applied to the master and then replicated to the testing slave (as they can’t be applied twice). For that situation, look into
SQL_SLAVE_SKIP_COUNTER. When used appropriately,
SQL_SLAVE_SKIP_COUNTER can reliably skip these duplicate