Impact of Full Table Scans on MySQL Server Memory and InnoDB Buffer Pool

When scanning a 200 GB InnoDB table on a MySQL server that only has 100 GB of RAM, the query does not consume the whole memory because MySQL streams rows directly to the client instead of building a complete result set on the server.

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

The data is read row‑by‑row and placed into net_buffer , whose size is defined by the net_buffer_length variable (default 16 KB). When the buffer fills, it is sent over the network and then cleared for the next rows.

Fetch a row → write to net_buffer .

Repeat until net_buffer is full → send via socket.

If the socket send buffer is full (EAGAIN/WSAEWOULDBLOCK), MySQL pauses reading until the network stack can accept more data.

The maximum memory a query occupies on the server is therefore limited to net_buffer_length , not the size of the result set. If the client reads slowly, the server’s send operation blocks, extending the query’s execution time but still avoiding memory exhaustion.

MySQL shows the state "Sending to client" when the server’s network buffer is full, while "Sending data" can simply indicate that the query is still executing (e.g., during lock wait or metadata transmission).

InnoDB stores data pages in the Buffer Pool (BP). The BP accelerates both writes (with WAL/redo logs) and reads. A high BP hit rate (≥99 %) means most pages are served from memory, eliminating disk I/O.

The Buffer Pool size is controlled by innodb_buffer_pool_size (typically 60‑80 % of physical RAM). When the pool fills, InnoDB evicts pages using an LRU algorithm.

Original LRU uses a single linked list: recently accessed pages move to the head, and the tail pages are evicted. For large scans, this would evict hot pages needed by active workloads.

InnoDB improves LRU by splitting the list into a New region (5/8 of the list) and an Old region (3/8). New pages go to the head; pages older than innodb_old_blocks_time (default 1 s) are placed at the Old region boundary. During a full‑table scan of historical data, new pages are inserted into the Old region, preserving the New region for hot data and preventing the scan from polluting the cache used by active queries.

Consequently, even a massive full‑table scan does not dramatically increase server memory usage, and the Buffer Pool’s hit rate for regular workloads remains stable.

Nevertheless, full‑table scans are I/O‑intensive and should be avoided on production primary instances during peak traffic.