Shulou(Shulou.com)06/01 Report--

Received an alarm that the disk space of a MySQL5.7 server is insufficient. When the amount of data of the service is 20g, what causes the insufficient disk space?

After investigation, it is found that there is an ibtmp1 file under the data directory that is very large, with 949G.

# du-sh ibtmp1 949G ibtmp1

What is ibtmp1? After looking at the official documentation, it is found that this is a separate tablespace for uncompressed innodb temporary tables. The path, file name and size of the file are specified through the innodb_temp_data_file_path parameter. 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

2, set the innodb_fast_shutdown parameter

SET GLOBAL innodb_fast_shutdown = 0; # InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down

3. Shut down the mysql service

4. Delete the ibtmp1 file

5. Start 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

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.