Why Full Table Scans Won’t Exhaust MySQL Server Memory

1. Impact of Full Table Scan on the Server Layer

When a 200 GB InnoDB table db1.t is scanned with a command such as:

mysql -h $host -P $port -u $user -p $pwd -e "select * from db1.t" > $target_file

MySQL reads rows from the primary‑key index and streams them directly to the client. The server does not keep the entire result set in memory. Each fetched row is written into net_buffer , whose size is controlled by the net_buffer_length variable (default 16 KB). When the buffer fills, it is sent over the network and cleared for the next rows.

If the socket send buffer becomes full (e.g., returning EAGAIN or WSAEWOULDBLOCK ), MySQL pauses reading until the kernel’s send buffer can accept more data. Thus, the maximum memory a single query consumes on the server side is bounded by net_buffer_length, not by the total size of the result set.

A query’s in‑flight memory never exceeds net_buffer_length , so it cannot reach 200 GB.

The OS socket send buffer is also limited (e.g., /proc/sys/net/core/wmem_default), and when it fills MySQL blocks further reads.

MySQL therefore operates in a “read‑while‑send” mode. If the client reads slowly, the server’s execution time lengthens, but memory usage remains modest.

When many threads show the state “Sending to client”, it indicates that the server’s network stack is saturated. Increasing net_buffer_length can reduce the number of blocked threads.

2. Impact of Full Table Scan on InnoDB

InnoDB stores data pages in the Buffer Pool (BP). The BP accelerates both writes (via redo logs) and reads. The effectiveness of the BP depends on the memory hit rate, which should stay above 99 % for a stable production system.

During a full table scan, InnoDB reads pages from disk into the BP. Because the BP uses an LRU algorithm, scanning a large historical table can evict hot pages needed by active workloads, reducing the hit rate and increasing disk I/O.

InnoDB mitigates this with an improved LRU algorithm that separates the list into a “New” region (5/8 of the list) and an “Old” region (3/8). New pages are placed at the head; pages that reside in the Old region for more than innodb_old_blocks_time (default 1000 ms) are promoted to the head, while younger pages stay where they are.

This design ensures that sequential scans of large, cold tables mainly occupy the Old region, preserving the New region for hot data and keeping the hit rate for active queries stable.

3. InnoDB Memory Management

The basic LRU algorithm uses a linked list where the head holds the most recently accessed pages. When the list is full, inserting a new page evicts the tail page.

In the improved algorithm:

Pages accessed in the New region are moved to the head as before.

New pages that do not exist in the list are inserted at the boundary between New and Old (the LRU_old pointer).

Pages in the Old region are examined: if they have been in the list for more than 1 second, they are promoted to the head; otherwise they remain.

During a full scan of a 200 GB historical table, newly read pages are placed in the Old region, preventing them from displacing hot pages in the New region. Consequently, the buffer pool continues to serve active workloads efficiently.

4. Summary

MySQL streams query results using a limited net_buffer_length, so even massive full‑table scans do not exhaust server memory. InnoDB’s buffer pool, governed by an optimized LRU strategy, further ensures that large scans of cold data do not cause uncontrolled memory growth or severe hit‑rate degradation. Nevertheless, full scans are I/O‑intensive and should be avoided on primary servers during peak traffic.