https://www.percona.com/blog/wp-content/uploads/2021/11/Temporary-Tables-MySQL-300×157.png
If you ever had to deal with performance and/or disk space issues related to temporary tables, I bet you eventually found yourself puzzled. There are many possible scenarios depending on the type of temporary table, settings, and MySQL version used. We have observed a pretty long evolution in that matter due to a couple of reasons. One of them was the need to completely eliminate the need to use the deprecated MyISAM engine, and at the same time introduce more performant and reliable alternatives. Another set of improvements was required related to InnoDB, where it was necessary to lower the overhead of temporary tables using that engine.
For that reason, I decided to gather them in a sort of summary which may help to troubleshoot their usage. Due to vast changes between major MySQL releases, I divided the article by them.
MySQL 5.6
(If you are still using that version, you are encouraged to consider upgrading it soon as it has reached EOL.)
User-Created Temporary Tables
When a table is created using CREATE TEMPORARY TABLE clause, it will use the engine defined by default_tmp_storage_engine (defaults to InnoDB) if not explicitly defined otherwise and will be stored inside the directory defined by the tmpdir variable.
An example one may look like this:
mysql > create temporary table tmp1 (id int, a varchar(10)); Query OK, 0 rows affected (0.02 sec) mysql > show create table tmp1\G *************************** 1. row *************************** Table: tmp1 Create Table: CREATE TEMPORARY TABLE `tmp1` ( `id` int(11) DEFAULT NULL, `a` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
But how do you find the file created on disk that stores this table data? While this query may help:
mysql > select table_id,space,name,path from information_schema.INNODB_SYS_DATAFILES join information_schema.INNODB_SYS_TABLES using (space) where name like '%tmp%'\G *************************** 1. row *************************** table_id: 21 space: 7 name: tmp/#sql11765a_2_1 path: /data/sandboxes/msb_5_6_51/tmp/#sql11765a_2_1.ibd 1 row in set (0.00 sec)
We don’t see the original table name here. Even by looking at the buffer pool, we still don’t have the real name:
mysql > select TABLE_NAME from information_schema.INNODB_BUFFER_PAGE where table_name like '%tmp%'; +-------------------------+ | TABLE_NAME | +-------------------------+ | `tmp`.`#sql11765a_2_1` | +-------------------------+ 1 row in set (0.07 sec)
Here comes the extension available in the Percona Server for MySQL 5.6 variant – additional information_schema table: GLOBAL_TEMPORARY_TABLES. With that one, we can craft a query that provides a bit more information:
mysql > select SPACE,TABLE_SCHEMA,TABLE_NAME,ENGINE,g.NAME,PATH from information_schema.GLOBAL_TEMPORARY_TABLES g LEFT JOIN information_schema.INNODB_SYS_TABLES s ON s.NAME LIKE CONCAT('%', g.name, '%') LEFT JOIN information_schema.INNODB_SYS_DATAFILES USING(SPACE)\G
*************************** 1. row ***************************
SPACE: 16
TABLE_SCHEMA: test
TABLE_NAME: tmp1
ENGINE: InnoDB
NAME: #sql12c75d_2_0
PATH: /data/sandboxes/msb_ps5_6_47/tmp/#sql12c75d_2_0.ibd
*************************** 2. row ***************************
SPACE: NULL
TABLE_SCHEMA: test
TABLE_NAME: tmp3
ENGINE: MEMORY
NAME: #sql12c75d_2_2
PATH: NULL
*************************** 3. row ***************************
SPACE: NULL
TABLE_SCHEMA: test
TABLE_NAME: tmp2
ENGINE: MyISAM
NAME: #sql12c75d_2_1
PATH: NULL
3 rows in set (0.00 sec)
So at least for the InnoDB temp table, we can correlate the exact table name with the file path.
Internal Temporary Tables
These are ones created by MySQL in the process of executing a query. We don’t have any access to such tables, but let’s see how we can investigate their usage.
This type is created in memory (using MEMORY engine) as long as its size doesn’t exceed either tmp_table_size or max_heap_table_size variables, and if no TEXT/BLOB columns are in use. If such a table has to be stored on disk though, in MySQL 5.6 it will use MyISAM storage and also tmpdir used as a location. Quick example, on 10M rows sysbench table, query producing big internal temporary table:
mysql > SELECT pad, COUNT(*) FROM sbtest1 GROUP BY pad;
And we can see the related files growing:
$ ls -lh /data/sandboxes/msb_5_6_51/tmp/ total 808M -rw-rw---- 1 przemek przemek 329M Sep 29 23:24 '#sql_11765a_0.MYD' -rw-rw---- 1 przemek przemek 479M Sep 29 23:24 '#sql_11765a_0.MYI'
It may be difficult to correlate a particular temp table and its client connection though. The only information I found is:
mysql > select FILE_NAME,EVENT_NAME from performance_schema.file_summary_by_instance where file_name like '%tmp%' \G *************************** 1. row *************************** FILE_NAME: /data/sandboxes/msb_5_6_51/tmp/Innodb Merge Temp File EVENT_NAME: wait/io/file/innodb/innodb_temp_file *************************** 2. row *************************** FILE_NAME: /data/sandboxes/msb_5_6_51/tmp/#sql_11765a_0.MYI EVENT_NAME: wait/io/file/myisam/kfile *************************** 3. row *************************** FILE_NAME: /data/sandboxes/msb_5_6_51/tmp/#sql_11765a_0.MYD EVENT_NAME: wait/io/file/myisam/dfile 3 rows in set (0.00 sec)
MySQL 5.7
User-Created Temporary Tables
As earlier, the default_tmp_storage_engine variable decides on the engine used. But two changes happened here. InnoDB temporary tables now use a common dedicated shared tablespace – ibtmp1, unless it is compressed. Moreover, we have an additional information_schema view: INNODB_TEMP_TABLE_INFO. Given that, we can get information like below:
mysql > select name, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, SPACE, PER_TABLE_TABLESPACE, IS_COMPRESSED from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO join INFORMATION_SCHEMA.FILES on FILE_ID=SPACE\G *************************** 1. row *************************** name: #sql12cf58_2_5 FILE_NAME: ./ibtmp1 FILE_TYPE: TEMPORARY TABLESPACE_NAME: innodb_temporary SPACE: 109 PER_TABLE_TABLESPACE: FALSE IS_COMPRESSED: FALSE *************************** 2. row *************************** name: #sql12cf58_2_4 FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.ibd FILE_TYPE: TEMPORARY TABLESPACE_NAME: innodb_file_per_table_110 SPACE: 110 PER_TABLE_TABLESPACE: TRUE IS_COMPRESSED: TRUE 2 rows in set (0.01 sec)
But again to correlate with a table name, the Percona Server for MySQL extension needs to be used:
mysql > select g.TABLE_SCHEMA, g.TABLE_NAME, name, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, SPACE, PER_TABLE_TABLESPACE, IS_COMPRESSED from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO join INFORMATION_SCHEMA.FILES on FILE_ID=SPACE join information_schema.GLOBAL_TEMPORARY_TABLES g using (name)\G *************************** 1. row *************************** TABLE_SCHEMA: test TABLE_NAME: tmp1 name: #sql12cf58_2_5 FILE_NAME: ./ibtmp1 FILE_TYPE: TEMPORARY TABLESPACE_NAME: innodb_temporary SPACE: 109 PER_TABLE_TABLESPACE: FALSE IS_COMPRESSED: FALSE *************************** 2. row *************************** TABLE_SCHEMA: test TABLE_NAME: tmp3 name: #sql12cf58_2_4 FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.ibd FILE_TYPE: TEMPORARY TABLESPACE_NAME: innodb_file_per_table_110 SPACE: 110 PER_TABLE_TABLESPACE: TRUE IS_COMPRESSED: TRUE 2 rows in set (0.01 sec)
Alternatively, to see also MyISAM and related .frm files, we can use:
mysql > SELECT g.TABLE_SCHEMA, g.TABLE_NAME, NAME, f.FILE_NAME, g.ENGINE, TABLESPACE_NAME, PER_TABLE_TABLESPACE, SPACE FROM information_schema.GLOBAL_TEMPORARY_TABLES g join performance_schema.file_instances f ON FILE_NAME LIKE CONCAT('%', g.name, '%') left join INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO using (name) left join INFORMATION_SCHEMA.FILES fl on space=FILE_ID order by table_name\G
*************************** 1. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp1
NAME: #sql12cf58_2_5
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_5.frm
ENGINE: InnoDB
TABLESPACE_NAME: innodb_temporary
PER_TABLE_TABLESPACE: FALSE
SPACE: 109
*************************** 2. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp2
NAME: #sql12cf58_2_6
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_6.MYD
ENGINE: MyISAM
TABLESPACE_NAME: NULL
PER_TABLE_TABLESPACE: NULL
SPACE: NULL
*************************** 3. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp2
NAME: #sql12cf58_2_6
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_6.MYI
ENGINE: MyISAM
TABLESPACE_NAME: NULL
PER_TABLE_TABLESPACE: NULL
SPACE: NULL
*************************** 4. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp2
NAME: #sql12cf58_2_6
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_6.frm
ENGINE: MyISAM
TABLESPACE_NAME: NULL
PER_TABLE_TABLESPACE: NULL
SPACE: NULL
*************************** 5. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp3
NAME: #sql12cf58_2_4
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.frm
ENGINE: InnoDB
TABLESPACE_NAME: innodb_file_per_table_110
PER_TABLE_TABLESPACE: TRUE
SPACE: 110
*************************** 6. row ***************************
TABLE_SCHEMA: test
TABLE_NAME: tmp3
NAME: #sql12cf58_2_4
FILE_NAME: /data/sandboxes/msb_ps5_7_33/tmp/#sql12cf58_2_4.ibd
ENGINE: InnoDB
TABLESPACE_NAME: innodb_file_per_table_110
PER_TABLE_TABLESPACE: TRUE
SPACE: 110
6 rows in set (0.01 sec)
Internal Temporary Tables
For internal temporary tables in 5.7, it is similar in terms of in-memory ones. But the default engine for on-disk temp tables is defined via a new variable: internal_tmp_disk_storage_engine, which now defaults also to InnoDB, and also the ibtmp1 tablespace is used to store its contents.
Insight into this shared temporary tablespace is pretty limited. We can check its size and how much free space is currently available. An example view was taken during heavy query ongoing:
mysql > select FILE_NAME, FILE_TYPE, TABLESPACE_NAME, ENGINE, TOTAL_EXTENTS, FREE_EXTENTS, EXTENT_SIZE/1024/1024 as 'extent in MB', MAXIMUM_SIZE from INFORMATION_SCHEMA.FILES where file_name like '%ibtmp%'\G *************************** 1. row *************************** FILE_NAME: ./ibtmp1 FILE_TYPE: TEMPORARY TABLESPACE_NAME: innodb_temporary ENGINE: InnoDB TOTAL_EXTENTS: 588 FREE_EXTENTS: 1 extent in MB: 1.00000000 MAXIMUM_SIZE: NULL 1 row in set (0.00 sec)
And after the query is finished we can see most of the space is freed (FREE_EXTENTS):
mysql > select FILE_NAME, FILE_TYPE, TABLESPACE_NAME, ENGINE, TOTAL_EXTENTS, FREE_EXTENTS, EXTENT_SIZE/1024/1024 as 'extent in MB', MAXIMUM_SIZE from INFORMATION_SCHEMA.FILES where file_name like '%ibtmp%'\G *************************** 1. row *************************** FILE_NAME: ./ibtmp1 FILE_TYPE: TEMPORARY TABLESPACE_NAME: innodb_temporary ENGINE: InnoDB TOTAL_EXTENTS: 780 FREE_EXTENTS: 764 extent in MB: 1.00000000 MAXIMUM_SIZE: NULL 1 row in set (0.00 sec)
However, the tablespace won’t be truncated unless MySQL is restarted:
$ ls -lh msb_5_7_35/data/ibtmp* -rw-r----- 1 przemek przemek 780M Sep 30 19:50 msb_5_7_35/data/ibtmp1
To see the writing activity (which may turn out to be much higher for a single query than total size growth made by it):
mysql > select FILE_NAME, SUM_NUMBER_OF_BYTES_WRITE/1024/1024/1024 as GB_written from performance_schema.file_summary_by_instance where file_name like '%ibtmp%' \G *************************** 1. row *************************** FILE_NAME: /data/sandboxes/msb_5_7_35/data/ibtmp1 GB_written: 46.925933837891 1 row in set (0.00 sec)
MySQL 8.0
For simplicity, let’s skip how things worked before 8.0.16 and discuss only how it works since then, as the changes in that matter are quite significant:
- internal_tmp_disk_storage_engine variable was removed and it is no longer possible to use the MyISAM engine for internal temporary tables
- shared ibtmp1 table space is no longer used for either temporary table type
- a pool of new Session Temporary Tablespaces was introduced to handle both user and internal temporary tables on disk and is located by default in the main data directory
- the new TempTable engine for in-memory tables uses both the space in memory as well as mmapped files on disk
User-Created Temporary Tables
For an example temporary table:
mysql > create temporary table tmp1 (id int, a varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql > select * from information_schema.INNODB_TEMP_TABLE_INFO; +----------+----------------+--------+------------+ | TABLE_ID | NAME | N_COLS | SPACE | +----------+----------------+--------+------------+ | 1089 | #sqlbbeb3_a_12 | 5 | 4243767289 | +----------+----------------+--------+------------+ 1 row in set (0.00 sec)
We can correlate which file was used from that pool by looking at the space number:
mysql > select * from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ; +----+------------+----------------------------+-------+----------+-----------+ | ID | SPACE | PATH | SIZE | STATE | PURPOSE | +----+------------+----------------------------+-------+----------+-----------+ | 10 | 4243767290 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE | INTRINSIC | | 10 | 4243767289 | ./#innodb_temp/temp_9.ibt | 98304 | ACTIVE | USER | | 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE | | 0 | 4243767288 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE | +----+------------+----------------------------+-------+----------+-----------+ 10 rows in set (0.00 sec)
But again, no way to look for the table name. Fortunately, Percona Server for MySQL still has the GLOBAL_TEMPORARY_TABLES table, so given the three available system views, we can get better information on user-created temporary tables using various engines, like below:
mysql > SELECT SESSION_ID, SPACE, PATH, TABLE_SCHEMA, TABLE_NAME, SIZE, DATA_LENGTH, INDEX_LENGTH, ENGINE, PURPOSE FROM information_schema.GLOBAL_TEMPORARY_TABLES LEFT JOIN information_schema.INNODB_TEMP_TABLE_INFO USING(NAME) LEFT JOIN INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES USING(SPACE)\G *************************** 1. row *************************** SESSION_ID: 10 SPACE: 4243767290 PATH: ./#innodb_temp/temp_10.ibt TABLE_SCHEMA: test TABLE_NAME: tmp3 SIZE: 98304 DATA_LENGTH: 16384 INDEX_LENGTH: 0 ENGINE: InnoDB PURPOSE: USER *************************** 2. row *************************** SESSION_ID: 13 SPACE: NULL PATH: NULL TABLE_SCHEMA: test TABLE_NAME: tmp41 SIZE: NULL DATA_LENGTH: 24 INDEX_LENGTH: 1024 ENGINE: MyISAM PURPOSE: NULL *************************** 3. row *************************** SESSION_ID: 13 SPACE: NULL PATH: NULL TABLE_SCHEMA: test TABLE_NAME: tmp40 SIZE: NULL DATA_LENGTH: 128256 INDEX_LENGTH: 0 ENGINE: MEMORY PURPOSE: NULL *************************** 4. row *************************** SESSION_ID: 13 SPACE: 4243767287 PATH: ./#innodb_temp/temp_7.ibt TABLE_SCHEMA: test TABLE_NAME: tmp33 SIZE: 98304 DATA_LENGTH: 16384 INDEX_LENGTH: 0 ENGINE: InnoDB PURPOSE: USER 4 rows in set (0.01 sec)
Similar to ibtmp1, these tablespaces are not truncated apart from MySQL restart.
From the above, we can see that user connection 10 has one open InnoDB temporary table, and connection 13 has three temporary tables using three different engines.
Internal Temporary Tables
While a heavy query is running in connection 10, we can get the following views:
mysql > show processlist\G ... *************************** 2. row *************************** Id: 10 User: msandbox Host: localhost db: test Command: Query Time: 108 State: converting HEAP to ondisk Info: SELECT pad, COUNT(*) FROM sbtest1 GROUP BY pad mysql > select * from performance_schema.memory_summary_global_by_event_name where EVENT_NAME like '%temptable%'\G *************************** 1. row *************************** EVENT_NAME: memory/temptable/physical_disk COUNT_ALLOC: 2 COUNT_FREE: 0 SUM_NUMBER_OF_BYTES_ALLOC: 1073741824 SUM_NUMBER_OF_BYTES_FREE: 0 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 2 HIGH_COUNT_USED: 2 LOW_NUMBER_OF_BYTES_USED: 0 CURRENT_NUMBER_OF_BYTES_USED: 1073741824 HIGH_NUMBER_OF_BYTES_USED: 1073741824 *************************** 2. row *************************** EVENT_NAME: memory/temptable/physical_ram COUNT_ALLOC: 12 COUNT_FREE: 1 SUM_NUMBER_OF_BYTES_ALLOC: 1074790400 SUM_NUMBER_OF_BYTES_FREE: 1048576 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 11 HIGH_COUNT_USED: 11 LOW_NUMBER_OF_BYTES_USED: 0 CURRENT_NUMBER_OF_BYTES_USED: 1073741824 HIGH_NUMBER_OF_BYTES_USED: 1073741824 2 rows in set (0.00 sec) mysql > select * from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES where id=10\G *************************** 1. row *************************** ID: 10 SPACE: 4243767290 PATH: ./#innodb_temp/temp_10.ibt SIZE: 2399141888 STATE: ACTIVE PURPOSE: INTRINSIC *************************** 2. row *************************** ID: 10 SPACE: 4243767289 PATH: ./#innodb_temp/temp_9.ibt SIZE: 98304 STATE: ACTIVE PURPOSE: USER 2 rows in set (0.00 sec)
From the above, we can see the query created a huge temporary table, that first exceeded temptable_max_ram variable and continued to grow in a mmapped file (still TempTable engine), but as also temptable_max_mmap was reached, the table had to be converted to on-disk InnoDB intrinsic table. The same pool of temporary InnoDB tables is used in this case, but we can see the purpose information, depending if the table is external (user-created) or internal.
The mmapped file is not visible in the file system as it has deleted state, but can be watched with lsof:
mysqld 862655 przemek 52u REG 253,3 133644288 52764900 /data/sandboxes/msb_ps8_0_23/tmp/mysql_temptable.8YIGV8 (deleted)
It is important to know here, that as long as mmapped space has not exceeded, the Created_tmp_disk_tables counter is not incremented even though a file is created on disk here.
Also, in Percona Server for MySQL, the extended slow log, the size of temporary tables when the TempTable engine is used, is not accounted for: https://jira.percona.com/browse/PS-5168 – it shows “Tmp_table_sizes: 0”.
In some use cases, there are problems reported with the TempTable. It is possible to switch back to the old Memory engine via the internal_tmp_mem_storage_engine variable if needed.
References
- https://www.percona.com/blog/2017/12/04/internal-temporary-tables-mysql-5-7/
- https://dev.mysql.com/worklog/task/?id=7682
- https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-temp-table-info.html
- https://bugs.mysql.com/bug.php?id=96236
- https://www.percona.com/doc/percona-server/8.0/diagnostics/slow_extended.html#other-information
- https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
Complete the 2021 Percona Open Source Data Management Software Survey
Planet MySQL