Faster pipelines were just the warm-up. MySQL backfill optimization is the real key to turning speed into a number you can build a recovery plan around.
In part one, we shared how we rebuilt the Pipelinewise MySQL connector for speed and turned sluggish resyncs into something dependable. The improvements were real and the benchmarks moved in the right direction, so we won’t re-tread them here.
But raw speed was never the finish line. The real question the team was chasing was sharper than that: when disaster strikes, how long until you’re actually recovered?
This is the story of what it took to turn a set of performance wins into a documented, trustworthy Mean Time to Recovery, including the dead ends we ruled out along the way and the tooling now baked in to keep proving it.
The Question That Mattered Most
Speed improvements are satisfying in isolation, but they don’t mean much until they’re tested against a scenario that actually hurts. That scenario arrived during an outage affecting a co-replica, when the team had to reason through recovering a schema roughly three times larger than anything tested before, on the order of 3 billion rows.
Some smaller pipelines resynced in under an hour, exactly as part one’s work had promised. But schemas in the 20M-to-350M row range were still running past the 24-hour mark, with several even larger schemas still waiting behind them. That gap, small tables behaving beautifully while larger ones remained an open question, is exactly what this next phase of work set out to close.
Separately, this same period also surfaced a direct customer escalation: a re-sync on a ~60M-row table had been running for over 21 hours and had only processed a fraction of its rows, well outside what the customer considered acceptable given prior experience on another platform. It’s a distinct incident from the co-replica outage, but it added real urgency to the same underlying question. Both point at the same gap in recovery guarantees for anything past the smallest schemas.
Note on the target: the original ask coming out of the outage was recovery in under 6 hours for the largest (1B+ row) schema, to leave real margin inside the 7-day binlog window. The recovery target the team is actually building and testing against today is 48 hours for that tier (see the table below), a more realistic near-term goal that still comfortably beats the binlog expiry window, with 6 hours remaining the longer-term aspiration rather than the current deliverable.
Two Promising Ideas We Deliberately Walked Away From
Not every optimization survives contact with a real database, and knowing what not to build is just as valuable as knowing what to ship.
Exporting straight to S3 via SELECT INTO S3. The idea was elegant: skip row-by-row streaming entirely and have MySQL export directly to object storage for bulk loading. It was prototyped end-to-end, and turned out to be an Aurora-specific capability, not something that generalizes across the MySQL sources actually in use in the wild.
Exporting via SELECT INTO FILE. Same instinct, a different mechanism. This one requires the FILE privilege, which is a solvable constraint since it’s something that could be requested from a customer if needed. The harder wall is what happens after the export: the file is written directly to the database server’s own filesystem rather than returned to the client, and available client libraries have no way to retrieve that file back to the local shell task runner. In practice, that makes the approach unusable whenever the database is hosted on a public cloud provider.
Both were ruled out early and deliberately, which freed the team to double down on the opt-in BATCH message support covered in part one, a mechanism that works consistently, without special infrastructure or elevated privileges.
MySQL Backfill Optimization: Why Bottlenecks Move
One of the clearer lessons from working this across real customer pipelines: the bottleneck isn’t always in the same place, and treating it as one fixed problem leads you to the wrong fix.
On one pipeline, the target was single-threaded and CPU-bound, serializing oversized batches across a large number of tables at once. The fix there was table sharding combined with validate_records: false.
On another (a single very large table running through tap, mapper, and target in sequence), the CPU wasn’t the constraint at all. The target was spending most of its time waiting on the Snowflake COPY/PUT round trip because batch_size_rows was set too low (10,000), forcing constant small flushes roughly every 8-12 seconds. Raising batch_size_rows toward the 250,000-500,000 range cut the per-batch overhead dramatically.
On a third, the constraint wasn’t the tap, mapper, or target CPU at all: it was the network. A single connection through the SSH tunnel is capped at roughly 0.8 MB/s by the round-trip time and TCP window size, independent of how the query itself is batched. No amount of “fetch more rows per call” tuning moves that ceiling.
That last finding led to a workaround worth documenting in its own right, and one distinct from the parallel table runs covered in part one, which run whole tables side by side. This splits a single table’s backfill into several parallel pipelines by primary-key range, each populating its own temporary table, with a final assembly step that merges them into the target table and drops the temp tables. Applied to a 40-million-row table, this cut a backfill that would otherwise blow past 24 hours down to roughly 3 hours. It’s a stopgap rather than a permanent architecture (it doesn’t want to live inside a single pipeline long-term), but it’s actively being used today and deserves a place in the recovery playbook alongside BATCH messages.
The Follow-Through That’s Making It Production-Ready
A handful of concrete moves are turning the initial performance work into something teams can rely on day to day:
Tap and target, kept in lockstep. tap-mysql has moved beyond the v1.4.1 release covered in part one to v1.5.0, alongside target-snowflake v0.17.8-2 and a new mapper-fivetran v0.2.0, so every fix from part one ships together with target and mapper versions validated against it. The catalog’s plugin definition still needs a follow-up update to match the tap’s newest settings before this is fully rolled out.
BATCH support, being documented. A fast capability only helps if people know it exists and how to switch it on, so BATCH messaging is getting a dedicated documentation pass. The current draft still needs a formatting cleanup for clarity, and it will go out once the associated ticket is live rather than as a standalone release.
Setup instructions, being sharpened. The exact DDL queries and database privileges a MySQL source needs before a backfill can run cleanly are being spelled out as part of that same documentation pass, turning “should work” into “works the first time,” once published.
A dedicated performance-testing runner, in development. Built specifically to exercise tap-mysql under realistic load and surface the metrics that matter (CPU distribution across tap and target, throughput at every stage), so nobody has to hand-instrument every test run from scratch. This work is ongoing, with no blockers currently identified.
Putting a Number on Recovery
This is where engineering effort turns into an operational target rather than a vague promise. Instead of settling for “faster,” the team defined explicit recovery goals against real, production-sized schemas, and is now validating the pipeline against each one:
| Schema | Recovery Target |
| Smaller schemas (e.g. lion, eagle) | Under 24 hours |
| Largest schema (bigdb, ~3B rows) | Under 48 hours |
That 48-hour figure is the one that matters most set against a typical 7-day binlog retention window. It leaves comfortable headroom, turning a full resync into a routine recovery step rather than a race against an expiring clock, once validated end to end. The original ask following the outage was a 6-hour target for this tier; 48 hours is the milestone being validated now, with 6 hours as the direction to keep pushing toward.
The smallest schemas already resync in well under an hour, as part one showed. The medium and largest schemas are a different matter: part one’s improvements got individual runs moving in the right direction, but a good run isn’t the same as a validated recovery guarantee, and turning one into the other is the current focus of testing. The deliverable here isn’t a single benchmark chart. It’s a documented recovery process built around a known MTTR target, with progress against it now measurable schema by schema rather than assumed from one good run.
Arrow Batching: From Prototype to Shipped
The original framing here was forward-looking, “early-stage work,” a direction to build toward. Part of that has now shipped: Arrow/ADBC batch message support has landed on the tap and mapper sides. tap-mysql v1.5.0 natively emits Arrow/ADBC BATCH messages, and the new mapper-fivetran v0.2.0 reads, transforms, and re-emits Arrow IPC batches. Both live in the existing tap and mapper rather than a separate pipeline, preserving the log-based replication logic that already works instead of re-implementing it from scratch.
The target-snowflake side of this is not done yet. Arrow support there (column/table-name handling, the transition back to log-based sync) is still an open work item, not yet started. And these new tap/mapper versions still need to be bumped into the plugin catalog before they’re available to run in a customer workspace; that’s an explicit follow-up task, not yet complete. Same story for standing up the parallel per-schema pipelines needed to run the largest schema’s backfill concurrently, assigned, not yet built. Worth being precise about this: the fast path exists in the two connectors that needed it least (tap, mapper); the piece most exposed to the largest schemas is still ahead.
The throughput case driving this was real and measured directly against production-scale data: a from-scratch Arrow ADBC prototype hit throughput in the 15,000-20,000 rows/sec range in local end-to-end testing, and a full run against a 40-million-row table completed in roughly 52 minutes, a clear step up from the JSON-based batching path benchmarked in part one, where the same tap to mapper to target path was measured at somewhere between 1,800 and 6,600 rows/sec depending on configuration.
A standalone Arrow pipeline had one real gap: no path to log-based replication, meaning backfill and ongoing incremental sync would need two different connectors, a fragile split to operate long term. That gap is actively being closed rather than left as a permanent limitation: the tap now has the ability to record a binlog position to state even while running in full-table replication mode, and there’s a live proposal to give the tap a proper log-based replication mode that can also be used to drive a full refresh. So the “no CDC path” caveat should be framed as a closing gap, not a settled dealbreaker.
The mapper remains the piece to watch. Even with a faster tap and target, it’s emerged as the current bottleneck in mixed pipelines, and initial testing suggests batching the mapper alone won’t be enough on its own to hit the largest throughput targets. The Arrow-compatible mapper supports batch messages directly, and rather than silently reworking unsupported operations like flattening or Fivetran backward-compatible ID generation, it’s designed to error out on them explicitly, keeping the fast path fast and making unsupported cases visible instead of silently degraded.
Known Limitations Worth Naming
A more detailed architectural review of the backfill path surfaced a few correctness and reliability gaps that this post should flag as known limitations, rather than implying the recovery story is fully closed:
- Tables without an auto-incrementing primary key aren’t resumable. A crash partway through a backfill on one of these means starting over from scratch, with no fence to check against.
- Binlog filtering happens at the schema level, not the table level. On a busy source database, this means every write to every table in a replicated schema is received and evaluated, even for tables that aren’t being synced.
- GTID mode is off by default, despite being more resilient across replica failover than the current file-and-position approach.
- The checkpoint interval is a fixed row count (1,000 rows) rather than time-based, so recovery-window consistency varies with row size.
None of these block the recovery targets in the table above, but a reader evaluating this system against their own MTTR requirements would want to know where the edges are.
The Takeaway
Fast backfills are good. A backfill you can promise will finish in time is better. That’s the shift here: from “we made it quicker” to “we’ve defined exactly how long recovery should take, written it down, and we’re proving it schema by schema.” Arrow batching has now moved from a promising experiment to something built into the pipeline itself, the parallel PK-range workaround is filling the gap while that work matures, and the known resumability and filtering limitations are on the table rather than swept under it.
Running MySQL sources on Meltano Cloud and want your own backfill setup reviewed against this recovery baseline? Get in touch with the team here