Selective pt-online-schema-change Can Trigger Galera Stalls
A selective WHERE filter in pt-online-schema-change can trigger a production stall on a Galera-based cluster of the open-source relational database, according to a detailed blog post published on September 28, 2026 by Corrado Pandiani. The post describes a customer engagement with a clustered version of this database (often called a Galera cluster) in which a routine online schema change that copied only the newest rows led to severe Galera Flow Control, heavy overhead, and a cluster that had to be restarted after staying locked for dozens of minutes. The root cause was not the schema change itself but the interaction among adaptive chunk auto-resizing, highly selective copying of the newest rows, clustered replication, and InnoDB checkpointing.
The scenario begins with a large table holding hundreds of millions of rows. Instead of rebuilding the entire table, the team wanted to copy only a subset of rows and add a new index. The command looked like this: pt-online-schema-change --alter "ADD INDEX idx_status(status)" --where "created_at >= NOW() - INTERVAL 30 DAY" D=test,t=events --execute --force. In plain terms, pt-online-schema-change (pt-osc) is a tool that alters a table's structure while the original table keeps serving traffic, and the --where option limits the work to rows whose created_at date falls within the last 30 days. This approach is common when historical data can be ignored, only active records require modification, or a migration is staged to reduce total time.
Normally, pt-osc's copy phase adjusts the size of each chunk of rows to keep the work close to a configured execution time, with a default of 0.5 seconds. If chunks complete too quickly, the tool increases the next chunk size; if chunks slow down, it decreases the size. This adaptive chunk auto-resizing works well on tables with relatively uniform row density because every chunk contains a similar mixture of rows to copy and rows to skip. The problem appears when most scanned rows are discarded by a selective --where condition. In the example, the table had 1 billion rows: 990 million did not match the filter and only 10 million did. The matching rows sat at the end of the clustered index, because the created_at values increased in step with the primary key and the primary key boundaries were combined with the WHERE condition. For a long time, pt-osc scanned chunks where almost every row was rejected, making the copy operation extremely fast. The adaptive algorithm interpreted that speed as "chunks are too small" and kept raising the chunk size. By the time the scan reached the first matching rows, the chunk size had grown dramatically, so instead of copying a few thousand rows, pt-osc might suddenly copy hundreds of thousands or even a million rows in a single transaction.
When that oversized transaction hits a Galera cluster, the problem shifts from a quiet scan to a replication storm. Galera is a synchronous replication technology that keeps multiple database nodes identical, and it replicates every copied chunk as a write set, which is the group of row changes sent to the other nodes. Very large transactions produce very large write sets, long certification phases (the process where nodes check that the writes do not conflict), increased apply queues, longer replication latency, and ultimately Flow Control activation. Flow Control is Galera's built-in mechanism to pause writes on a fast node when other nodes fall behind. Once it starts, cluster throughput decreases sharply, application commits begin waiting, and in extreme cases the cluster appears almost frozen until every node has fully applied the oversized transaction. The post notes that ironically the adaptive algorithm, designed to optimize throughput, creates exactly the workload pattern Galera dislikes most.
InnoDB, the default storage engine of the open-source relational database, suffers as well. As soon as the first large chunks began processing, the writer node's error log showed a warning like "[Warning] [MY-014084] [InnoDB] Threads are unable to reserve space in redo log ... Consider increasing innodb_redo_log_capacity." The redo log is a record of changes used to recover after a crash, and when a very large transaction arrives, writing threads cannot find enough free space because the log_checkpointer consumer is falling behind. In practice this forces the server to trigger checkpoints more frequently and on a larger scale, adding overhead. After Galera replicated the write set to the other nodes, the same type of message appeared on those nodes during the apply phase. Typical symptoms include Flow Control percentage rapidly increasing, a growing wsrep_local_recv_queue (the queue of writes waiting to be applied locally), high replication lag, application commits waiting, higher CPU utilization, large spikes in transaction apply time, and more frequent InnoDB checkpoints on all nodes. The schema change may still finish, but production traffic can be severely impacted; in the worst case described, the cluster stalled and had to be restarted.
The first and most effective proposed fix is to disable adaptive chunk resizing whenever a selective --where clause is used. Instead of continuously increasing chunk sizes, keep them fixed throughout the operation by including --chunk-time = 0 and --max-flow-ctl = 0 in the command. Setting --chunk-time to zero stops pt-osc from auto-adjusting chunk size, so query times vary but query chunk sizes do not. An alternative is to specify a value for --chunk-size explicitly and omit --chunk-time. With the adaptive algorithm bypassed, chunk execution becomes predictable, transaction size remains bounded, and Galera never receives unexpectedly massive write sets. The overall schema change may take slightly longer, but cluster stability improves dramatically, which is usually the preferable trade-off in production.
Another parameter to 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 that percentage exceeds the threshold, resuming once the cluster drops back below it. Setting --max-flow-ctl to zero (or very close to it, such as 1) makes pt-osc stop copying rows at the very first sign that any node has entered flow control, rather than tolerating some pause time before backing off. The analysis calls this the safest possible stance for a production cluster, especially one already showing checkpointer-lag warnings, because any additional write pressure from the migration's chunked copy could tip an already struggling node into a much bigger stall. The near-zero threshold trades migration speed for safety, letting the tool defer to the cluster's real-time capacity and only advance when there is genuinely no contention.