Question 42: Temporary tables in MySQL 8.0 will make a piece of disk space "disappear"

ask

In MySQL 8.0, when using temporary tables, you will find that 1G of disk space "disappears"

experiment

Let's make a MySQL 8.0.25 instance first. Here we ignore the creation steps, you can refer to the previous experiment.

Or use the familiar doubling method to create a table:

Execute the last sentence of SQL continuously to make the table contain enough records:

Here we set the configuration parameters of two temporary tables, which will be explained later:

We also need to set up performance_schema to observe the entire process:

You also need to record the current disk capacity:

Now our next SQL using temporary tables, refer to Experiment 6:

During the execution of SQL, observe the disk space:

The total amount of disks in the database has not changed throughout the entire process, and the total amount of disks will gradually increase, increasing by about 1G, and then it will fall again

What happened during this time?

Let's sort out the use process of temporary tables in MySQL 8.0.25:

In 8.0.25, the default engine for temporary tables is TempTable, which will first create a memory temporary table in memory
When the total size of all the memory temporary tables reaches the temptable_max_ram limit, MySQL will use the mmap mechanism to use a part of the disk mapping as memory. During this process, the disk usage will increase. (We set temptable_max_ram to the minimum value in the experiment, in order to let MySQL use the mmap mechanism as soon as possible, the experiment will be more convenient)
When the amount of memory allocated by mmap for all memory temporary tables (actually disk) reaches the temptable_max_mmap limit, MySQL will convert the memory temporary table into a disk temporary table (engine is InnoDB or MyISAM). (We set temptable_max_ram to 1G in the experiment )
After the SQL is over, the temporary table will be cleaned up, during this process, the disk usage will drop

Let's do this experiment again and study how to observe this process:

Of course, through performance_schema, you can see the memory size allocated by mmap (actually the disk size)

In addition to using performance_schema, are there other means to observe?

We can also observe this space through the smaps of procfs

You can see: Two characteristics of the space allocated by mmap:

The size of the allocated area will gradually double
It corresponds to a deleted file

When the temptable_max_mmap limit is reached, the memory temporary table will be converted to a disk temporary table (InnoDB/MyISAM table). This step can also be observed through performance_schema:

This is the truth of the "disappearing disk": MySQL uses mmap to map disk space into memory and use it as memory.

Tips
When the SQL execution is complete, let's take a closer look at the performance_schema:
When the SQL is executed, it can be observed that the temporary table has been reclaimed (disk space has decreased), but CURRENT_NUMBER_OF_BYTES_USED will not return to zero.
The reason for this is that MySQL does not count the recovery of this space on the SQL thread, but counts it into the global statistics:
So it will cause the thread-level statistical value to look "only increase but not decrease". Be careful when using this value for statistics