Why Full Table Scans Won’t Exhaust MySQL Memory – Server and InnoDB Insights

Full Table Scan Impact on Server Layer

When scanning a 200 GB InnoDB table, MySQL reads the primary‑key index directly and streams rows to the client without storing the entire result set in server memory.

The data flow is:

Each row is written to net_buffer , whose size is defined by net_buffer_length (default 16 KB).

When net_buffer fills, it is sent through the network socket and then cleared.

If the socket send buffer is full, MySQL receives EAGAIN or WSAEWOULDBLOCK and pauses until the socket becomes writable.

Thus, the maximum memory a query occupies on the server is roughly net_buffer_length , not the size of the result set.

Query Result Sending Process

During execution, MySQL may show states such as "Sending to client" (waiting for the client to read) or "Sending data" (still executing). The latter does not necessarily mean data is being transmitted; it can indicate any stage of execution, including lock waiting.

If a client reads slowly, the server’s query duration increases, but memory usage remains bounded.

InnoDB Buffer Pool and Memory Management

The buffer pool (BP) caches data pages to accelerate reads and writes. Its size is controlled by innodb_buffer_pool_size , typically set to 60‑80 % of physical memory.

BP uses an LRU algorithm to evict pages. The original LRU could evict hot pages during a large scan, harming active workloads.

Improved LRU Algorithm for Large Scans

InnoDB splits the LRU list into a New region (5/8 of the list) and an Old region (3/8). New pages go to the head; pages inserted during a scan are placed at the Old region boundary. Pages older than innodb_old_blocks_time (default 1 s) are moved to the head; younger pages stay in Old.

This design ensures that a massive scan of historical data fills the Old region without displacing pages needed by active queries, preserving the buffer pool hit rate for hot data.

Practical Recommendations

For queries returning modest result sets, use mysql_store_result to buffer results locally.

For very large result sets, switch to mysql_use_result (or the --quick client option) to fetch rows one‑by‑one.

Increase net_buffer_length if many threads are blocked in "Sending to client".

Conclusion

MySQL streams results, so full‑table scans do not exhaust server memory. InnoDB’s optimized LRU algorithm further protects the buffer pool during large scans, but such scans still consume I/O resources and should be avoided on hot production tables.