Mysql disk space explodes

After going to work today, I suddenly found that the disk space of mysql from the library was suddenly full. After some inspection, I found that ibtmp1 took up a lot of space, which was almost 300g. According to the query, ibtmp1 file is a new feature of MySQL5.7. MySQL5.7 uses an independent temporary table space to store temporary table data, but it cannot be a compressed table. Temporary tablespaces are created when the instance is started and deleted when shutdown. This provides a separate tablespace for all uncompressed innodb temporary tables, and the default temporary tablespace file is ibtmp1, located in the data directory. We can specify the path and size of the temporary tablespace through the innodb_temp_data_file_path parameter, which defaults to 12m. Only by restarting the instance can you reclaim the size of the temporary tablespace file ibtmp1. Create temporary table and using temporary table will share this temporary tablespace. The default configuration is ibtmp1:12M:autoextend, which means that the file size can be increased indefinitely on systems that support large files.

Solution:

1. Modify the my.cnf configuration file:

Innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

two。 Restart the mysql service.

Note: to avoid similar situations in the future, be sure to limit the maximum value of temporary tablespaces, such as innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

When will mysql create a temporary table? refer to https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html.