Percona Toolkit’s pt-online-schema-change (pt-osc) has long been the preferred solution for performing online schema changes with minimal downtime. Its chunk-based copy algorithm is designed to adapt dynamically to the workload, making it suitable for very large tables in production environments.
However, under specific conditions, one of its optimization mechanisms can become counterproductive.
During a customer engagement involving Percona XtraDB Cluster (PXC), we investigated a case where running pt-online-schema-change with a selective –where clause caused severe Galera Flow Control and general overhead, effectively stalling the cluster for an extended period. The issue was not caused by the schema change itself, but by the interaction between:
This article explains why it happens and proposes a set of practical mitigations that eliminate the root cause.
Consider a large table containing hundreds of millions of rows.
Instead of rebuilding the entire table, we only want to copy a subset of rows and apply a schema change like adding a new index.
The command looks similar to:
|
1 2 3 4 5 |
pt-online-schema-change \ --alter "ADD INDEX idx_status(status)" \ --where "created_at >= NOW() - INTERVAL 30 DAY" \ D=test,t=events \ --execute --force |
This is a common approach when:
At first glance this seems perfectly reasonable.
Unfortunately, it exposes an interesting corner case in pt-online-schema-change.
During the copy phase, pt-osc continuously adjusts the chunk size in order to keep each chunk copy close to the configured execution time (–chunk-time), 0.5 seconds by default.
Simplified, the algorithm works like this:
For ordinary table scans this works remarkably well because row density is relatively uniform.
The problem appears when most scanned rows are discarded by –where.
Imagine a table like this with 1 billion rows:
Suppose the matching rows are located at the end of the clustered index — and that was exactly the case here. Keep in mind that chunk selection always relies on the Primary Key boundaries starting from minimum value, but in this case those boundaries were combined (AND) with the condition passed via –where. Since the created_at values increased in step with the Primary Key, all the matching rows ended up clustered at the end of the index.
For a long time pt-osc scans chunks where almost every row is rejected. The copy operation becomes extremely fast.
The adaptive algorithm interprets this as: “Chunks are too small.” It therefore keeps increasing the chunk size.
Eventually, when the scan reaches the first matching rows, the chunk size has already grown dramatically. Instead of copying a few thousand rows, pt-osc may suddenly copy hundreds of thousands—or even a million—of rows in a single transaction.
Nothing is technically wrong. But Galera now has a completely different problem.
Every copied chunk is replicated as a write set.
Very large transactions produce:
Once Flow Control starts, cluster throughput decreases dramatically.
Application commits begin waiting.
In extreme situations the cluster appears almost frozen until the oversized transaction has been fully applied by every node.
Ironically, the adaptive algorithm—designed to optimize throughput—ends up creating exactly the workload pattern that Galera dislikes the most.
As soon as the first large chunks began processing, the following messages appeared in the PXC writer node’s error log:
|
1 2 3 |
[Warning] [MY-014084] [InnoDB] Threads are unable to reserve space in redo log which can't be reclaimed due to the 'log_checkpointer' consumer still lagging behind at LSN = 852886039088. Consider increasing innodb_redo_log_capacity. |
This indicates that InnoDB received a very large transaction, and the writing threads could not find enough free space in the redo logs because the log_checkpointer consumer was falling behind. In practice, this forces the server to trigger checkpoints more frequently and on a larger scale, which increases overall overhead.
After Galera Replication propagated the write set to the other nodes, the same type of message was observed on them as well, during the apply phase of the replicated transaction.
Typical symptoms include:
The schema change itself may still complete successfully, but the impact on production traffic can be severe. In the worst scenario this can lead to a complete stall of the cluster due to excessive flow control. This actually happened in production, and the cluster had to be restarted after remaining locked for dozens of minutes.
Here are a few options, starting with the simplest and most effective one: disable adaptive chunk resizing whenever a selective –where clause is used.
Instead of continuously increasing chunk sizes, keep them fixed throughout the operation.
Simply include the following options in the command:
|
1 2 |
--chunk-time = 0 --max-flow-ctl = 0 |
If –chunk-time option is set to zero, the chunk size doesn’t auto-adjust, so query times will vary, but query chunk sizes will not. Another way to do the same thing is to specify a value for –chunk-size explicitly, instead of leaving it at the default, and omit the option –chunk-time.
The adaptive algorithm is bypassed. Chunk execution becomes predictable. Transaction size remains bounded. Galera never receives unexpectedly massive write sets. The overall schema change may take slightly longer, but cluster stability improves dramatically. For production environments this is usually the preferable trade-off.
Another parameter you should consider is –max-flow-ctl.
With –max-flow-ctl=PCT, pt-osc periodically checks wsrep_flow_control_paused (the percentage of time the node has spent paused for flow control) and automatically pauses row copying whenever it exceeds the threshold, resuming once the cluster drops back below it.
Setting –-max-flow-ctl to zero (or very close to it, e.g. 1) makes pt-online-schema-change stop copying rows at the very first sign that any node has entered flow control, rather than tolerating some pause time before backing off — which is the safest possible stance for a production cluster, especially one that’s already showing signs of stress (like the checkpointer-lag warnings, where large transactions were pushing InnoDB’s redo log reclamation to its limit): any additional write pressure from the migration’s chunked copy could tip an already struggling node into a much bigger stall, so a near-zero threshold trades migration speed for safety, letting the tool fully defer to the cluster’s real-time capacity and only advance when there’s genuinely no contention, which is generally the right call for critical, latency-sensitive production workloads where a slower-but-safe schema change beats a fast one that risks a cluster-wide write freeze.
A related lever worth mentioning is –chunk-index. By default, pt-online-schema-change chunks the table using the primary key (or the most appropriate unique index it can find), which is precisely why, in our case, chunk boundaries tracked the same monotonically increasing column as created_at and produced the long stretch of near-empty chunks described above. If the table has a secondary index whose ordering is not correlated with created_at, forcing the tool to chunk on that index instead (–chunk-index=idx_name) can spread matching and non-matching rows more evenly across chunks, so the adaptive algorithm never sees the extended low-density run that drives chunk size to unsafe levels in the first place. That said, this is not a substitute for –chunk-time=0 and –max-flow-ctl: pt-osc’s own documentation warns that a poorly chosen chunking index can hurt performance, since it forces query plans, and few tables have a convenient index that is both selective enough to chunk on and uncorrelated with the –where condition. In practice, we recommend treating –chunk-index as a secondary, table-specific optimization, with fixed chunk sizes and flow-control awareness remaining the primary safeguard.
A third option is to avoid the –where clause altogether and split the task into two phases instead. First, run pt-archiver solely to delete the rows you no longer need; then run pt-online-schema-change to alter the table. This two-step procedure avoids any risk stemming from the non-uniform distribution of the matching rows.
pt-online-schema-change remains one of the best tools available for online schema migrations.
Nevertheless, when using –where to copy only a small subset of rows on Percona XtraDB Cluster, adaptive chunk resizing can unintentionally generate oversized transactions that trigger excessive Galera Flow Control and temporarily stall the cluster.
Keeping chunk sizes fixed eliminates this feedback loop, producing smoother replication behaviour and reducing the operational risk of online schema changes. Other fixes are also available.
Sometimes, predictability is a better optimization than aggressiveness.
Resources
RELATED POSTS
