How to use MySQL 5.7 temporary tablespace to avoid pitfalls

Introduction

MySQL 5.7 aims to be the most secure MySQL server ever released, with some important changes in SSL/TLS and overall security development.

MySQL 5.7 and later supports independent temporary tablespaces, but sometimes you may run into problems.

Starting from MySQL 5.7, an independent temporary tablespace is used (which is not the same as an independent undo tablespace). The file is named ibtmp1, initialized to 12M, and has no upper limit by default.

The option innodb_temp_data_file_path configures temporary tablespace related parameters.

innodb_temp_data_file_path = ibtmp1:12M:autoextend

A few notes on temporary tablespace

• Temporary tablespaces do not support raw devices like normal InnoDB tablespaces.
• The temporary tablespace uses a dynamic tablespace ID, so it changes at each restart (the temporary tablespace file is reinitialized at each restart).
• When the option setting is incorrect or other reasons (such as insufficient permissions) prevent the temporary tablespace from being created, the mysqld instance cannot start either.
• The temporary tablespace stores non-compressed InnoDB temporary tables. If they are compressed InnoDB temporary tables, they need to be stored separately in their own tablespace files, which are stored in the tmpdir (/tmp) directory.
• Temporary table metadata is stored in the INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO view.

Sometimes temporary tables are generated when executing SQL requests. In extreme cases, this may cause a sudden increase in temporary table space files. In the cases I have helped people handle, the maximum increase was nearly 300G, which is even more severe than the sudden increase in ibdata1 files I have encountered before...

Some suggestions for using temporary tables

• Set the innodb_temp_data_file_path option to set the maximum file size. When the size exceeds the maximum, the SQL statements that need to generate temporary tables cannot be executed (generally, the efficiency of such SQL statements is also relatively low, so you can take this opportunity to optimize them).
• Check INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO, find the thread corresponding to the largest temporary table, and kill it to release it. However, the ibtmp1 file cannot be released (unless the system is restarted).
• Restart the instance at an appropriate time to release the ibtmp1 file. Unlike ibdata1, ibtmp1 will be reinitialized when restarted, while ibdata1 cannot.
• Regularly check SQL statements that have run for more than N seconds (for example, N=300) and consider killing them to prevent junk SQL statements from running for a long time and affecting the 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 original table size is only 120MB. Directly INSERT…SELECT from this table to import data into the tmp1 table.

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

Generate a temporary table (remove the virtual column, the temporary table does not support virtual columns, and then write data), and it is even bigger (I don’t understand it, I will find out the reason later).

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

View temporary table metadata information

[email protected] [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

Delete the index again, and it will be even bigger.

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

After deleting the index in the second test, it became 200M (because in the second test, I set the maximum temporary table size to 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 statement that will generate a temporary table.

Note: Starting from MySQL 5.7, executing UNION ALL no longer generates a temporary table (unless additional sorting is required).

[email protected] [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 size has increased to 588M and it is not finished yet. I am stuck directly

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

During the second test, the maximum size of the temporary tablespace file was set to 200M, and an error was reported when executing again:

[email protected] [test]>select * from tmp1 union 
 select id,name,aid from sid;
ERROR 1114 (HY000): The table '/var/folders/bv/j4tjn6k54dj5jh1tl8yn6_y00000gn/T/#sql14032_5_8' is full

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

refer to

[MySQL FAQ] Series - When to use temporary tables

FAQ series | How to prevent the ibdata1 file size from 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