How we capture real production database traffic at Airbnb and replay it offline to load-test, plan capacity, and de-risk upgrades.
By: Zuofei Wang, Erluo Li
Introduction
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.
Challenges and motivation
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:
- Load testing with real production traffic. Synthetic benchmarks such as sysbench don’t capture the full spectrum of production queries. By replaying captured traffic at production rates, or at higher multiples to model forecast growth, we can right-size clusters: upscaling those with insufficient headroom for growth and downscaling over-provisioned ones.
- Query compatibility across upgrades and migrations. Subtle behavior differences (such as differences between MySQL 5.7 and 8.0, or between “official” MySQL and a MySQL-compatible engine) can break applications. By replaying the same traffic against two targets and comparing results, we surface incompatibilities before production migrations.
- Performance debugging. MySQL provides performance_schema digests for diagnostics, but these are abstracts of actual queries and don’t contain enough information to reproduce a problem. Capturing complete SQL statements with their transactional context lets us replay problematic workloads offline, both to investigate incidents and to validate fixes before they ship.
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.
System architecture overview
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.
Log Mover
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.
Log Processor
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:
Parsing, grouping, and ordering
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.
Timestamp-based bucketing
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
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.
Query rewriting
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.
Log Replayer
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:
- Source database cluster: the name of the database cluster whose traffic to replay
- Time range: the temporal scope of captured query logs to replay
- Target database endpoints: the destination databases for replay traffic
- Replay mode: which of the two modes to use (described below).
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.
API Server
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:
- Replay only (load testing): Queries are replayed to a single target database at a configurable speed, set either as a factor of the original traffic (1x, 2x, 3x) or as a target QPS. While query results are ignored, we track query errors, latency histograms per query digest, and resource utilization. This supports our load testing and capacity planning, and lays the groundwork for automated regression detection.
- Replay and compare (compatibility testing): The same queries are run against two target databases at once, and their results are compared with any discrepancies logged. This is used to validate database upgrades and migrations.
Replay Task Scheduler
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.
Replay Task Worker
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.
Impact
This framework enables multiple business-critical capabilities for our online workloads and the teams operating them.
Upgrade and migration testing
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.
Capacity planning
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.
Conclusion
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!
Acknowledgments
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.