Why Full Table Scans Won’t Exhaust MySQL Server Memory

Full Table Scan Impact on MySQL Server

When a client runs 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 of the InnoDB table and writes them into net_buffer , whose size is defined by the net_buffer_length variable (default 16 KB). The server sends the buffer to the client, clears it, and repeats until all rows are transferred.

The server never stores the entire result set in memory; it only holds at most net_buffer_length bytes, so even a 200 GB scan cannot exhaust the host’s 100 GB RAM. If the client reads slowly, the network send buffer may fill, causing the server to block on sending (state “Sending to client”), but memory usage remains bounded.

For small result sets it is recommended to use mysql_store_result (stores the whole result in client memory). For large result sets, mysql_use_result streams rows one by one, avoiding large memory consumption on the client side.

InnoDB Buffer Pool and LRU

The InnoDB buffer pool (BP) caches data pages in memory, accelerating reads. Its size is controlled by innodb_buffer_pool_size (typically 60‑80 % of physical RAM). A healthy system maintains a buffer‑pool hit rate above 99 %.

InnoDB uses an improved LRU algorithm that splits the list into a “New” region (5/8 of the list) and an “Old” region (3/8). Pages accessed for less than innodb_old_blocks_time (default 1 s) stay in the Old region; pages older than that may be promoted to the head of the list. During a full‑table scan of a large historical table, new pages are inserted into the Old region, leaving the New region (hot data) untouched, thus preserving hit rate for regular workloads.

When the buffer pool is full, the least‑recently‑used page in the Old region is evicted to make room for newly scanned pages. This prevents a massive scan from evicting frequently accessed hot pages.

Overall, MySQL’s “read‑and‑send” model and InnoDB’s optimized LRU ensure that large scans do not cause memory blow‑out, though they still generate significant I/O and should be avoided on a busy primary during peak traffic.