MySQL 5.7 How to play the temporary table space without falling into the pit

Guide reading

The goal of MySQL 5.7 is to become the most secure MySQL server since its release, and there are some important changes in SSL/TLS and overall security development.

MySQL 5.7 supports stand-alone temporary tablespace, but it may step into the pit at some time.

Since MySQL 5.7, independent temporary tablespaces have been adopted (which is not the same as independent undo tablespaces), ibtmp1 files have been named, 12M has been initialized, and there is no upper limit by default.

The option innodb_temp_data_file_path configures the temporary tablespace related parameters.

innodb_temp_data_file_path = ibtmp1:12M:autoextend

Some Notes on Temporary Tablespace

Temporary tablespaces do not support bare devices (raw device) like normal InnoDB tablespaces. The temporary tablespace uses the dynamic tablespace ID, so it changes every time it is restarted (the temporary tablespace file is reinitialized every time it is restarted). The mysqld instance also fails to start when a temporary tablespace cannot be created because of an option set incorrectly or for other reasons (insufficient permissions, etc.). This uncompressed InnoDB temporary table is stored in a temporary tablespace. If it is a compressed InnoDB temporary table, it needs to be stored separately in a separate tablespace file, and the file is stored in the tmpdir (/tmp) directory. Temporary table metadata is stored in the INFORMATION_SCHEMA. INNODB_TEMP_TABLE_INFO view.

Sometimes when executing SQL requests, temporary tables will be generated. In extreme cases, temporary tablespace files may skyrocket. In the cases handled by people, the highest rise is 300G, which is more fierce than the previous ibdata1 file skyrocket …

Suggestions on the use of temporary tables

Set the option of innodb_temp_data_file_path, and set the maximum upper limit of files. When the upper limit is exceeded, SQL that needs to generate temporary tables cannot be executed (generally, this SQL is also inefficient, so we can take this opportunity to optimize it). Check INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO to find the thread corresponding to the largest temporary table, and kill can be released, but ibtmp1 files cannot be released (unless restarted). Restart the instance at the right time and release the ibtmp1 file. Unlike ibdata1, ibtmp1 will be reinitialized when restarted, but ibdata1 cannot. Regularly check SQL that runs longer than N seconds (for example, N=300), and consider killing it to avoid the long-term operation of garbage SQL affecting business.

Attachment: Temporary table test case

Table DDL

CREATE TEMPORARY TABLE `tmp1` (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `name` varchar(50) NOT NULL DEFAULT '',
 `aid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `nid` int(11) unsigned GENERATED ALWAYS AS ((`id` + 1)) VIRTUAL NOT NULL,
 `nnid` int(11) unsigned GENERATED ALWAYS AS ((`id` + 1)) STORED NOT NULL,
 PRIMARY KEY (`aid`),
 KEY `name` (`name`),
 KEY `id` (`id`),
 KEY `nid` (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The size of the original table is only 120MB, from which data is directly derived from INSERT … SELECT to tmp1 table.

-rw-r----- 1 yejr imysql 120M Apr 14 10:52 /data/mysql/test/sid.ibd

Generate temporary tables (remove virtual columns, which are not supported by temporary tables, and then write data), and even bigger (I don't understand, I will have a chance to trace the reasons later).

-rw-r----- 1 yejr imysql 140M Jun 25 09:55 /Users/yejinrong/mydata/ibtmp1

View temporary table metadata information

yejr@imysql.com [test]>select * from 
 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*********************** 1. row ***********************
   TABLE_ID: 405
    NAME: #sql14032_300000005_3
    N_COLS: 6
    SPACE: 421
PER_TABLE_TABLESPACE: FALSE
  IS_COMPRESSED: FALSE

Then delete the index, and as a result, it is bigger again

-rw-r----- 1 yejr imysql 204M Jun 25 09:57 /data/mysql/ibtmp1

After the index was dropped in the second test, it became 200M (because in the second test, I set the temporary table maximum of 200M)

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:200M

-rw-r----- 1 yejr imysql 200M Jun 25 10:15 /data/mysql/ibtmp1

Execute a slow SQL that produces a temporary table.

Note: As of MySQL 5.7, executing UNION ALL no longer produces temporary tables (unless additional sorting is required).

yejr@imysql.com [test]>explain select * from tmp1 union 
 select id,name,aid from sid\G
*************************** 1. row ***************************
   id: 1
 select_type: PRIMARY
  table: tmp1
 partitions: NULL
   type: ALL
possible_keys: NULL
   key: NULL
  key_len: NULL
   ref: NULL
   rows: 3986232
  filtered: 100.00
  Extra: NULL
*************************** 2. row ***************************
   id: 2
 select_type: UNION
  table: sid
 partitions: NULL
   type: ALL
possible_keys: NULL
   key: NULL
  key_len: NULL
   ref: NULL
   rows: 802682
  filtered: 100.00
  Extra: NULL
*************************** 3. row ***************************
   id: NULL
 select_type: UNION RESULT
  table: <union1,2>
 partitions: NULL
   type: ALL
possible_keys: NULL
   key: NULL
  key_len: NULL
   ref: NULL
   rows: NULL
  filtered: NULL
  Extra: Using temporary

The file has risen to 588M. It's not over yet. I gave it directly to the card

-rw-r----- 1 yejr imysql 588M Jun 25 10:07 /data/mysql/ibtmp1

In the second test, the temporary tablespace file is set to a maximum of 200M, and then an error will be reported:

CREATE TEMPORARY TABLE `tmp1` (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `name` varchar(50) NOT NULL DEFAULT '',
 `aid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `nid` int(11) unsigned GENERATED ALWAYS AS ((`id` + 1)) VIRTUAL NOT NULL,
 `nnid` int(11) unsigned GENERATED ALWAYS AS ((`id` + 1)) STORED NOT NULL,
 PRIMARY KEY (`aid`),
 KEY `name` (`name`),
 KEY `id` (`id`),
 KEY `nid` (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

0

Summarize

Reference

[MySQL FAQ] Series--When will temporary tables be used

How does the FAQ family avoid the ibdata1 file size skyrocketing

https://dev.mysql.com/doc/refman/5.7/en/temporary-files.html

https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_temp_data_file_path

https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html