Background
The Skinny
Performing schema changes often requires extended downtime for applications. This is due to MySQL needing to rebuild tables for common schema change operations. Tools like pt-online-schema-change have been written to try to overcome the downtime associated with schema changes, however they are complex and put a high load on the database. Amazon’s Aurora improves some schema changes operations, but still requires a table rebuild for common operations like adding a column using before or after, or simply to add a column with a default value. Rebuilding a table with millions of rows can take hours and prevent writes to that table the entire time.
How Can Tungsten Clustering Keep Applications Running?
How Does It All Work?
Tungsten Clustering provides High Availability to off the shelf MySQL, MariaDB, and Percona Server. It also allows maintenance operations to be performed on cluster nodes with no downtime to applications (Read about zero downtime maintenance here: https://www.continuent.com/how-to-architect-mysql-for-zero-downtime-maintenance/). To perform zero downtime maintenance in Tungsten Clustering, we simply perform our maintenance on each slave, then promote a slave to master, and perform our maintenance on the old master.
It would be tempting to think this exact process would work for schema changes, but replication could break if slaves’ schemas differ from the master. The secret to making this work lies in the power of filters included in the Tungsten Replicator.
Solution
How To Configure the Replicator to Handle Online Schema Changes
The solution is simply to tell the replicator to ignore the new and/or removed columns, until all nodes have been updated. We can use the dropcolumn filter to do this. In the defaults section of tungsten.ini, add:
svc-extractor-filters=colnames,dropcolumn
property=replicator.filter.dropcolumn.definitionsFile=/opt/continuent/share/schemachange.json
Now we just need to create the file /opt/continuent/share/schemachange.json to define the column list to ignore:
[
{
"schema": "schema_name",
"table": "table_name",
"columns": ["column1","column2","column3"]
}
]
After updating the configuration on all nodes, run the schema changes on a slave. Let it finish and have replication get current. Repeat for other slaves. Then, switch to a new master, and run the schema changes on the old master and let replication get current. We have just applied schema changes with zero downtime!
Finally, we need to empty the /opt/continuent/share/schemachange.json file and restart the replicators so that the changes are active in the cluster. At this point, applications can start using the new schema.
For full details about the online schema change procedures, please visit our documentation page at https://docs.continuent.com/tungsten-clustering-6.0/operations-schemachanges.html
Summary
The Wrap-Up
By combining zero downtime maintenance and the power of replication filters, we can easily do complex schema changes on MySQL that would potentially take our applications offline for hours. Because all of our maintenance is done on slaves only, performance of our master is unaffected, and long maintenance windows dedicated to schema changes are no longer needed.
To learn about Continuent solutions in general, check out https://www.continuent.com/solutions
The Library
Please read the docs!
For more information about monitoring Tungsten clusters, please visit https://docs.continuent.com.
Tungsten Clustering is the most flexible, performant global database layer available today - use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!
For more information, please visit https://www.continuent.com/solutions
Want to learn more or run a POC? Contact us...
Comments
Add new comment