Can a MySQL full‑table scan of a 200 GB table exhaust a server with 100 GB RAM?

When a client runs

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

to scan a 200 GB InnoDB table, MySQL reads the primary‑key index and streams each row directly to the client.

The server does not keep the entire result set in memory. For each row it fills the net_buffer (default 16 KB, controlled by net_buffer_length ), sends the buffer when full, and clears it after a successful send. If the socket send buffer is full, MySQL pauses reading until the network stack becomes writable.

Consequently, the maximum memory MySQL uses for a single query is bounded by net_buffer_length , not by the size of the result set, and the socket send buffer also cannot grow to hundreds of gigabytes.

If the client reads slowly, the server thread stays in the "Sending to client" state, extending the transaction duration, but it still does not exhaust RAM.

InnoDB stores updated pages in the Buffer Pool (BP). The BP size is set by innodb_buffer_pool_size (typically 60‑80 % of physical memory). BP pages are cached to accelerate reads; a high hit rate (≥99 %) is desired for production workloads.

During a full‑table scan of a large historical table, the original LRU algorithm would evict all hot pages, dramatically lowering the hit rate and increasing disk I/O. InnoDB therefore uses an improved LRU that splits the list into a New region (5/8) and an Old region (3/8). New pages are inserted at the head; pages in the Old region are only promoted to the head if they have been in the list for more than 1 second (controlled by innodb_old_blocks_time , default 1000 ms).

This design ensures that sequential scans of a massive table fill the Old region without displacing pages needed by active workloads, preserving the Buffer Pool hit rate for hot data.

In summary, MySQL streams results row‑by‑row, limiting server‑side memory usage to the net buffer size, while InnoDB’s optimized LRU algorithm prevents large scans from polluting the Buffer Pool, so a 200 GB scan on a 100 GB server will not cause an OOM condition, though it may increase I/O and should be avoided during peak traffic.