Technology Sharing | How are MySQL's internal temporary tables stored?

Author: Hu Chengqing

DBA team member, good at failure analysis, performance optimization, personal blog: 16131e41d3ebb5 https://www.jianshu.com/u/a95ec11f67a8, welcome to discuss.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

If the data read during SQL execution cannot get the result directly, then additional memory is needed to store the intermediate result and get the final result. This additional memory is the internal temporary table. For example, when group by is executed, a temporary table needs to be constructed, and additional fields are needed to store the results of the aggregate function. Of course, in order to prevent excessive memory usage, it will generally be placed on disk after a certain limit is exceeded. Regarding which operations will generate internal temporary tables, you can check the official document: https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html, the following mainly introduces MySQL8.0 internal temporary Changes in table storage.

MySQL 5.6

In MySQL 5.6, when the internal temporary table size exceeds the memory limit, it is created in a single table file table space in the temporary directory. If innodb_file_per_table is disabled, it is created in the InnoDB shared table space (ibdata1) in the data directory, which is easy As a result, ibdata1 is too large and cannot be released. It can only be solved by logically exporting data and migrating to a new instance.

MySQL 5.7

MySQL 5.7 has made improvements to the temporary table space. The temporary table space has been separated from the InnoDB shared table space or the independent table space. It is now called the shared temporary table space. There are two benefits:

1. Can eliminate the performance cost of creating and deleting each temporary table;
2. It is a separate table space for internal temporary tables. Restarting mysqld can reset its size, avoiding the problem that ibdata1 is difficult to release in MySQL 5.6.

The manifestation is that an ibtmp1 file will be created under the datadir when MySQL starts, and it will expand indefinitely by default. For example, if an internal disk temporary table with a size of 20MB is created when a certain SQL is executed, the temporary tablespace file with a default size of 12MB at the time of creation will be expanded to 20MB to fit the table. When SQL is executed and the temporary table is deleted, the freed space can be reused for the new temporary table, but the ibtmp1 file maintains the expanded size. Only when MySQL is restarted will the shared temporary table space be reclaimed and become the initial size of 12MB.

Related parameters:

• tmp_table_size&max_heap_table_size, the internal temporary table is stored in memory, using the MEMORY storage engine, if the size exceeds the smaller value of the two, it will be converted into a disk temporary table;
• internal_tmp_disk_storage_engine: If the internal temporary table is converted to a disk temporary table, this parameter specifies the storage engine of the disk temporary table. The default is INNODB, and it can also be set to MYISAM;
• innodb_temp_data_file_path: Specifies the location and size of the temporary tablespace. The default value is ibtmp1:12M:autoextend , which is datadir/ibtmp1. The initial size is 12M and can be expanded indefinitely. It is recommended to limit a maximum value to prevent the disk from filling up.

Disadvantages: The internal temporary table generated after SQL execution may be very large and must be restarted to release. This once made me very confused, why can't I do it better and release it after execution? Fortunately, MySQL8.0 optimizes this problem.

MySQL 8.0

MySQL 8.0 has another major change, adding some new parameters:

• internal_tmp_mem_storage_engine: used to specify the storage engine of the internal temporary table in memory, the default value is TempTable instead of the previous default MEMORY
• temptable_max_ram: defines the maximum amount of memory that the TempTable storage engine can occupy before storing data on the disk, the default value is 1G
• temptable_use_mmap: Defines whether the TempTable storage engine allocates space for the internal temporary table in memory as a temporary file for memory mapping when the amount of memory occupied by the TempTable storage engine exceeds the limit defined by the temptable_max_ram variable. When temptable_use_mmap is disabled, the InnoDB disk internal temporary table will be used instead. The default value is ON, introduced in 8.0.16, and deprecated in 8.0.26.
• temptable_max_mmap: Defines the maximum amount of memory (in bytes) that the TempTable storage engine is allowed to allocate from memory-mapped temporary files before it starts storing data in the InnoDB internal temporary table on disk. Set to 0 to disable allocating memory from memory-mapped temporary files. The default value is 1G, introduced in 8.0.23.

Memory mapped temporary files

In other words, by default, SQL is executed to generate internal temporary tables, and the storage engine used has changed from MEMORY to TempTable. Of course, TempTable is still a memory table, and the maximum memory that can be used is 1G (default). When the size exceeds 1G, the memory-mapped temporary file will be used as the overflow mechanism of the internal temporary table. The vernacular is to prevent the memory from being used too much and put the data in the memory in the temporary file.

But if you think about it, relational databases are designed with something as good as a storage engine to store data. Isn't it a bit too much to use files for storage at this time? It is estimated that the official thinks this way: Oh, the internal temporary table is very small, I will release it temporarily, you can bear it. Later I found that some internal temporary tables were too big to bear. In order to prevent the memory mapping temporary files from being too large, version 8.0.23 introduced a new parameter temptable_max_mmap to limit its size. If it exceeds its size (default 1G), it will be converted to a disk temporary table. (This is consistent with MySQL 5.7). It is worth noting that the 8.0.26 flag of the temptable_use_mmap parameter is deprecated, and the official documentation also suggests that it is recommended to be set to 0 to turn it off, so I personally understand that uses a memory-mapped temporary file as an overflow mechanism for internal temporary tables is a bad solution.

TempTable

Why change the default engine for internal temporary tables to TempTable? The biggest difference between it and MEMORY is:

• Can support variable length types, such as varchar(100) data "abcd" should only occupy 4 bytes instead of 100 bytes, saving memory;
• Supports binary large objects, such as blob, text, etc. If you use the MEMORY engine, such internal temporary tables will directly use disk temporary tables, this is to improve performance.

So is it really that easy to use? The latest version is 8.0.26, and there are still some problems, such as:
https://bugs.mysql.com/bug.php?id=98782
https://bugs.mysql.com/bug.php?id=98739
https://bugs.mysql.com/bug.php?id=99593
https://bugs.mysql.com/bug.php?id=99100

The first three are performance issues, the latter one may cause an error during SQL execution: The table'/tmp/#sql639b7_13_4' is full, so before these issues are resolved, it is recommended to set internal_tmp_mem_storage_engine=MEMORY .

Temporary table space

MySQL 8.0 temporary table space has also changed. It is divided into session temporary table space and global temporary table space. The global temporary table space is no different from MySQL 5.7, but the internal temporary table generated by SQL will be stored in session temporary. In the table space.

New parameters:

• innodb_temp_tablespaces_dir: defines the location of temporary tablespaces for creating sessions, the default location is the #innodb_temp directory in the data directory

shell> ls datadir/#innodb_temp
temp_10.ibt  temp_2.ibt  temp_4.ibt  temp_6.ibt  temp_8.ibt
temp_1.ibt   temp_3.ibt  temp_5.ibt  temp_7.ibt  temp_9.ibt

The session temporary table space is actually a pool containing 10 temporary table spaces. The session temporary table space is allocated to the session from the temporary table space pool when the disk temporary table is created for the first time. A session allocates up to two table spaces, one for user-created temporary tables, and the other for internal temporary tables created by the optimizer. When the session is disconnected, its temporary table space is cleared and released back to the pool.

Test phenomenon

When temptable_use_mmap = ON, if the internal temporary table exceeds the size of temptable_max_ram, the memory-mapped temporary file is used as the overflow mechanism of the internal temporary table, and the temporary file is placed in the tmpdir directory:

You can see that the number of temporary files +1, and the number of disk temporary tables remains unchanged:

When temptable_use_mmap = OFF, if the internal temporary table exceeds the size of temptable_max_ram, the InnoDB disk internal temporary table is used as the overflow mechanism of the internal temporary table, and it is stored in the innodb session temporary table space. The difference with MySQL 5.7 is that after the session is disconnected, Will free up space, no need to restart MySQL:

You can see that the number of temporary files remains unchanged, and the number of disk temporary tables +1: