Technology Sharing | Talking about MySQL's Temporary Tables and Temporary Files

Author: Yao Song

The manager of the delivery service department in the southern area of Ecoson, loves music, animation, movies, games, humanities, food, tourism, and others. Although they are all dishes, they are hobbies after all.

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.

---

The content of this article comes from thinking about three questions from customers:

1. Which SQL will generate temporary tables/temporary files
2. How to view existing temporary tables
3. How to control the total size of temporary tables/temporary files

Description:

The following tests are all completed in MySQL 8.0.21 version, different versions may have differences, you can test by yourself;

First, let us understand what is a temporary table|temporary file?

Temporary tables and temporary files are used to temporarily store data sets;

In general, the data set that needs to be temporarily stored in a temporary table or temporary file should meet the following characteristics:

• The data set is small (large temporary data set generally means poor SQL, of course there are exceptions)
• Clean up when used up (because it is a place to temporarily store data sets, so the life cycle is the same as the SQL or session life cycle)
• Session isolation (because it is a temporary data set, does not involve interaction with other sessions)

• Does not generate GTID

From the subjectivity of temporary tables|temporary files, it can be divided into two categories:

• Temporary table created by the user
• Temporary table generated by SQL|Temporary file

user creates a temporary table:

The user creates a temporary table (only the session that created the temporary table can view the contents of the temporary table it created)

create database if not exists db_test ;
use db_test ;
CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
select * from db_test.t1 ;

note:

You can create a temporary table with the same name as a normal table, and other sessions can see the normal table (because you cannot see the temporary table created by other sessions);

The session that created the temporary table will see the temporary table first;

The statement for the creation of the table with the same name is as follows

CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;
insert into  t1 values(1);

When there is a temporary table with the same name, the session will give priority to the temporary table (rather than the ordinary table), including: select, update, delete, drop, alter and other operations;

View the temporary table created by the user:

Any session can execute the following statement;

View the current active temporary table created by the user (the internal InnoDB temporary table information used by the optimizer is not provided)

SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G

note

The temporary table created by the user, the table name is t1,

But the name of the temporary table viewed through INNODB_TEMP_TABLE_INFO is the name beginning with #sql, for example: #sql45aa_7c69_2;

In addition, information about temporary tables is not recorded in the information_schema.tables table.

Recycling of temporary tables created by users:

1. The session is disconnected, and the temporary table created by the user is automatically recycled;
2. You can delete temporary tables created by users through drop table, for example: drop table t1;

Other information & parameters of the temporary table created by the

The session temporary table space stores the temporary tables created by the user and the internal temporary tables created by the optimizer (when the storage engine of the internal temporary tables on the disk is InnoDB);

The innodb_temp_tablespaces_dir variable defines the location where the session temporary tablespace is created. The default is the #innodb_temp directory under the data directory;

The file is similar to temp_[1-20].ibt;

View the metadata of the session temporary table space:

select * from information_schema.innodb_session_temp_tablespaces ;

After the temporary table created by the user is deleted, the space occupied by it will be released (temp_[1-20].ibt file will become smaller).

Before MySQL 8.0.16, the internal_tmp_disk_storage_engine variable defines the engine of the temporary table created by the user and the internal temporary table created by the optimizer. INNODB and MYISAM are optional;

Starting from MySQL 8.0.16, the internal_tmp_disk_storage_engine parameter has been removed, and the InnoDB storage engine is used by default;

innodb_temp_data_file_path defines the relative path, name, size and attributes of the storage file of the rollback segment used by the temporary table created by the user. This file is the global temporary table space (ibtmp1);

You can use the statement to query the data file size of the global temporary table space:

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE

AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
WHERE TABLESPACE_NAME = 'innodb_temporary'\G 

SQL generate temporary tables|temporary files?

When you need to use a temporary table or temporary file, the optimizer will naturally create and use it (it feels nonsense, but it makes sense=.=!);

(If you have a strong imagination, you can remember the above sentence; if you have a weak imagination, you can only memorize the following SQL. I am also weak, and there is a tired smile here😊)

Here are some examples of SQL that the server may create internal temporary tables when processing SQL:

SQL contains union | union distinct keywords

Derived table exists in SQL

SQL contains the with keyword

The order by and group by fields in SQL are different

SQL is multi-table update

SQL contains two keywords distinct and order by

We can determine whether the SQL statement uses a temporary table space in the following two ways:

# If the Extra column of explain contains Using temporary, it means that temporary space will be used. If it contains Using filesort, then it means that file sorting (temporary files) will be used;

explain xxx ; 

If the ID column of the table changes to the value of the id column in the show processlist after the SQL is executed, it means that the SQL statement uses a temporary table space

select * from information_schema.innodb_session_temp_tablespaces ;   

The storage information of the internal temporary table created by SQL:

When SQL creates an internal temporary table, the priority is in memory, and the TempTable storage engine is used by default (determined by the parameter internal_tmp_mem_storage_engine),

When the amount of memory used by temptable exceeds the size defined by temptable_max_ram,

Temptable_use_mmap determines whether to use memory mapped files or InnoDB disk internal temporary tables to store data

(The temptable_use_mmap parameter was introduced in MySQL 8.0.16, MySQL 8.0.26 is not recommended, and should be removed in the future);

The function of temptable_use_mmap will be replaced by temptable_max_mmap introduced in MySQL version 8.0.23.

When temptable_max_mmap=0, it means that the memory mapped file is not used, which is equivalent to temptable_use_mmap=OFF;

When temptable_max_mmap=N, N is a positive integer, including temptable_use_mmap=ON and the maximum amount of memory allowed to be allocated for memory mapped files is declared.

The definition of this parameter solves the risk of these files using too much space.

Temporary files generated by memory-mapped files will be stored in the directory defined by tmpdir, and the space will be reclaimed when the TempTable storage engine is shut down or the mysqld process is shut down;

When the MEMORY storage engine is selected for the internal temporary table created by SQL, if the temporary table in memory becomes too large, MySQL will automatically convert it to a disk temporary table;

The upper limit of the memory that can be used is min(tmp_table_size,max_heap_table_size);

monitors the space allocated by TempTable from memory and disk:

select * from performance_schema.memory_summary_global_by_event_name \
where event_name in('memory/temptable/physical_ram','memory/temptable/physical_disk') \G

For specific field meanings, see: Section 27.12.20.10, “Memory Summary Tables”.

monitors the creation of internal temporary tables:

When creating internal temporary tables in memory or disk, the server will increase the value of Created_tmp_tables;

When creating an internal temporary table on the disk, the server will increase the value of Created_tmp_disk_tables,

If too many internal temporary tables are created on the disk, please consider increasing the values of tmp_table_size and max_heap_table_size;

created_tmp_disk_tables does not count the disk temporary tables created in the memory-mapped file;

exception:

Temporary tables/temporary files are generally small, but there are also requirements for temporary tables/temporary files that require a lot of space:

1. Load data local statement, the client reads the file and sends its content to the server, and the server stores it in the path specified by the tmpdir parameter;

2. In replica, when replaying the load data statement, the data parsed from the relay log needs to be stored in the directory specified by slave_load_tmpdir (replica_load_tmpdir), which is the same as the path specified by the tmpdir parameter by default;
3. The online alter table that needs to rebuild the table needs to use innodb_tmpdir to store the sort disk sort files. If innodb_tmpdir is not specified, the value of tmpdir is used;

Because these exceptions generally require a lot of space, you need to consider whether to store them on a separate mount point.

Other:

List the hidden temporary tables created by the failed alter table. These temporary tables start with #sql and can be deleted using drop table;

show extended tables ; 

Through lsof +L1, you can view the files that are marked as delete, but the space has not been released yet.

If you want to release these files in the delete state, you can try the following methods (not recommended, at your own risk):

cd /proc/${pid}/fd   # ${pid} 表示你想释放的delete状态的文件持有者的进程号
ls -al | grep '${file_name}'    # 假设${file_name}是/opt/mysql/tmp/3306/ibBATOn8 (deleted)
echo "" > ${fd_number}    # ${fd_number} 表示你想释放的delete状态的文件的fd，倒数第三个字段，如echo "" > 6

to sum up:

Ordinary disk temporary table|Temporary file (generally needs smaller space):

Temporary tables|temporary files generally require less space and will be stored in memory first. If it exceeds a certain size, it will be converted to disk temporary tables|temporary files;

The disk temporary table defaults to the InnoDB engine, which is stored in the temporary table space. The storage directory of the table space is defined by innodb_temp_tablespaces_dir. The table space file is similar to: temp_[1-20].ibt; MySQL does not define the maximum use limit of InnoDB temporary table space. ；

When the temporary table|temporary file is used up, the size of the temporary tablespace file will be automatically recovered;

innodb_temp_data_file_path defines the relative path, name, size and attributes of the storage file of the rollback segment used by the temporary table created by the user. This file is the global temporary table space (ibtmp1). This file can set the maximum file size;

exception items (generally need more space):

Load data local statement, the client reads the file and sends its content to the server, and the server stores it in the path specified by the tmpdir parameter;

In replica, when replaying the load data statement, the data parsed from the relay log needs to be stored in the directory specified by slave_load_tmpdir(replica_load_tmpdir), which is the same as the path specified by the tmpdir parameter by default;

The online alter table that needs to rebuild the table needs to use innodb_tmpdir to store the sort disk sort files. If innodb_tmpdir is not specified, the value of tmpdir is used;

If the user judges that the generated temporary table|temporary file will definitely be converted to disk temporary table|temporary file, then you can set set session big_tables=1; let the generated temporary table|temporary file be directly stored on the disk;

Guess its design:

For a temporary table|temporary file that requires a small space, MySQL either stores it in memory or puts it in a unified disk temporary table space, and releases it when it is used up;

For temporary tables|temporary files that require a large space, you can store them in a separate directory|mount point by setting parameters; for example: load local data statement or online alter table statement that needs to rebuild the table, all have corresponding parameters Set the path for storing temporary tables|temporary files;

Currently, only the innodb_temp_data_file_path parameter can limit the size of the storage file of the rollback segment used by the temporary table created by the user, and no other parameters can limit the disk space that the temporary table|temporary file can use;