Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the temporary table space of MySQL 5.7. the editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Guide reading

MySQL 5.7aims to be the most secure MySQL server since its release, with some important changes in SSL/TLS and comprehensive security development.

MySQL has supported independent temporary tablespaces since 5. 7, but it may be cheated on a few occasions.

Since MySQL 5.7, we have adopted independent temporary tablespaces (it is not the same thing as independent undo tablespaces), named ibtmp1 files, initialized 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

Some explanations of temporary tablespace

Unlike regular InnoDB tablespaces, temporary tablespaces do not support bare devices (raw device).

The temporary tablespace uses a dynamic tablespace ID, so it changes every time you restart (the temporary tablespace file is reinitialized each time you restart).

The mysqld instance cannot be started when the option is set incorrectly or for other reasons (insufficient permissions, etc.) to create a temporary tablespace.

The uncompressed InnoDB temporary table is stored in the temporary table space. if it is a compressed InnoDB temporary table, it needs to be stored separately in its own tablespace file, which is 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 SQL requests are executed, which in extreme cases can lead to a surge in temporary tablespace files, up to 300 gigabytes faster in cases handled by others, which is even faster than the surge in ibdata1 files encountered before.

Some suggestions on the use of temporary watch

Set the innodb_temp_data_file_path option and set the maximum limit for files. When the upper limit is exceeded, the SQL that needs to generate temporary tables cannot be executed (generally, this kind of SQL is also relatively inefficient, so you can take this opportunity to optimize it).

Check the INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO, find the thread corresponding to the largest temporary table, and the kill can be released, but the ibtmp1 file cannot be released (unless restarted).

Choose a machine to restart the instance and release the ibtmp1 file. Unlike ibdata1, ibtmp1 will be reinitialized when it restarts, but ibdata1 cannot.

Regularly check the SQL that has been running for more than N seconds (for example, Niss300) and consider killing it to avoid junk SQL running for a long time to affect your business.

Attached: temporary table test case

Table DDL

CREATE TEMPORARY TABLE `tmp1` (`id` int (10) unsigned NOT NULL DEFAULT '0mm, `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, INSERT directly from this table. SELECT imports data to the tmp1 table.

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

Generate temporary tables (remove virtual columns, temporary tables don't support virtual columns, and then write data), and even bigger (I don't understand, I'll have a chance to trace the cause 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\ gateway * 1. Row * * TABLE_ID: 405 NAME: # sql14032_300000005_3 N_COLS: 6 SPACE: 421PER_TABLE_TABLESPACE: FALSE IS_COMPRESSED: FALSE

And then delete the index, and as a result, it's bigger.

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

After the second test deleted the index, it became 200m (because in the second test, I set a maximum of 200m for the temporary table)

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 temporary tables.

Note: since MySQL 5.7, executing UNION ALL will no longer generate temporary tables (unless additional sorting is required).

Yejr@imysql.com [test] > explain select * from tmp1 union select id,name Aid from sid\ gateway * 1. Row * * id: 1 select_type: PRIMARY table: tmp1 partitions: NULL type: ALLpossible_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: ALLpossible_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: partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Using temporary

The file goes up to 588m and it's not over yet. I just gave it to the card.

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

In the second test, a maximum of 200m of temporary tablespace files is set, and an error will be reported when executed:

Yejr@imysql.com [test] > select * from tmp1 union select id,name,aid from sid ERROR 1114 (HY000): The table'/ var/folders/bv/j4tjn6k54dj5jh2tl8yn6_y00000gn/T/#sql14032_5_8' is full on the use of MySQL 5.7 temporary tablespaces, this article ends here. I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.