Scaling the Unscalable: A “Replica Pivot” Strategy for Online Schema Changes on multi-TB MySQL Instances
In the world of high-scale database management, some requirements keep DBAs up at night. These are the “structural emergencies” — the realization that your most critical table, the one handling thousands of transactions per second, needs a fundamental architectural change.
Whether you’ve finally hit the 2.14 billion row limit of an INT primary key and need to move to BIGINT, or you’ve realized that your XTB “orders” table desperately needs partitioning to stay performant, you face the same wall: The Scale.
When you are dealing with multi-terabyte tables in a MySQL, standard operations aren’t just slow; they are service-ending events. This post details the “Replica Pivot” strategy — a battle-tested approach to performing massive schema changes without taking your business offline.
The High-Stakes Scenarios
These are the two most common “nightmare” scenarios where traditional DDL fails:
- The INT Overflow: Your primary key is an INT. You’ve hit 2 billion rows. If you don’t convert to BIGINT immediately, your database will stop accepting new writes.
- The Partitioning Pivot: A massive legacy table is becoming unmanageable. To improve maintenance windows and query performance, you need to implement Range or Hash partitioning on a table that is already several terabytes in size.
In both cases, a standard ALTER TABLE will lock your production environment for days.
The Live Production Dilemma: Why isn’t this easy?
Performing schema changes on a live, multi-terabyte production environment is fraught with complexity. When you run a heavy operation on a “live” master, you aren’t just changing a definition; you are fighting for resources against your own users.
The Complexity Factors
- Metadata Locking: Even “Online DDL” in MySQL requires brief metadata locks at the start and end of the operation. In a high-TPS environment, waiting for these locks can cause a “pile-up” of application connections, leading to a site-wide outage.
- I/O and CPU Saturation: Rewriting a XTB table is an I/O-intensive nightmare. It consumes massive amounts of IOPS and throughput, which can starve your production read/write queries, leading to increased latency.
- The “Copy” Problem: Most schema changes create a hidden temporary table. This means for a XTB table, you suddenly need another XTB of free space instantly, and your database has to manage double the writes.
Common Approaches & Their Impact
The Strategy: The “Replica Pivot”
The core philosophy here is to perform the heavy, blocking I/O operations on a standalone instance, then use MySQL’s native replication to bridge the gap back to production.
Lets use this strategy to address the INT overflow problem.
Phase 1: The Transformation (Offline)
- Clone the Environment: Create a MySQL Read Replica in the same zone/region with the identical configuration as your Master. Let’s call this Replica-A.
- Break the Chain: Promote Replica-A to a standalone instance. It is now a point-in-time snapshot of your production data, but it is no longer receiving live updates from Master.
- Perform the Surgery: Run your massive DDL statement on the promoted instance.
- For Datatype Change: ALTER TABLE orders MODIFY id BIGINT;
- For Partitioning: ALTER TABLE orders PARTITION BY RANGE…;
Note: This will take hours or even days. Since this instance is isolated, your production Master remains fast and healthy.
- Configure Type Conversion Flags: This is the most critical technical step. To ensure that the promoted instance can eventually talk to the original Master (which still has the old schema), you must set specific flags on the promoted instance:
- slave_type_conversions = ALL_LOSSY
- slave_type_conversions = ALL_NON_LOSSY
Why? This tells MySQL to allow replication even if the datatypes or structures don’t match perfectly, as long as the data is compatible.
Phase 2: The Re-Sync (Online)
- Re-attach to Master: Use the external master configuration or MySQL’s replication tools to point your modified instance back to the original Master.
- Catching Up: The modified instance will begin reading the Binary Logs from the Master. It will replay every transaction that happened while the “surgery” was taking place.
- Monitor Lag: Wait for the replication lag to reach zero. Because the schema change is already done, the replica is simply inserting new data into your new BIGINT or Partitioned table structure.
Note: For detailed steps, you can refer this blog
Phase 3: The Precision Cutover
Once the lag is zero, your modified instance is essentially a “Future Version” of your production database.
- Validation: Perform functional testing on the replica. Ensure the application can read the new BIGINT range or query the partitions correctly.
- The Maintenance Window: Stop traffic to the original Master. Wait for the final few transactions to sync (lag = 0).
- Remove *LOSSY* flags and edit database parameters like original source.
- The Promotion: Promote the replica to be the new Master.
- DNS Update: Change your application’s database endpoint (or update your Load Balancer) to point to the new Master.
Summary of Benefits
By using the Replica Pivot, you shift 99% of the performance degradation and locking issues to an isolated instance. The only downtime your users experience is the few minutes it takes to swap the connection strings and promote the instance.
Scaling the Unscalable: A “Replica Pivot” Strategy for Online Schema Changes on multi-TB MySQL… was originally published in Google Cloud – Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
Source Credit: https://medium.com/google-cloud/scaling-the-unscalable-a-replica-pivot-strategy-for-online-schema-changes-on-multi-tb-mysql-dff6ee96f9eb?source=rss—-e52cf94d98af—4
About the Author
