q

In MySQL 8.0, 1GB of disk space “disappeared” when using temporary tables

The experiment

Let’s start with an example of MySQL 8.0.25. We will ignore the creation step here, so you can refer to the previous experiment.

Again, using the familiar method of doubling, make a table:

SQL > create a table with as many records as possible;

Here we set the configuration parameters for the two temporary tables, which we will explain later:

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

Also note the current disk capacity:

Now let’s go to experiment 6 for the next SQL that uses temporary tables:

During SQL execution, look at the disk space:

The total number of disks in the database doesn’t change the whole time, but the total number of disks grows gradually, increasing by about 1 gigabyte and then falling back down again

What happened in the meantime?

MySQL > use temporary tables in MySQL 8.0.25

  1. In 8.0.25, the default engine for temporary tables is TempTable, and the in-memory temporary tables are created in memory first
  2. When the total size of all memory temporary tables reaches the temptable_max_ram limit, MySQL uses the mmap mechanism to use part of the disk map as memory, and disk usage increases in the process. (We set temptable_max_ram to the minimum in our experiment to make it easier for MySQL to use the mmap mechanism as early as possible.)
  3. When the temptable_max_mmap limit is reached for the amount of memory allocated by mmap (actually disks) for all memory temporary tables, MySQL will convert memory temporary tables to disk temporary tables (InnoDB or MyISAM engine). (We set temptable_max_RAM to 1G in our experiment.)
  4. After the SQL finishes, the temporary table is cleaned up, and in the process, disk usage drops

Let’s rerun the experiment to see how we can observe this process:

Of course, from performance_schema, you can see how much memory is allocated by mmap (actually disk size).

Other than using performance_schema, is there any other way to observe it?

We can also observe this space through the SMAPS of PROCFS

It can be seen that the space allocated by mmap has two characteristics:

  1. The size of the allotted area will gradually double
  2. This corresponds to a file that has been deleted

When the temptable_max_mmap limit is reached, the in-memory temporary tables are converted to disk temporary tables (InnoDB/MyISAM tables). This step can also be observed in the performance_schema:

This is the truth of “missing disks” : MySQL uses mmap to map disk space into memory for use as memory.

Tips:

After the SQL execution is complete, let’s take a closer look at the performance_schema:

When the SQL finishes, you can observe that the temporary table has been reclaimed (the disk space has decreased), but CURRENT_NUMBER_OF_BYTES_USED does not return to zero.

The reason for this is that MySQL does not count the space collection on the SQL thread, but instead counts it into the global statistics:

This can result in thread-level statistics that appear to be “increasing, not decreasing,” and you need to be careful when using them for statistics

Is there anything else you’d like to know about MySQL’s technical content? Leave a message to tell Xiaobian!