Why SELECT * 20M Rows Won't OOM MySQL: Streaming Protocol & LRU Protection

Introduction

A friend with 5 years of Java experience faced a tricky interview question: "If network latency is ignored, will executing SELECT * to fetch 20 million rows in MySQL cause the server to OOM?" He confidently answered yes, assuming 20GB of data would exceed the Buffer Pool. The interviewer rejected him, stating he knew nothing about MySQL's protocol and memory management.

The misconception stems from applying JVM memory models to databases. In Java, loading 20 million objects into a List would indeed OOM. MySQL, however, uses a streaming (edge-read-edge-send) process.

Mine 1: Does MySQL Really "Swallow Everything at Once"?

Conclusion: With proper configuration, MySQL server will never OOM due to a single large query.

MySQL does not "read all 20M rows → package → send". Instead, it operates in a streaming fashion:

Scan engine layer: InnoDB scans one row.

Write to buffer: Server layer puts the row into net_buffer (controlled by net_buffer_length, default 16KB).

Trigger send: Once net_buffer is full or a batch finishes, MySQL pushes the packet to the client.

Clear and reuse: After sending, net_buffer is cleared for the next row.

Truth: Regardless of 20M or 20B rows, server-side memory only holds net_buffer_length worth of data. It's a pipe, not a bucket.

Caveat: In practice, the client will likely time out or OOM first (e.g., Java client can't handle the volume), but the MySQL server itself remains stable.

Mine 2: No OOM Means Safe? The Real "Grim Reaper" Lurks Behind

Even without OOM, a full table scan causes a worse disaster: Buffer Pool Pollution .

The Buffer Pool holds hot data: user sessions, flash-sale inventory, popular articles — all served in milliseconds. A SELECT * scanning 20M cold rows (e.g., 3-year-old logs) forces InnoDB to read those pages into the Buffer Pool. Under standard LRU, these "use-once" garbage pages evict precious hot data.

Result: The export runs happily, MySQL doesn't crash, but soon the whole site slows: logins lag, products won't load. Hot data is gone, disk IO saturates, and the system enters a "fake death" state.

Mine 3: InnoDB's "Anti-Pollution" Shield (Source-Code Proof)

Why doesn't mysqldump crash production? It also uses streaming queries and leverages InnoDB's LRU cold-hot separation strategy.

Evidence 1: New Data Defaults to "Cold Palace"

Many tutorials claim LRU puts new data at the head. In InnoDB source, full-scan pages are inserted at the head of the Old Sublist (the LRU midpoint), never touching the hot zone.

Location:

storage/innobase/buf/buf0lru.cc

// Function: buf_LRU_add_block (add page to LRU)
void buf_LRU_add_block(buf_pool_t* buf_pool, buf_page_t* bpage, ibool old) {
    // ... preamble ...
    // [Key 1] Decide whether to insert into "cold list" (old list)
    // During full table scan, 'old' parameter defaults to TRUE
    if (old) {
        // Insert directly at LRU_old pointer (cold-hot boundary)
        // This is the "Midpoint Insertion Strategy"
        UT_LIST_INSERT_AFTER(LRU, buf_pool->LRU, buf_pool->LRU_old, bpage);
    } else {
        // Only extremely rare cases insert at head
        UT_LIST_ADD_FIRST(LRU, buf_pool->LRU, bpage);
    }
}

Analysis: The if (old) branch places new pages directly in the cold zone, denying them any chance to pollute LRU_new (hot zone).

Evidence 2: The Time Barrier

The core logic: during a full scan, pages are accessed but InnoDB checks "are you newly arrived?" If the page has been in the Old Sublist for less than 1 second (default), promotion is refused!

Location:

storage/innobase/buf/buf0lru.cc

// Function: buf_page_make_young_if_needed (attempt to move page to hot zone)
void buf_page_make_young_if_needed(buf_pool_t* buf_pool, buf_page_t* bpage) {
    // [Key 2] Check if page is in "cold list"
    if (buf_page_is_old(bpage)) {
        // [Key 3] Hard comparison of time and parameter
        // buf_LRU_old_threshold_ms is parameter innodb_old_blocks_time (default 1000ms)
        if (now - access_time < buf_LRU_old_threshold_ms) {
            // If access interval < 1 second, return immediately!
            // Even if read, must stay in cold zone!
            return;
        }
        // Only after surviving 1000ms can it promote to hot zone
        buf_LRU_make_block_young(bpage);
    }
}

Analysis: Full scans are pipeline operations: read row → send → read next. Access to the same page occurs within milliseconds, far below 1000ms, so return executes. Your 20M garbage pages merely "day-trip" in the cold zone before eviction. True hot data (New Sublist) remains untouched!

Note: innodb_old_blocks_time specifically targets full scans or large-range scans; it doesn't affect normal high-frequency point queries.

✅ Champion Interview Answer Template (Full Marks)

Next time you're asked "Will a large table query OOM?", deliver this three-part combo:

"View this from two dimensions.

First, OOM: MySQL server will never OOM . It uses a 'read-and-send' streaming protocol. Data is batched into net_buffer (full name net_buffer_length, default 16KB) and flushed; no data piles up in memory. The real OOM risk is the client (e.g., Java List can't hold it).

Second, the real hidden danger (Buffer Pool pollution): Physical memory won't crash, but the biggest risk of a full scan is evicting hot cache . With a naive LRU, a full scan would push out all hot data, causing disk IO spikes and system avalanche.

Third, InnoDB's source-level defense: InnoDB employs a 'cold-hot separation' strategy (Midpoint Insertion). I've read buf0lru.cc source: new pages default into LRU_old. Combined with innodb_old_blocks_time (default 1s), full-scan data fails the promotion condition due to 'extremely short access intervals', stays in the cold zone, and gets evicted — perfectly protecting hot data."

Final Words

Details determine success; source code determines height. Many treat MySQL as a black box, but top-tier interviews test your understanding of that box's "temperament". Being able to articulate net_buffer 's streaming mechanism and the buf_LRU_add_block low-level logic marks you as a P7 in the interviewer's eyes.