-
When pt-online-schema-change “where” Meets Galera: Understanding Chunk Auto-Resize and Flow Control
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:
adaptive chunk auto-resizing
highly selective copying of the newest rows
clustered replication in Galera
InnoDB checkpointing
This article explains why it happens and proposes a set of practical mitigations that eliminate the root cause.
The Scenario
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:pt-online-schema-change \
--alter "ADD INDEX idx_status(status)" \
--where "created_at >= NOW() - INTERVAL 30 DAY" \
D=test,t=events \
--execute --forceThis is a common approach when:
historical data can be ignored
only active records require modification
performing staged migrations
reducing migration time
At first glance this seems perfectly reasonable.
Unfortunately, it exposes an interesting corner case in pt-online-schema-change.
How Chunk Auto-Resize Works
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:
if chunks execute too quickly, increase the next chunk size
if chunks become slower, decrease the size
repeat until reaching a stable value
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.
The Hidden Effect of a Selective WHERE Clause
Imagine a table like this with 1 billion rows:
Rows
Match WHERE
990 million
No
10 million
Yes
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.
Why Galera Suffers
Every copied chunk is replicated as a write set.
Very large transactions produce:
large write sets
long certification phases
increased apply queues
longer replication latency
Flow Control activation
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.
Why InnoDB Suffers too
As soon as the first large chunks began processing, the following messages appeared in the PXC writer node’s error log:[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.
Symptoms
Typical symptoms include:
Flow Control percentage rapidly increasing
Growing wsrep_local_recv_queue
High replication lag between nodes
Application commits waiting
CPU utilization increasing
Large spikes in transaction apply time
More frequent InnoDB checkpoints on all the nodes
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.
The Proposed Fixes
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:--chunk-time = 0
--max-flow-ctl = 0If –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.
Conclusion
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.
The post When pt-online-schema-change “where” Meets Galera: Understanding Chunk Auto-Resize and Flow Control appeared first on Percona.
-
MyVector v1.26.9: Keeping Up With MySQL Innovation
MySQL 26.7 Innovation Support Lands
September 22, 2026 · GitHub Release
MySQL just changed the rules.
With MySQL 26.7, Oracle has moved to a new calendar-based versioning model for Innovation releases. For MyVector, that means one thing:
We need to keep up.
MyVector v1.26.9 adds MySQL 26.7 Innovation support, but the more interesting story is what happened beneath the surface.
This release puts the Component architecture introduced in v1.26.5 through a much more serious test.
From architecture to reliability
When we introduced the MySQL Component architecture in v1.26.5, the goal was straightforward: build MyVector in the direction MySQL itself is taking.
Now we are testing what happens when things don’t go perfectly.
v1.26.9 adds proper Component lifecycle testing, including installation, uninstallation, failed removal, and deinitialization rollback.
Because installing something is easy.
Installing, removing, failing, restarting, and recovering correctly is the real test.
HNSW gets harder to kill
There is also some important HNSW work in this release.
A number of failure paths around HNSW index creation and persistence have been fixed, including a case where an index build could crash mysqld.
That obviously isn’t acceptable.
We also fixed configuration handling so type=hnsw is correctly honored, and persistence failures are now surfaced instead of disappearing silently.
If MyVector cannot save an index, you should know about it.
Does it survive a restart?
This became an explicit test in v1.26.9.
It is one thing to create an index and run a vector search.
It is another thing to restart MySQL and have that index come back.
The new lifecycle tests verify that persisted indexes are actually reloaded from disk.
That is an important distinction as MyVector moves from an interesting MySQL extension toward something people can consider for real workloads.
The release process got better too
v1.26.9 also expands the testing and release pipeline around:
MySQL 26.7 compatibility
Component lifecycle
HNSW
Persistent indexes
Online updates
Stress testing
Stanford dataset smoke tests
Benchmark failure detection
Docker builds
What’s next?
The original MyVector idea hasn’t changed:
Why move your data to another database just because your application needs vector search?
The answer is becoming more interesting as MySQL evolves.
With MyVector, the goal is to bring:
SQL + transactional data + vector search + full-text search + AI workloads
into the same database environment.
MySQL 26.7 brings a new Innovation release.
MyVector 1.26.9 makes sure we’re ready to follow it.
v1.26.5 was about introducing the Component architecture.
v1.26.9 is about proving it.
Get MyVector
MyVector v1.26.9
GitHub Repository
Documentation
MyVector is open source. Feedback, issues, benchmarks, and contributions are welcome.
-
Open FIPS Build, Vectors, and Binlog Server: Marco Tusa at Percona Live
This post collects the main things Marco Tusa covered in his keynote at Percona Live 2026 in Amsterdam: where Percona stands on MySQL, which features landed in the server, and what holds the MySQL community together.
Watch it here: “I Was Going to Show You a Roadmap”, about 18 minutes.
Start with the community, because the rest of it follows from there. In 2011, after the Sun and Oracle acquisitions, O’Reilly stopped the MySQL Conference, and Percona kept a conference going when nobody knew what came next. The community scattered again more recently, and Peter Zaitsev and others regrouped it in the OurSQL Foundation. MySQL, MariaDB, and Percona ship competing solutions. The people running and building them are one community, with one shared interest: this environment stays usable and open.
Oppose the decision, not the people
MariaDB moved later Galera development inside an enterprise circle. The MariaDB Foundation pushed back. MariaDB keeps its own version, and others can still improve it. Percona keeps shipping Percona XtraDB Cluster (PXC). PXC has been in production since about 2012, and it was not built as a reply to that dispute. The argument is with the decision, not with the people who made it.
The same rule applies inside Percona. A FIPS implementation in the Pro Build program was available to customers and not to everyone else. That broke the mandate to stay open. The code is open now: it is the default, and everyone can get it.
What is already there
This is work in progress, not a list of future dates.
A binlog server, shown in Yura Sorokin’s talk “Beyond the Dumb Pipe.” It is early: a bicycle that should become a full server.
Vector search inside Percona Server for MySQL, with no separate system beside it.
Monitoring and observability an agent can use to see a query more clearly.
MyRocks and PXC, still in active development. They are first-class parts of the server, not items added to fill a list.
MySQL Enterprise features carried from version to version. Each version needs merges and adjustments. That work stays in the freely available server.
The written vision is at percona.com/mysqlvision. The roadmap follows it, in public.
Answer the MariaDB questionnaire from Percona Live, and share the answer. MySQL Calculator is an open source project you can try through the operator. If something is wrong, open an issue.
More sessions from the event are in the Percona Live 2026 Amsterdam playlist. The same recordings are on the conference site, in Videos.
-
MySQL 26.10.0 Early Access Is Here
The MySQL 26.10.0 Early Access Release is now available, giving developers and database teams an early opportunity to preview, evaluate, and provide feedback on the next evolution of MySQL. Early Access releases are where curiosity becomes contribution. Whether you’re testing an application stack, validating upgrade plans, or simply eager to see what’s coming, this is […]
-
Database governance in the AI era: Framework, risks and best practices
AI database governance can get overlooked when teams rush to connect AI tools to production data. IBM’s 2025 research found that 97% of organizations that reported a breach involving an AI model or application lacked proper AI access controls. The risk is easy to see. Give an AI agent too much access and it can […]
The post Database governance in the AI era: Framework, risks and best practices appeared first on Devart Blog.
|
