Synthetic benchmarks often fail to capture production nuances. This system allows engineers to validate database upgrades and capacity plans using actual traffic patterns, significantly reducing the risk of regressions and performance issues during migrations.
By: Zuofei Wang, Erluo Li
At Airbnb, MySQL-compatible databases are a critical backbone of our online database infrastructure: a fleet of hundreds of clusters supporting thousands of use cases at millions of queries per second (QPS). Operating databases at scale brings hard problems, including sizing clusters for future growth, keeping behavior consistent across version upgrades and migrations, and reproducing production incidents well enough to debug them. This post describes the database traffic capture and replay system we built to help us tackle them.
A complex database operation such as Airbnb’s requires a number of supporting systems, and one of them is a way to capture database queries and replay them. This is needed for error recovery, governance, quality assurance testing and product improvement efforts.
Previously, Airbnb’s system for database query capture and replay was fragmented. Each language binding used its own logging framework to emit query traces to Kafka, which were then processed per use case and replayed by a custom replayer.
This system was borrowed from our observability pipeline. It worked as a fast initial solution, but over time, we found that it fell short of what we needed. Capturing queries client-side and per language carried a heavy maintenance burden whose ownership was ambiguous, and it scaled poorly across our service-oriented architecture (SOA). Most importantly, it lacked enough information to convey a complete picture of each transaction. As a result, it couldn’t accurately replay transactions, which made it impossible to verify that the same queries return the same results on the new database.
To close these gaps, we set out to build a system for query capture and replay, with three goals:
To meet these goals, we needed a single, client-agnostic capture point that needs no application changes. We built it on top of ProxySQL, an open-source proxy that understands the MySQL wire protocol and already sits between our applications and databases.
All database traffic already flows through a ProxySQL deployment, which makes it a natural place to capture queries. The query capture and replay system has three components, all built in-house: a Log Mover that collects the query logs, a Log Processor that turns them into replayable, per-cluster datasets, and a Log Replayer that runs captured traffic against target databases.
Because captured queries can contain personal data, the pipeline treats them exactly as we treat production data. Logs are encrypted in transit and at rest, access follows the principle of least privilege and is limited to the database infrastructure team, and query logging is enabled for a given cluster only for the window a test requires. Replays run only in production-equivalent environments held to the same standards. Captured traffic is never replayed against lower environments or copied into a separate development tier.
ProxySQL has a built-in query logging feature controlled by its query rules, so we can turn logging on for one cluster’s traffic without touching anything else. When enabled, it writes query logs to the local disk, with little impact on query performance.
We complement ProxySQL with a Log Mover sidecar that monitors these log files and transfers them to cloud object storage, thus keeping local disk usage in check.
Our ProxySQL deployment runs on Kubernetes as a pool of stateless pods, each serving clients for several backend clusters. Therefore, a single log file can contain queries from many backend database clusters. An offline post-processing pipeline partitions these original query logs into a replayable dataset for each cluster.
The Log Processor job performs a few transformations:
The original query logs are in a binary format and intermingled with queries from multiple backend database clusters, so we decode them and write the queries for each cluster into its own file. Because the logs also interleave statements from concurrent connections, we reassemble each transaction’s statements in their original order so that replay reproduces the original behavior.
Each processed file is bucketed by query timestamp, holding a five-minute window of queries from one database cluster and ProxySQL pod. This allows the replayer to control the replay pace, throughput, and overall load at a fine granularity.
Metadata, such as timestamp, username, database cluster and schema name, is stored alongside each processed log file, so users can locate specific query logs by timestamp and database cluster.
The Log Processor also rewrites queries to keep auto-increment behavior consistent across databases. While MySQL’s default auto-increment values are monotonically increasing, MySQL 8.0 allows for customization to enhance performance, and certain MySQL-compatible databases may assign auto-increment values differently. As a result, an INSERT statement can produce a different last_insert_id than it did in production. A later read that depends on that value, such as a lookup of the related rows, would then fail or return nothing, skewing the test results.
To avoid this, the Log Processor rewrites each INSERT statement to explicitly include the last_insert_id captured in the logs. For example, an original MySQL statement INSERT INTO users (name) VALUES (‘bob’) that generates last_insert_id=1 will be rewritten to INSERT INTO users (id, name) VALUES (1, ‘bob’). This rewriting ensures that subsequent queries depending on last_insert_id values behave consistently across different database systems during replay tests, reducing false positives in compatibility testing. We accept the tradeoff here: by pinning last_insert_id, we no longer exercise the target’s native auto-increment generation during replay.
The Log Replayer takes processed query logs and replays them against target databases. Users create replay jobs through a web UI with the following parameters:
There are three components in the log replayer: an API Server (the control plane), a Replay Task Scheduler, and a fleet of Replay Task Workers.
The API Server is the control plane of the log replayer. Through a web UI, users can submit and control replay jobs, monitor job status, and view metrics and replay results. Job and task states are kept in a persistent database.
Users can choose one of two replay modes:
The task scheduler breaks a job into batches of tasks, one task per log file. For each task, the scheduler pushes a message onto a managed message queue for replay task workers to consume.
To preserve the original timing, the task scheduler assigns every task in a batch the same “expected start time”, which is a future moment when that batch should begin. Because workers pick up tasks from the queue at different times, this shared start signal keeps them in step: each worker waits for it, then they all begin replaying their five-minute files together. This reproduces the concurrency and pacing of the original workload instead of replaying each file in isolation.
The task scheduler also monitors progress: if tasks start failing, it stops scheduling new ones to avoid cascading issues, and otherwise advances to the next batch.
Workers scale horizontally, up to thousands of instances. Each polls the message queue for tasks, reads and parses its assigned query log file from cloud object storage, waits for the task’s “expected start time,” and then executes the logged queries against target databases in order. By spacing consecutive queries according to their original timestamps and the chosen speed factor, workers reproduce (or accelerate) the original traffic pattern while preserving relative timing.
This framework enables multiple business-critical capabilities for our online workloads and the teams operating them.
For a database upgrade or migration, ensuring the new database has a compatible spec isn’t enough. We also have to preserve the performance baseline and backwards-compatible behavior. This became particularly apparent during our MySQL 5.7 to 8.0 upgrade, and replay testing was critical in identifying issues early and avoiding surprises in production.
Performance. MySQL 8.0’s performance profile differs from 5.7’s. While most changes were positive, some workloads regressed, for example more conservative metadata locking.
Pinning down the cause sometimes took more than one replay run. MySQL 8.0 removes the query cache entirely, so we first replayed against 5.7 with and without the query cache to isolate that effect, then compared 5.7-without-cache against 8.0. This bisection surfaced a pattern of duplicate, cacheable queries the application was sending, which the cache had quietly absorbed.
In the sharpest case, the latency for one query pattern went from 0.03 to 2.6 seconds, reading 273 MB per join instead of 5 MB, which we traced to an upstream change in how MySQL 8.0.20+ reads rows while sorting.
Correctness. Some default behaviors in MySQL 8.0 also changed in ways that can break applications, such as the default innodb_autoinc_lock_mode, which hands out interleaved, non-sequential auto-increment IDs that some applications assumed were sequential. To verify behavior stayed consistent, we ran “Replay and compare” against 5.7 and 8.0 restored from the same snapshot and diff’ed the results. This surfaced a common pattern: queries whose row order was never fully determined, either with no ORDER BY or an ORDER BY without a unique tiebreaker, which quietly return different rows on a new engine once a LIMIT is applied. By catching this proactively, we were able to work with the owning teams to add explicit ordering where it was implicitly expected by the application.
To plan for growth, usually ahead of peak travel season, we replay a production cluster’s traffic in “Replay only” mode at higher speeds (for example, 2x) to model future load. This shows whether the current setup can absorb a seasonal peak or launch, or whether we need to scale up or out. On one large cluster, replaying 80% more write traffic pushed average commit latency up by almost 500% from about 6 ms to 34 ms, locating its ceiling well before real traffic did.
Teams at Airbnb can now request these traffic replays through a self-serve tool to help them prepare for expected growth, or identify current headroom on clusters which may be opportunities for more efficient bin-packing and cost optimization.
Synthetic benchmarks tell you how a database handles the workload you imagined. Replaying real traffic tells you how it handles the workload you actually have. That difference carried our MySQL fleet from 5.7 to 8.0 without a major production incident: we used replay to clear the highest-risk clusters first, catching latency regressions and non-deterministic queries offline instead of in production.
If this type of work interests you, check out some of our related positions!
Thanks to Ping Wang, Eugene Yedvabny, Ari Ekmekji, and Sam Lightstone for their support and feedback throughout this project.
We also want to thank Zheng Liu for their support in authoring this post during their time at Airbnb.
All product names, logos, and brands are property of their respective owners. All company, product and service names used in this website are for identification purposes only. Use of these names, logos, and brands does not imply endorsement.
Beyond synthetic testing: Capturing and replaying real database workloads at Airbnb was originally published in The Airbnb Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.
Continue reading on the original blog to support the author
Read full articleTraditional testing fails with non-deterministic LLMs. Eval-driven development provides a systematic framework to ensure GenAI reliability, preventing regressions and aligning model outputs with product goals through structured, multi-layered validation.
Viaduct offers a middle ground between monolithic GraphQL and complex Federation by allowing teams to contribute to a shared schema via modules. This reduces operational overhead while maintaining developer autonomy, making it easier to scale data access across large organizations.
Scaling observability for 1,000+ services requires balancing multi-tenant isolation with operational efficiency. Airbnb's approach to shuffle sharding and automated control planes provides a blueprint for building resilient, petabyte-scale metrics systems that avoid 'flying blind' during outages.
This article details how a large-scale key-value store was rearchitected to meet modern demands for real-time data, scalability, and operational efficiency. It offers valuable insights into addressing common distributed system challenges and executing complex migrations.
