Understanding the “Missing” 1 GB Disk Space When Using Temporary Tables in MySQL 8.0

Question

In MySQL 8.0, using temporary tables appears to make about 1 GB of disk space “disappear”.

Experiment

We first create a MySQL 8.0.25 instance (setup steps omitted; refer to previous labs). Using the familiar doubling method, we create a large table to generate enough rows.

We continuously execute the final INSERT statement until the table contains a sufficient number of records.

Two temporary‑table configuration parameters are set; their purpose will be explained later.

We also enable performance_schema to monitor the whole process.

Record the current disk capacity.

Next we run a SQL statement that uses a temporary table (refer to experiment 6).

During execution we observe the disk space.

The total disk space reported by the OS does not change, but the MySQL‑tracked disk usage grows by roughly 1 GB and then drops again.

What actually happens during this period?

We summarize the temporary‑table lifecycle in MySQL 8.0.25:

By default, temporary tables use the TempTable engine and are first created in memory.

When the combined size of all in‑memory temporary tables reaches temptable_max_ram , MySQL switches to the mmap mechanism, mapping a portion of disk as memory, causing disk usage to rise.

When the mmap‑allocated memory (actually disk) reaches temptable_max_mmap , the temporary tables are converted to on‑disk tables (InnoDB or MyISAM). In our experiment we set temptable_max_ram to a minimal value to trigger mmap early.

After the SQL finishes, temporary tables are cleaned up, and disk usage drops.

We repeat the experiment to study how to observe each step.

Through performance_schema we can see the size of memory obtained via mmap (which is actually disk space).

Besides performance_schema , other methods such as reading /proc/<pid>/smaps can also reveal the allocated region.

Observations from procfs smaps:

The allocated region size grows exponentially (doubling).

The region corresponds to a file that has already been deleted.

When the temptable_max_mmap limit is reached, the in‑memory temporary table is converted to a disk‑based temporary table (InnoDB/MyISAM), which can also be seen via performance_schema .

This explains the “missing” disk space: MySQL uses mmap to map disk space into memory for temporary tables.

Tip After the SQL finishes, check performance_schema again. Although the temporary table has been reclaimed (disk usage drops), the CURRENT_NUMBER_OF_BYTES_USED metric does not return to zero. This is because MySQL accounts the reclaimed space in a global statistic rather than the thread‑level statistic. Consequently, thread‑level counters appear to only increase; use them with caution when performing analysis.

Do you have more MySQL topics you’d like to explore? Leave a comment below!