Database Workload Capture and Replay System for Scalability and Compatibility
Airbnb developed a robust system to capture and replay real production database workloads. This system addresses critical challenges like capacity planning, de-risking database upgrades, and performance debugging by simulating production traffic in test environments. By leveraging ProxySQL as a client-agnostic capture point, the architecture ensures accurate transaction replay and result comparison across different database versions or configurations.
Read original on Airbnb EngineeringOperating databases at scale, particularly hundreds of MySQL-compatible clusters processing millions of queries per second, presents significant challenges. These include accurately sizing clusters for future growth, ensuring consistent behavior during version upgrades and migrations, and effectively reproducing production incidents for debugging. Traditional synthetic benchmarks often fall short in mirroring real-world traffic patterns, making capacity planning and compatibility testing difficult.
Motivation for a New System
Airbnb's prior fragmented system, relying on client-side query logging, suffered from high maintenance burden, poor scalability in an SOA environment, and most critically, lacked sufficient transactional context for accurate replay. This necessitated a new, unified system with three primary goals:
- Load Testing: Accurately gauge capacity and identify bottlenecks by replaying real traffic at production rates or higher multiples.
- Query Compatibility: Identify subtle behavioral differences and breaking changes between database versions (e.g., MySQL 5.7 vs. 8.0) by comparing results of identical workloads.
- Performance Debugging: Capture complete SQL statements with transactional context to reproduce and validate fixes for performance issues.
System Architecture Overview
The core of the system leverages ProxySQL, an open-source MySQL proxy already in their infrastructure, as a transparent, client-agnostic query capture point. This avoids application code changes. The system comprises three main components:
- Log Mover: A sidecar to ProxySQL that monitors and transfers query logs from local disk to cloud object storage.
- Log Processor: An offline job that parses, groups, orders, and buckets query logs by timestamp and database cluster. It also performs crucial query rewriting for auto-increment IDs to ensure consistent replay behavior.
- Log Replayer: A distributed service consisting of an API Server (control plane), Replay Task Scheduler, and Replay Task Workers that execute captured workloads against target databases.
Key Design Decision: Query Rewriting
To handle auto-increment ID inconsistencies across different MySQL versions or compatible databases, the Log Processor rewrites `INSERT` statements to explicitly include the `last_insert_id` captured from production. For example, `INSERT INTO users (name) VALUES ('bob')` becomes `INSERT INTO users (id, name) VALUES (1, 'bob')`. While this sacrifices testing native auto-increment generation, it ensures subsequent queries relying on these IDs behave consistently during replay, significantly reducing false positives in compatibility tests.
Replay Modes
- Replay Only (Load Testing): Queries are replayed to a single target database at a configurable speed (e.g., 1x, 2x, 3x, or target QPS). Results are ignored, but errors, latency, and resource utilization are tracked for capacity planning and performance baselining.
- Replay and Compare (Compatibility Testing): The same queries are run against two target databases simultaneously, and their results are compared. Any discrepancies are logged, crucial for validating database upgrades and migrations like the MySQL 5.7 to 8.0 transition.
Concurrency and Pacing
The Replay Task Scheduler plays a vital role in preserving original workload concurrency. It assigns an "expected start time" to batches of tasks. Workers wait for this shared signal before replaying their respective five-minute log files simultaneously, accurately reproducing the original workload's pacing and concurrent execution patterns, rather than replaying files in isolation.