Step-by-Step Study Material

Step 1: The Problem — Why Synthetic Testing Falls Short

What is Synthetic Testing?

Synthetic testing means creating artificial workloads to simulate database usage.

Example of Synthetic Test:
"Run 1000 SELECT queries per second against this table"

Why It's Insufficient

Real Problems Requiring Real Workloads

Airbnb needed to solve three categories of problems:

1. SIZING    → Can this cluster handle 2x traffic next peak season?
2. UPGRADES  → Will MySQL 8.0 behave the same as 5.7?
3. INCIDENTS → Can we reproduce this production bug to debug it?

Key Insight: You cannot answer these questions confidently with made-up traffic

---

Step 2: Why the Old System Failed

Original Architecture (Fragmented)

Application (Python) → Python Logger  ↘
Application (Java)   → Java Logger   → Kafka → Custom Replayer
Application (Ruby)   → Ruby Logger   ↗

Problems With This Approach

Problem 1: Maintenance Burden

• Every programming language needed its own logging code
• Ownership was unclear — who fixes it when it breaks?

Problem 2: Poor Scalability

• Adding new services meant adding new logging integrations
• Did not scale well across a service-oriented architecture (SOA)

Problem 3: Incomplete Transaction Data

Transaction Example:
  BEGIN
  INSERT INTO orders (user_id) VALUES (42)   ← captured
  SELECT * FROM orders WHERE id = ?          ← depends on INSERT result
  COMMIT

Problem: Without knowing the INSERT's result (last_insert_id),
         the SELECT cannot be replayed correctly

Key Concept: A transaction is a group of related queries that must be replayed together and in order to produce correct results

---

Step 3: The New Architecture — Three Core Components

Design Principle: Single Capture Point

Instead of capturing at the application layer, capture at the database proxy layer

Before (Client-Side Capture):
[App 1] --logs--> [Logger]
[App 2] --logs--> [Logger]  → Fragmented, incomplete
[App 3] --logs--> [Logger]

After (Proxy-Side Capture):
[App 1] ↘
[App 2] → [ProxySQL] --logs--> [Unified Pipeline]  ← Single point
[App 3] ↗

ProxySQL as the Capture Point

What is ProxySQL?

• An open-source proxy that sits between applications and databases
• Understands the MySQL wire protocol (the language MySQL speaks)
• Already deployed at Airbnb — no new infrastructure needed

Traffic Flow:
[Applications] → [ProxySQL] → [MySQL Database]
                     ↓
               [Query Logs written to local disk]

Why this is elegant:

• Zero application changes required
• Captures ALL traffic regardless of programming language
• Can be toggled on/off per cluster without affecting others

---

Step 4: Component 1 — The Log Mover

Purpose

Move logs from ProxySQL's local disk to cloud storage

The Problem It Solves

ProxySQL runs as stateless pods in Kubernetes
Each pod serves multiple backend clusters
Local disk space is limited

Without Log Mover:
[ProxySQL Pod] → disk fills up → system fails

With Log Mover (sidecar):
[ProxySQL Pod] → writes logs → [Log Mover] → [Cloud Storage]
                                    ↑
                              Runs alongside ProxySQL
                              (sidecar pattern)

Key Characteristics

• Runs as a sidecar (companion process alongside ProxySQL)
• Monitors log files continuously
• Transfers to cloud object storage automatically
• Keeps local disk usage under control

---

Step 5: Component 2 — The Log Processor

Purpose

Transform raw, mixed logs into clean, replayable datasets per cluster

Three Key Transformations

Transformation 1: Decode and Separate

Raw Log (binary, mixed):
[Cluster-A query][Cluster-B query][Cluster-A query][Cluster-C query]...

After Processing:
cluster-A.log → [query][query][query]...
cluster-B.log → [query][query]...
cluster-C.log → [query][query]...

Transformation 2: Reassemble Transactions

Raw interleaved connections:
Connection-1: BEGIN
Connection-2: BEGIN
Connection-1: INSERT INTO orders...
Connection-2: SELECT * FROM users...
Connection-1: COMMIT
Connection-2: COMMIT

After reassembly:
Transaction-1: [BEGIN, INSERT INTO orders, COMMIT]
Transaction-2: [BEGIN, SELECT * FROM users, COMMIT]

Why this matters: Replaying queries out of order produces wrong results

Transformation 3: Fix Auto-Increment IDs

This is the most technically subtle transformation. Understand it carefully:

The Problem:
Production MySQL:
  INSERT INTO users (name) VALUES ('bob')
  → MySQL assigns id=1 (last_insert_id=1)
  → Next query: SELECT * FROM orders WHERE user_id=1  ✓ Works

Target MySQL (different version):
  INSERT INTO users (name) VALUES ('bob')
  → MySQL assigns id=5 (last_insert_id=5)  ← Different!
  → Next query: SELECT * FROM orders WHERE user_id=1  ✗ Wrong ID!

The Fix (Log Processor rewrites the INSERT):
Original:  INSERT INTO users (name) VALUES ('bob')
Rewritten: INSERT INTO users (id, name) VALUES (1, 'bob')
           ↑ Explicitly pins the ID from production logs

The Tradeoff:

Benefit: Subsequent queries work correctly → accurate test results
Cost:    We no longer test the target's own auto-increment behavior

Output Format

Processed files bucketed by:
- 5-minute time windows
- Per cluster
- Per ProxySQL pod

File metadata includes:
- Timestamp
- Username
- Cluster name
- Schema name

---

Step 6: Component 3 — The Log Replayer

Architecture Overview

[Web UI]
   ↓
[API Server] ← control plane, stores job state
   ↓
[Task Scheduler] ← breaks jobs into tasks, manages batches
   ↓
[Message Queue]
   ↓
[Worker 1][Worker 2]...[Worker N] ← scale to thousands
   ↓
[Target Database]

Two Replay Modes

Mode 1: Replay Only

Use case: Performance testing, capacity planning
Action:   Execute queries, measure performance
Output:   Latency metrics, throughput numbers

Example: "Replay at 2x speed to simulate peak season traffic"

Mode 2: Replay and Compare

Use case: Correctness testing, version upgrades
Action:   Execute queries on TWO databases simultaneously
Output:   Diff of results between databases

Example: "Run same queries on MySQL 5.7 AND 8.0, compare results"

How Workers Preserve Original Timing

This is a critical design detail:

Original Production Traffic:
T=0:00  Query-A executes
T=0:01  Query-B executes
T=0:03  Query-C executes
T=0:05  Query-D executes

Naive Replay (WRONG):
Worker picks up file → immediately runs all queries back-to-back
→ Loses original concurrency and pacing
→ Results are not representative

Correct Replay:
All workers receive same "expected start time" (e.g., T=future+0)
Worker-1 waits → starts at T → replays its file with original spacing
Worker-2 waits → starts at T → replays its file with original spacing
→ Concurrency and pacing are preserved

Speed Factor:

Speed Factor 1x → replay at original speed
Speed Factor 2x → compress timestamps by half (simulate 2x traffic)
Speed Factor 0.5x → slow down (easier debugging)

---

Step 7: Real-World Applications

Application 1: MySQL 5.7 → 8.0 Upgrade

Performance Discovery:

Replay finding:
Query pattern X:
  MySQL 5.7: 0.03 seconds, reads 5 MB per join
  MySQL 8.0: 2.6  seconds, reads 273 MB per join
             ↑ 86x slower!

Root cause: Change in how MySQL 8.0.20+ reads rows during sorting

Bisection Method Used:

Step 1: Replay on 5.7 WITH query cache
Step 2: Replay on 5.7 WITHOUT query cache  ← isolate cache effect
Step 3: Replay on 8.0 (no query cache)
Step 4: Compare Step 2 vs Step 3           ← isolate version effect

Correctness Discovery:

Problem found: Queries with no ORDER BY or incomplete ORDER BY
               return different rows when LIMIT is applied

Example:
  SELECT * FROM listings LIMIT 10
  → 5.7 returns rows [1,2,3,4,5,6,7,8,9,10]
  → 8.0 returns rows [1,3,2,5,4,7,6,9,8,10]  ← different order!

Fix: Add explicit ORDER BY with unique tiebreaker
  SELECT * FROM listings ORDER BY id LIMIT 10

Application 2: Capacity Planning

Goal: Will our cluster survive peak travel season?

Method:
1. Capture current production traffic
2. Replay at 1.8x speed (simulate 80% more writes)
3. Measure performance degradation

Finding on one large cluster:
  Normal traffic:    ~6ms  average commit latency
  At 1.8x traffic:  ~34ms average commit latency
                     ↑ 467% increase → cluster ceiling found!

Action: Scale up BEFORE peak season, not during

---

Step 8: Security and Privacy Considerations

Since captured queries may contain personal data:

Security Measures:
├── Encryption in transit (data moving between systems)
├── Encryption at rest (data stored in cloud)
├── Principle of least privilege (only DB infra team has access)
├── Logging enabled only during test windows (not always-on)
└── Replay only in production-equivalent environments
    (never in lower/dev environments)

Key Principle: Treat captured query logs with the same care as production data

---

Summary: The Complete Picture

CAPTURE LAYER
[Applications] → [ProxySQL] → [Local Disk]
                                    ↓
MOVEMENT LAYER                [Log Mover] → [Cloud Storage]
                                                  ↓
PROCESSING LAYER                         [Log Processor]
                                         - Decode binary logs
                                         - Separate by cluster
                                         - Reassemble transactions
                                         - Fix auto-increment IDs
                                                  ↓
REPLAY LAYER                             [Log Replayer]
                                         - API Server
                                         - Task Scheduler
                                         - Worker Fleet
                                                  ↓
                                         [Target Database]

Core Lessons