Database Testing Without Synthetic Data: How Airbnb Captures and Replays Real Production TrafficArticle · digest for November 15
Cinematic still life of a database engineer's workspace showing query latency charts and traffic replay analysis
Article3 sections≈ 7 min

Database Testing Without Synthetic Data: How Airbnb Captures and Replays Real Production Traffic

The morning before a scheduled database upgrade starts calmly enough: you have a tab open with staging charts, and a teammate pings Slack asking if you're ready to cut over at lunch. Everything on the monitor looks idyllic—the synthetic load test hammered away all night without a single error, response times hugged the floor, and CPUs sat at half capacity. Yet the moment you route real users onto the upgraded cluster, latency shoots through the roof within a minute, the connection pool clogs up, and live orders start timing out.

I've always been skeptical of lab benchmarks before major relational database migrations. How do you test a new DBMS version or a schema change against a real stream of customer transactions without putting live data or user wallets at risk? In the Airbnb Engineering blog, I found a breakdown of how their database infrastructure team moved beyond artificial tests and built a pipeline to capture and replay real production traffic offline for MySQL and Amazon Aurora clusters.

Why Synthetic Benchmarks Miss Real Production Outages

Let's trace what actually happens inside a database under load. When you run a synthetic query generator, you're testing the system in greenhouse conditions: the script picks random rows uniformly, like sending identical sedans across a new bridge five seconds apart. But when three overloaded dump trucks roll onto that bridge at rush hour and a hundred taxis merge in from a side ramp, your theoretical throughput collapses.

In real life, queries are never evenly distributed—you get data skew. When thousands of travelers open a popular city at the peak of the season, millions of queries per second (QPS) hammer the exact same hot rows and indexes. When multiple heavy transactions compete for those same records, row lock contention and deadlocks flare up—things a uniform test simply never triggers.

The risk gets even sharper during major engine upgrades, such as migrating from MySQL 5.7 to MySQL 8.0. If the query optimizer's logic changed in the new version, the planner on a complex live table might suddenly pick a different index or reorder a JOIN. Because a synthetic script only exercises pre-written templates, rare and heavy filter combinations sit in a blind spot right up until release day.

You're probably thinking: "Why not just have the backend services log every SQL query?" At Airbnb's scale, that idea crashes into infrastructure reality. Hundreds of MySQL clusters serve a sprawling fleet of microservices written in Java, Python, and Ruby. Forcing teams to maintain custom logging libraries across three languages would bury engineering in busywork, and writing logs inside the app or the database engine itself would tack extra milliseconds of latency onto every customer request.

ProxySQL Capture Architecture, Data Masking, and the Insert-ID Trick

Since touching microservice application code or loading down the database core was off the table, Airbnb's engineers decided to intercept traffic at the network layer. In their architecture, every call from applications to MySQL clusters already passes through a unified proxy layer—ProxySQL. By enabling event logging on the proxy, Java, Python, and Ruby services stay completely unaware of the capture, and database servers don't burn extra CPU cycles profiling queries.

The traffic capture and preparation pipeline runs in four steps:

  1. Capture at the proxy. To record a cluster's workload profile, ProxySQL enables event logging via the mysql-eventslog_filename parameter, capturing the stream of SQL calls without touching business logic.
  2. Chunking and shipping via Log Mover. To avoid filling up local proxy disks, a background sidecar process called Log Mover runs alongside it. It reads the query logs, packs them into isolated five-minute files per cluster, and ships them asynchronously to cloud storage (S3 and HDFS). We looked at a similar pattern in our piece on why Airbnb kept sidecar-based config delivery for its polyglot service fleet.
  3. PII masking and transaction assembly. Before storage, a sanitization script strips personal information (PII masking) from query parameters so sensitive customer data never lands in staging. At the same time, the pipeline groups scattered calls back into transaction chains, preserving their original commit order.
  4. Rewriting insert IDs. Here's where a subtle engineering trap pops up. In production, two parallel threads ran INSERT almost simultaneously, and the database assigned ID 1001 to the first order and 1002 to the second. During replay on a staging cluster, the second thread might hit the disk a split second earlier and grab 1001 for itself. If a subsequent UPDATE or SELECT looks up related records by the original ID, the whole transaction chain falls apart with foreign-key errors.

To prevent that desynchronization, Airbnb records the actual last_insert_id returned by the production database on write. When the replay runner reads an insert command, the --rewrite-last-insert-id=true flag dynamically rewrites the INSERT query to force the historical identifier onto the new row. That way, every follow-up read and update finds its exact entity.

Rehearsing the MySQL 8.0 Migration, Capacity Planning, and Engineering Trade-Offs

Once five-minute chunks of scrubbed logs are ready, testing begins. Engineers spin up a snapshot of the production database taken at the exact start of log capture (T0) on an isolated test cluster running the new DBMS version with no external network access. Then replay-runner connects to the target (--target=staging-db:3306), pulls logs from --log-source=s3://db-logs/cluster-01/, and replays traffic in real time (--speed=1.0), accelerated (2x), or at stress QPS, comparing P95/P99 latency, CPU load, and error rates against the baseline.

The payoff became unmistakable during Airbnb's migration from MySQL 5.7 to MySQL 8.0. While replaying live traffic on 8.0, the pipeline caught an anomaly synthetic tests had missed completely: a production query that consistently ran in 0.03 seconds on MySQL 5.7 suddenly took 2.6 seconds on the new version. Checking the execution plan showed that differences in sorting algorithms and non-deterministic row ordering caused the MySQL 8.0 optimizer to handle that query inefficiently. Catching a jump to 2.6 seconds in staging weeks before release let engineers fix the query in advance and avert an outage.

A second win is capacity planning. The team replayed a real workload profile while bumping the share of write queries by 80% to spot disk ceilings early and measure cluster headroom. For more on how a single heavy query can topple storage nodes even when average QPS looks normal, see our breakdown of how DBMS protection against cascading overloads evolved in high-load services.

Of course, this level of accuracy carries an engineering price tag, and it's worth weighing the method's limits soberly:

  • Strict snapshot alignment. If the test database dump drifts from the start of log capture by even a couple of minutes, update queries start hitting rows that haven't been created yet or have already changed.
  • Auto-increment trade-off. Forcing last_insert_id skips testing the new DBMS version's native primary-key generator under concurrent write contention.
  • Storage and sanitization overhead. Shipping five-minute logs to cloud storage demands infrastructure resources and rock-solid masking filters so sensitive data never leaks into test environments.

Looking at Airbnb's setup with hundreds of clusters and sidecars, it's easy to assume you can't touch this approach without a huge infrastructure team. You don't need to build a spaceship or get everything perfect on day one. Even if before your next database upgrade you just capture five minutes of real read queries through a proxy, scrub personal parameters, and replay them against a backup restore—you'll take off the blindfold, see real execution plans on live tables, and catch optimizer surprises before your users do.

Want a deep diveVotes help us choose what to break down in detail. This material has 0 votes.
Sources · 2