MySQL Internal Temporary Tables: When They Switch to Disk Storage

Problem: In Experiment 05 we observed that MySQL internal temporary tables consume a lot of memory; when the required temporary table grows larger, it must use disk. The question is: at what point does MySQL move an internal temporary table to disk?

Experiment Setup: Using the same environment as Experiment 05, we run two sessions – a run session for the main SQL and a ps session to observe performance_schema . We reset the performance_schema counters, set the session temporary‑table size to 2 MiB (smaller than the previous experiment), and execute a SQL statement that creates a temporary table.

We also note the session thread IDs (run = 29) as shown below:

After resetting the performance_schema statistics:

We set max_heap_table_size (and thus tmp_table_size ) to 2 MiB and run the SQL that uses a temporary table:

Memory allocation records show a usage slightly above 2 MiB, indicating the temporary table consumes a bit more memory than the configured limit, which we consider negligible.

The statement’s execution plan reveals that it requires a temporary table that must be written to disk.

We enable the waits related counters in performance_schema and repeat the experiment (omitting the data‑preparation steps). After the run, the performance_schema statistics show:

Key observations:

The temporary‑table space wrote about 7.92 MiB of data.

The data was written gradually, not all at once.

Further analysis (referencing Experiment 03) shows that the writes are performed by the page_clean_thread , which flushes dirty pages of 16 KiB each, explaining the gradual I/O pattern.

Conclusion:

MySQL generally respects the max_heap_table_size setting; when memory is insufficient, the temporary table is automatically moved to disk.

The on‑disk engine follows the internal_tmp_disk_storage_engine configuration, so the amount of data written to disk can differ from the memory‑only experiment.

If the temporary table uses InnoDB as its disk engine, even a short‑lived table will cause dirty pages to be flushed to disk after release, incurring additional I/O.