KOEN
Read Original
#Backend

Beyond synthetic testing: Capturing and replaying real database workloads at Airbnb

Beyond synthetic testing: Capturing and replaying real database workloads at Airbnb
01

Summary

Beyond Synthetic Benchmarks: How Airbnb Replays Real Production Traffic to De-risk Database Upgrades

Scaling a custom capture and replay pipeline at millions of QPS with ProxySQL for bulletproof MySQL 8.0 migrations

This article shares how Airbnb built an offline query capture and replay system to replace unreliable synthetic benchmarks with actual production workloads. Operating at a scale of millions of QPS, this architecture helped Airbnb proactively catch hidden query incompatibilities and latency spikes before migrating their entire fleet to MySQL 8.0.

  • 01Centralized query logging at the ProxySQL proxy layer, eliminating client-side instrumentation and performance overhead
  • 02Reproduced original concurrency and transaction order using a task scheduler that coordinates worker start times across hundreds of instances
  • 03Caught a severe MySQL 8.0 sorting regression where query latency spiked from 0.03 to 2.6 seconds before it reached production
  • 04Used 'Replay and Compare' mode to identify subtle correctness issues like non-deterministic LIMIT queries lacking explicit unique ordering
  • 05Stress-tested critical clusters at 1.8x write load to locate latency ceilings and ensure reliability ahead of seasonal travel peaks

RECOMMENDATION

For backend and infrastructure engineering teams planning database migrations or upgrades, adopting a proxy-based traffic capture and replay system is highly recommended to safely discover real-world performance anomalies.

The Problem

Airbnb's legacy database query capture system was fragmented across multiple client-side language frameworks, introducing high maintenance overhead and lacking the transaction context required to accurately replay and validate results on new databases.

The Solution

The team built a client-agnostic capture and replay system leveraging ProxySQL to capture binary query logs, an offline Log Processor to parse and order transaction sequences, and a distributed Log Replayer to reproduce original traffic concurrency and pacing.

The Result

This system enabled a zero-incident upgrade of Airbnb's entire MySQL fleet from 5.7 to 8.0, discovering critical performance regressions (such as a latency increase from 0.03s to 2.6s caused by the query cache removal) and validating capacity limits via accelerated playback up to 2x load.

Trade-off

In order to preserve transactional consistency during replays, the Log Processor rewrites INSERT queries to explicitly include the captured last_insert_id, which means they accept the trade-off of not testing the target database's native auto-increment generation mechanism.

03

Key Concepts

Concept · 01

ProxySQL

A high-performance, MySQL-protocol-aware open-source proxy that routes and manages database traffic between client applications and backends.

  • Served as the single, client-agnostic capture point for logging database queries at Airbnb without making any application-level code modifications.
Concept · 02

Log Processor

An offline data processing pipeline that parses binary query logs, groups queries by database cluster, and arranges transaction statements chronologically.

  • Responsible for query rewriting, such as pinning the last_insert_id on INSERT statements to ensure determinism across different database versions.
Concept · 03

Log Replayer

A distributed, horizontally scalable replay framework consisting of an API server, a task scheduler, and worker nodes that replay traffic against target databases.

  • Supports two main operations: 'Replay only' for capacity and load testing, and 'Replay and compare' for schema/version compatibility testing.