How a selective WHERE clause can freeze an entire Galera cluster

A maintenance command that exposed a corner case

On September 28, 2026, the Percona Database Blog published an account by Corrado Pandiani about a production incident involving pt-online-schema-change, the Percona Toolkit tool used to alter large tables without blocking writes. The case happened during a customer engagement running Percona XtraDB Cluster (PXC), the MySQL distribution with synchronous replication via Galera.

The command that triggered the problem seemed harmless: adding an index to a table with hundreds of millions of rows, restricting the copy to only recent rows with a --where clause.

pt-online-schema-change \
--alter "ADD INDEX idx_status(status)" \
--where "created_at >= NOW() - INTERVAL 30 DAY" \
D=test,t=events \
--execute --force

The logic is common: skip historical data, migrate only active records, reduce the total operation time. It works well in most cases. In this one, it nearly brought the cluster down.

How chunk auto-resize works under the hood

pt-online-schema-change copies the table in pieces (chunks) delimited by primary key ranges, applying each chunk as a separate transaction. By default, the tool adjusts the size of each chunk to keep execution time close to the value set in --chunk-time (0.5 second by default): if a chunk runs too fast, the next one gets bigger; if it runs slow, the next one shrinks.

This adaptive behavior exists to balance throughput and load automatically, without requiring the operator to manually calibrate the ideal chunk size for each table. For a typical scan, with uniform row density, the algorithm converges quickly to a stable value.

The hidden effect of a selective WHERE

The problem appears when most of the scanned rows are discarded by the --where. In the reported case, the table had on the order of 1 billion rows, of which only a small fraction matched the condition created_at >= NOW() - INTERVAL 30 DAY:

The critical detail: pt-osc chunking always uses the primary key as the boundary, and since created_at grew alongside that key, all the rows of interest were concentrated at the end of the index. For a long stretch of the scan, nearly every row read was rejected, and the copy advanced very fast.

The adaptive algorithm interprets this speed as a sign that the chunks are too small, and keeps increasing the next chunk. When the scan finally reaches the rows that match the --where, the chunk size has already grown disproportionately. Instead of copying a few thousand rows per transaction, pt-osc starts copying hundreds of thousands (or more) at once.

Why this breaks Galera

Each copied chunk becomes a write set replicated synchronously to all nodes in the cluster. Very large write sets produce longer certification phases, larger application queues, and growing replication latency. When this latency exceeds the tolerated limit, Galera activates Flow Control: the mechanism that pauses commits across the entire application to give the slowest node time to apply what it has already received.

With Flow Control active, cluster throughput collapses. In Percona's account, in extreme situations the cluster became practically frozen until the oversized transaction finished being applied on all nodes, requiring a restart after dozens of minutes stuck.

The irony is that the very mechanism designed to optimize throughput, auto-resize, ends up creating exactly the load pattern that Galera handles worst: large, synchronous, concentrated transactions.

InnoDB also suffers from large transactions

The Percona article shows that, on the node processing the write, the error log started recording warnings like this as soon as the first large chunks came in:

[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.

The warning indicates that InnoDB received a transaction too large for the free space in the redo logs, forcing more frequent and costlier checkpoints. The same type of message appeared later on the other nodes, during the application phase of the replicated write set, which helps explain why the impact was not restricted to a single server.

The symptoms in production

The list of symptoms observed in the real incident gives an idea of how much the cascading effect spreads through the cluster:

• Flow Control percentage rising rapidly;
• growth of wsrep_local_recv_queue;
• high replication lag between nodes;
• application commits stalled;
• high CPU usage;
• large spikes in transaction application time;
• more frequent InnoDB checkpoints on all nodes.

The schema migration itself usually finishes successfully. The problem is the side effect on production traffic while it runs.

The proposed fixes

In short: the simplest and most effective solution is to turn off auto-resize whenever there is a selective --where. Just fix the chunk size instead of letting it vary:

--chunk-time=0
--max-flow-ctl=0

With --chunk-time=0, the chunk size stops adjusting automatically: the time per chunk varies, but the number of rows per chunk stays constant. The same effect can be achieved by setting --chunk-size explicitly and omitting --chunk-time. The gain is predictability: the migration may take a bit longer, but Galera never receives an unexpectedly large write set.

The --max-flow-ctl parameter works as a second seatbelt. With it, pt-osc monitors wsrep_flow_control_paused (the fraction of time the node spent paused due to Flow Control) and suspends row copying whenever that value exceeds the defined limit, resuming when the cluster returns to normal operation. Setting the value close to zero makes the tool stop copying rows at the first sign of Flow Control on any node, instead of tolerating some pause before backing off, which is the safest posture for a production cluster already showing signs of stress, such as the checkpointer lag warnings described above.

A third lever is --chunk-index. By default, pt-osc chunks by the primary key (or the most suitable unique index it finds), which in the reported case meant following the same monotonically increasing column as created_at, producing the long stretch of nearly empty chunks. Forcing chunking by a secondary index uncorrelated with the --where column can distribute relevant and irrelevant rows more uniformly across chunks.

The Percona Toolkit documentation itself warns, however, that a poorly chosen chunking index hurts query execution plans, and few tables have a convenient index that is both selective enough and, at the same time, uncorrelated with the --where. For this reason, the article recommends treating --chunk-index as a secondary, table-specific optimization, keeping --chunk-time=0 and --max-flow-ctl as the main protection.

The fourth option avoids the --where altogether: splitting the operation into two steps. First, run pt-archiver just to delete the rows that no longer matter; then, run pt-online-schema-change on the already-trimmed table. This two-phase path eliminates the risk of non-uniform distribution of the rows matching the condition for good.

What changes for those running Galera in production

The case reinforces a principle well known to those working with synchronous replication: adaptive mechanisms optimized for one scenario (uniform scan) can become hostile in another (selective scan). As Pandiani puts it at the close of the article:

Sometimes, predictability is a better optimization than aggressiveness.

Corrado Pandiani, Percona

Before running pt-online-schema-change with a selective --where on any Galera or PXC cluster, it's worth checking whether the filter columns correlate with the key used for chunking. If they do, --chunk-time=0 combined with a low --max-flow-ctl stops being optional: it's what separates a smooth schema migration from a cluster frozen for dozens of minutes.