Airbnb Engineers Build ProxySQL-Based Shadow Traffic Pipeline for Zero-Risk Database Upgrades
Capturing live production queries enables accurate capacity planning and seamless major version migrations.
1Production Scale Operational Bottlenecks
Airbnb manages hundreds of MySQL-compatible database clusters serving millions of queries per second across thousands of use cases. Operating at this scale creates major risks during version upgrades, capacity planning, and incident reproduction. Synthetic benchmarks like sysbench consistently fail to replicate the nuanced complexity of real production traffic patterns.
2Shortcomings of Legacy Client Logging
Airbnb previously relied on language-specific client logging libraries that pushed raw query traces to Kafka. This fragmented setup imposed heavy maintenance burdens across their service-oriented architecture. Crucially, client logging lacked complete transactional context, making it impossible to reconstruct transaction boundaries or verify deterministic query results on target test databases.
3Unified ProxySQL Capture Architecture
To eliminate application-side changes, engineers centralized traffic capture inside their existing ProxySQL proxy layer. The newly designed system consists of three distinct in-house components: Log Mover, Log Processor, and Log Replayer. Capturing queries at the proxy tier provides client-agnostic collection while fully preserving wire-protocol fidelity across backend clusters.
4Sidecar Log Mover Mechanics
ProxySQL pods run statelessly on Kubernetes, handling queries across multiple backend clusters simultaneously. Engineers enabled native ProxySQL query rules to write binary logs locally with minimal latency overhead. An adjacent Log Mover sidecar process continuously monitors local disk storage, streaming log segments into cloud object storage to avoid disk exhaustion.
5Offline Processing and Transaction Reassembly
The offline Log Processor parses raw binary logs, demultiplexing intermingled traffic into individual cluster datasets. It reassembles interleaved statements from concurrent connections back into strict transactional sequences. Processed queries are then partitioned into discrete five-minute timestamped buckets alongside metadata like usernames, schema names, and exact execution intervals.
6Auto-Increment Query Rewriting Strategy
Different database engines generate auto-increment identifiers differently, which breaks dependent subsequent read queries during replays. The Log Processor actively rewrites INSERT statements to explicitly hardcode the original last_insert_id value captured in production. This intentional tradeoff eliminates false positives during compatibility testing, ensuring downstream relational queries resolve identical row references.
7Dual Mode Log Replayer
The Log Replayer features a centralized API Server controlling worker execution in two modes: Replay Only and Replay and Compare. Replay Only stress-tests capacity at accelerated throughput multipliers while ignoring returned data. Replay and Compare executes identical queries against dual targets, diffing returned result sets to surface subtle engine-level incompatibilities.
8Distributed Replay Task Coordination
A centralized scheduler splits replay jobs into individual log-file tasks dispatched over a message queue. To preserve production concurrency, the scheduler assigns an identical future start timestamp across task batches. Thousands of distributed workers sync against this shared time barrier, replaying their allocated five-minute files simultaneously rather than in isolation.
9Uncovering MySQL 8 Regressions
During Airbnb's MySQL 5.7 to 8.0 migration, replay bisection exposed massive performance regressions. Removing the query cache surfaced high-volume duplicate application queries. More critically, an upstream sorting change in MySQL 8.0.20+ caused a specific query's latency to spike from 0.03 to 2.6 seconds by reading 273 MB instead of 5 MB.
10Detecting Non-Deterministic Query Behaviors
Replay and Compare testing pinpointed queries lacking strict ORDER BY clauses or deterministic tiebreakers, which silently returned different rows under MySQL 8.0 when paired with LIMIT. The pipeline also highlighted differences in innodb_autoinc_lock_mode, allowing platform engineers to resolve ordering dependencies directly with product teams prior to live cutovers.
11Proactive Capacity and Cost Optimization
Replaying production traffic at accelerated multipliers allows Airbnb to accurately pinpoint hardware bottlenecks before peak travel seasons. Stressing one large cluster with 80% more write traffic revealed a 500% commit latency surge from 6 ms to 34 ms. This self-serve framework now governs reliable bin-packing and infrastructure rightsizing.