Background

In MySQL 5.7.1 and later versions, temporary table storage address changes from previous temporary files to independent temporary tablespace named "ibtmp1". You can configure related parameters of temporary tablespace using the "innodb_temp_data_file_path" parameter. MySQL separates temporary tablespace from system tablespace files. You can use this shared temporary tablespace for storing data, such as non-compressed InnoDB temporary tables, related objects, and rollback segments.

Failure phenomenon

One customer's MySQL 5.7 RDS instance disk usage rate sending alarm: The HOME remaining disk space is insufficient, and the disk usage rate is up to 95%.

Analysis of cause

• Finding that under the user's data directory, the proportion of master databases on disk is 95%, but the proportion of backup database is 65%, so we judge that there are additional data on the master database that occupy the disk;
• Further troubleshooting: under the master database's data directory, view the file directory size using "du -sh ./*" command, finding that "ibtmp1" temporary tablespace hits 167G;
• Through the troubleshooting, we find that there are "1000+s" Cartesian product queries amid user connections, leading to a large number of temporary data in the temporary tablespace.

Solutions

• With the approval of the user, we kill the query statements, and prevent the query statements from further pushing up "ibtmp1" size or the full disk will bring about service unavailability.
• Because the temporary tablespace cannot shrink after being expanded, the user needs to restart the RDS instance to release the temporary tablespace.

Case review

Test SQL statement:

[baidu_dba] mysql>select count(*) from tb_1 order by id,c,pad,k;

Parameter setting:

[baidu_dba]mysql>show variables like 'innodb_temp_data_file_path';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.01 sec)

Temporary tablespace in initialization default: 12M：

After executing relatively large queries, store the generated temporary tables in the temporary tablespace, and increase the tablespace:

In the RDS instance, if failed to set the largest temporary tablespace size for the "innodb_data_file_path" parameter by default, it may result in oversized temporary tablespace and full occupation of the disk.

Conclusions & suggestions

1. As for the MySQL5.7 query statement, check if the query method is reasonable (avoiding a constant increase of temporary tables due to Cartesian product). If you use statements like "order by" in a query, and then use the "temporary tablespace sorting", you need to control the size of the result set. Thus, it avoids oversized temporary tablespace due to overly large result set in temporary.
2. Conclusions on a temporary table

• To MySQL 5.6

When the parameter "innodb_file_per_table" is enabled, the temporary table produces temporary table data file and table structure file in the directory designated by tmpdir. So, you can store the metadata in "ibdata1". When the parameter "innodb_file_per_table" gets disabled, temporary table produces temporary table data in "ibdata1", and the size of "ibdata1" will not shrink after the session gets closed.

• To MySQL 5.7

When the temporary table is a non-compressed table, you can store the temporary table data in "ibtmp1" temporary tablespace, and save the table structure data under "tmpdir". When the temporary table is a compressed table, you can save the temporary table data and structure under the "tmpdir".