Gunjan Sharma

System Design

Why MySQL In-Memory Reads Are 50x Slower Than Redis (Even With 0% Disk I/O)

· Updated
ySQLRedisDatabaseInternalsSystemDesignPerformanceOptimizationSoftwareEngineeringBackendArchitectureInnoDBLatency

The Whiteboard Dilemma

Last week, during a systems design interview for a SDE-2 Infrastructure Engineer, I asked a deceptively simple question:

"Suppose you assign 128 GB of RAM to MySQL. Your entire active database is 35 GB. Every single table and index fits completely inside the InnoDB Buffer Pool. Your disk I/O metrics show 0 reads/sec.

Why is a single primary-key lookup in MySQL still 20x to 50x slower than reading the exact same data from Redis running on the same machine?"

The candidate blinked, smiled, and answered:
"Wait... aren't both just reading raw bytes from physical DDR4 RAM?"

They are. Both systems run on the exact same silicon. Both execute on CPU cores pulling memory across identical memory buses with ~60ns access latency.

Yet, in production benchmarks:

  • Redis GET user:1001: ≈30 to 60 μs (microseconds).

  • MySQL SELECT * FROM users WHERE id = 1001 (100% Buffer Pool Cache Hit): ≈800 to 2,000 μs (0.8 to 2.0 ms).

A recent security and architectural audit at a high-growth fintech startup exposed this exact misconception. Their engineering team had spent $18,000/month scaling up RDS instance sizes to keep their entire working dataset in RAM, assuming it would deliver "Redis-like latency with ACID guarantees."

Instead, their p99 checkout latencies degraded by 300% during flash sales.

Where do those missing 1,500+ microseconds go if the data is already sitting in RAM?

Here is the deep mechanical autopsy of the memory architectures of MySQL and Redis.

             PHYSICAL REALITY: BOTH OPERATE ON IDENTICAL RAM CHIPS
             
   ┌─────────────────────────────────┐       ┌─────────────────────────────────┐
   │         REDIS MEMORY            │       │      MYSQL INNODB BUFFER POOL   │
   │                                 │       │                                 │
   │   Hash Table (O(1))             │       │   B+Tree Traversal (O(log N))   │
   │   Direct C-Pointer Dereference  │       │   16 KB Slotted Page Parsing    │
   │   Zero Mutexes (Event Loop)     │       │   Page Latches & Mutexes        │
   │   Current State (No Undo Logs)  │       │   MVCC ReadView & Undo Logs     │
   │   Zero-Copy RESP Protocol       │       │   SQL Parser & Execution Engine │
   └────────────────┬────────────────┘       └────────────────┬────────────────┘
                    │                                         │
                    ▼                                         ▼
            ~40 Microseconds                         ~1,500 Microseconds
                    └─────────── 40x - 50x GAP ───────────────┘

1. Mechanical Granularity: 16 KB Slotted Pages vs. Direct Memory Pointers

The most fundamental reason is how data is physically represented in memory.

Redis: Pure Pointer Dereference

Redis is a memory-native key-value store. It represents data structures using native C memory allocations (jemalloc or glibc malloc).

When you issue GET user:1001:

  1. Redis hashes the key using SipHash.

  2. It jumps directly to the bucket array in its global dictionary: O(1).

  3. It finds the dictEntry pointer, which directly points to a redisObject struct in memory.

  4. It reads the string buffer directly.

There are no intermediate layers, no block structures, and no page abstractions. It is a direct memory address dereference. The CPU pulls the data into L1/L2/L3 cache lines almost instantly.

MySQL InnoDB: The 16 KB Page Curse

MySQL was designed from the ground up as a disk-first database engine. The fact that it can cache pages in RAM is an optimization, not its native design paradigm.

Because disks read and write in blocks, InnoDB organizes everything into 16 Kilobyte Pages.

Even when a 16 KB page is cached 100% in RAM inside the InnoDB Buffer Pool, MySQL cannot simply "grab the row":

  1. B+Tree Index Traversal: To find id = 1001, MySQL must traverse the clustered index B+Tree.

    • Root Page (16 KB) → Branch Page (16 KB) → Leaf Page (16 KB).

    • Even with all three pages in RAM, the CPU must compute three separate memory lookups across distinct memory pages, suffering multiple CPU cache line misses.

  2. Binary Search Inside the Slotted Page:
    Once MySQL locates the correct 16 KB leaf page, the rows are not sequentially indexed. The page contains a Page Directory with sparse slots. MySQL must execute an in-memory binary search across directory slots to identify the row header.

  3. Column Decoding & Alignment:
    The CPU must read the record header, calculate variable-length column offset tables (e.g., VARCHAR, TEXT), decode NULL bitmasks, and de-serialize the binary storage format into MySQL's internal memory format.

Key Takeaway: In Redis, reading a record is a single pointer jump. In MySQL, reading a record from RAM requires traversing a 3-to-4 level B+Tree, executing binary searches within 16 KB byte arrays, and unpacking binary row headers.

2. The Concurrency Tax: Mutex Latches vs. The Lockless Event Loop

What happens when 5,000 concurrent client requests attempt to read the same hot row?

Redis: Zero Lock Contention

Redis processes commands on a single-threaded event loop (using epoll on Linux or kqueue on macOS/BSD).

Because only one thread ever executes commands:

  • Zero mutexes on the key space.

  • Zero read/write locks.

  • Zero thread context switching.

  • Zero CPU cache-line bouncing between different physical CPU cores.

The CPU core runs in a tight, uninhibited loop, executing in-memory reads at memory-bus line rate.

MySQL: The Buffer Pool Latch Storm

MySQL uses a multi-threaded architecture (one thread per client connection or an OS thread pool).

When dozens of threads query the Buffer Pool simultaneously:

  1. Page Latches (rw-locks): A thread cannot just read a 16 KB page in RAM. What if another thread is in the middle of rebalancing the B+Tree or writing an update? The reader thread must acquire an in-memory shared latch (S-latch) on the page frame.

  2. Buffer Pool Mutexes: Tracking which pages are hot vs. cold requires updating MySQL’s LRU list. Mutating this list requires acquiring the buf_pool->mutex. (Note: While MySQL allows splitting the Buffer Pool into multiple instances via innodb_buffer_pool_instances, contention remains high under load).

  3. OS-Level Thread Contention: When hundreds of threads fight for the same memory latch, the operating system intervenes. Threads enter spin-locks, burn CPU cycles, and eventually fall back to OS futex waits, triggering expensive kernel context switches (~1 to 2 microseconds each).

                      THE CONCURRENCY TAX IN RAM
                      
      REDIS                                   MYSQL INNODB
  [Single Thread]                        [Thread 1]   [Thread 2]   [Thread 3]
         │                                    │            │            │
         ▼                                    ▼            ▼            ▼
   Direct Read                         [ Acquire Page S-Latch (Mutex) ]
  (Zero Mutexes)                                      │
         │                                            ▼
         │                                    [ Update LRU Mutex ]
         │                                            │
         ▼                                            ▼
   ~40 Microseconds                           [ Thread Spin / Context Switch ]
                                                      │
                                                      ▼
                                              ~1,500 Microseconds

3. The Epistemic Tax of Truth: MVCC & Undo Log Traversal

In Redis, data has no philosophical doubts about its existence: the value in memory IS the current state of the universe.

MySQL, however, is an ACID relational database enforcing Multi-Version Concurrency Control (MVCC).

When you execute:

SELECT balance FROM accounts WHERE user_id = 1001;

MySQL cannot simply read what is currently sitting in the page. It must answer a complex historical question:

"What was the state of this row at the exact millisecond my transaction snapshot (ReadView) was created?"

To determine this in RAM:

  1. MySQL inspects the record's hidden system columns: DB_TRX_ID (the transaction ID that last modified the row) and DB_ROLL_PTR (the roll pointer).

  2. It evaluates whether DB_TRX_ID is visible to the current transaction’s ReadView.

  3. Walking the Undo Log Chain in RAM: If another transaction modified the row or if your transaction is running under REPEATABLE READ, MySQL must follow the DB_ROLL_PTR backwards into the Undo Log pages in the Buffer Pool. It must reconstruct the historical version of the row by applying delta diffs backwards in memory.

Even if all undo pages reside in the Buffer Pool, walking an in-memory pointer chain to rebuild old row states burns dozens of microseconds per query.

4. The Computational Stack: SQL Layer vs. RESP Protocol

Before MySQL even touches the InnoDB Buffer Pool, your query must survive the massive computational machinery of the Relational SQL Engine:

[ Client Query ]
       │
       ▼
1. Connection & Thread Context Switch
       │
       ▼
2. Lexical Analysis & Tokenizer (Lex / Yacc)
       │
       ▼
3. Abstract Syntax Tree (AST) Generation
       │
       ▼
4. Query Optimizer (Cost-based evaluation of indexes)
       │
       ▼
5. Execution Plan Compilation
       │
       ▼
6. Storage Engine Handler Interface (ha_innobase)
       │
       ▼
7. [ INNODB BUFFER POOL MEMORY LOOKUP ]
       │
       ▼
8. Row Reconstruction & Protocol Serialization

In contrast, Redis uses RESP (REdis Serialization Protocol):

  • It is a lightweight, human-readable, binary-safe, line-oriented protocol.

  • There is no lexer, no query optimizer, and no cost-based execution planner.

  • The command parser reads the first token (GET), indexes the hash table, and immediately writes the bytes to the socket buffer.

5. Latency Anatomy: Where Does the Time Go?

Here is the forensic breakdown comparing a primary-key read from the InnoDB Buffer Pool vs. Redis, assuming both have 100% data in RAM over a local/internal network:

Operation Phase: Network & Parsing

  • Redis (Pure In-Memory): 15−25 μs

  • MySQL (Buffer Pool Hit): 80−150 μs

  • Why MySQL Takes Longer: SQL parsing, AST generation, and cost-based query optimization.

Operation Phase: Index Traversal

  • Redis (Pure In-Memory): 5−10 μs (Hash Table)

  • MySQL (Buffer Pool Hit): 150−300 μs (B+Tree)

  • Why MySQL Takes Longer: Navigating 3-4 levels of 16KB pages; CPU L1/L2 cache misses.

Operation Phase: Concurrency / Locks

  • Redis (Pure In-Memory): 0 μs (Single-threaded)

  • MySQL (Buffer Pool Hit): 200−600 μs (Latches)

  • Why MySQL Takes Longer: Acquiring page S-latches, Buffer Pool mutexes, and thread synchronization.

Operation Phase: State Resolution

  • Redis (Pure In-Memory): 0 μs (Current value)

  • MySQL (Buffer Pool Hit): 100−300 μs (MVCC)

  • Why MySQL Takes Longer: Evaluating ReadView, checking DB_TRX_ID, and walking Undo Logs.

Operation Phase: Row Serialization

  • Redis (Pure In-Memory): 10−20 μs

  • MySQL (Buffer Pool Hit): 150−400 μs

  • Why MySQL Takes Longer: Unpacking slotted page offsets, decoding row headers, building tabular response.

Operation Phase: Total p50 / p95

  • Redis (Pure In-Memory): 30−60 μs

  • MySQL (Buffer Pool Hit): 800−1,800 μs

  • Why MySQL Takes Longer: ≈30x to 50x Difference

+-----------------------------------------------------------------------------------------+
| LATENCY ANATOMY: WHERE DOES THE TIME GO? (100% In-RAM Read)                             |
+-----------------------------------------------------------------------------------------+
| PHASE                     | REDIS (DRAM)    | MYSQL (BUFFER POOL) | BOTTLENECK          |
|---------------------------+-----------------+---------------------+---------------------|
| 1. Network & Parsing      | 15 - 25 µs      | 80 - 150 µs         | SQL Lexer & AST     |
| 2. Index Traversal        | 5 - 10 µs       | 150 - 300 µs        | B+Tree Cache Misses |
| 3. Concurrency / Latches  | 0 µs            | 200 - 600 µs        | Page Frame Mutexes  |
| 4. State / MVCC Check     | 0 µs            | 100 - 300 µs        | Undo Log Traversal  |
| 5. Row Extraction         | 10 - 20 µs      | 150 - 400 µs        | 16KB Page Decoding  |
|---------------------------+-----------------+---------------------+---------------------|
| TOTAL p50 / p95           | 30 - 60 µs      | 800 - 1,800 µs      | 30x - 50x SLOWER    |
+-----------------------------------------------------------------------------------------+

6. What About MySQL’s "Adaptive Hash Index" (AHI)?

MySQL engineers recognized this disparity years ago. To combat B+Tree traversal overhead in RAM, InnoDB introduced the Adaptive Hash Index (AHI).

  • How it works: InnoDB monitors index searches on the Buffer Pool. If it notices that specific B+Tree pages are being accessed repeatedly, it automatically builds an in-memory hash table pointing directly to the leaf page records.

  • The Catch: While AHI speeds up lookups, it introduces brutal mutex contention under high-concurrency multi-core write workloads (btr_search_latch). Many high-scale engineering teams (including Uber and Shopify) frequently disable AHI in production because the lock contention on the global hash index outweighs the read gains.

7. Architectural Rules of Thumb for Senior Engineers

  1. "In RAM" = "Fast as Redis":
    Never assume that sizing your database Buffer Pool to 100% of your dataset eliminates the need for an in-memory cache. A disk-centric relational database running in RAM is still paying the architectural taxes of B+Trees, MVCC, and page latches.

  2. Use MySQL Buffer Pool For:
    Complex relational joins, multi-row ACID transactions, aggregate reports, and durable ground truth where sub-millisecond latency is not required.

  3. Use Redis For:
    High-frequency point lookups (100k+ops/sec), global rate limiters, session caches, real-time counters, sorted leaderboards, and transient state where sub-millisecond p99 latency is an absolute requirement.

  4. Mechanical Sympathy Always Wins:
    Software performance is never just about the storage medium (RAM vs. NVMe). It is dictated by the number of CPU cache misses, memory indirection hops, mutex synchronizations, and protocol layers between your query and the raw bytes.

← Back to all posts