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

A database found that the disk alarm had been 100%. After troubleshooting, it was found that there was a disk alarm in the data directory of the database.

What is ibtmp1? After looking at the official documents, I found that

The temporary tablespace is a tablespace for non-compressed InnoDB temporary tables and related objects. The configuration option, innodb_temp_data_file_path, defines a relative path for the temporary tablespace data file. If innodb_temp_data_file_path is not defined, a single auto-extending 12MB data file named ibtmp1 is created in the data directory. The temporary tablespace is recreated on each server start and receives a dynamically generated space ID, which helps avoid conflicts with existing space IDs. The temporary tablespace cannot reside on a raw device. Startup is refused if the temporary tablespace cannot be created.

The temporary tablespace is removed on normal shutdown or on an aborted initialization. The temporary tablespace is not removed when a crash occurs. In this case, the database administrator may remove the temporary tablespace manually or restart the server with the same configuration, which removes and recreates the temporary tablespace

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.

This parameter is a new feature of 5.7

Introduce a new "non-redo" undo log for temporary tables and related objects and store them in temporary tablespaces. This type of undo log is non-redolog because the temporary table does not need crash recovery and therefore does not need redo logs, but it requires undo log for rollback, MVCC, and so on. The default temporary tablespace file is ibtmp1, which is located in the data directory and is recreated each time the server starts. Temporary tablespaces can be specified through innodb_temp_data_file_path. (http://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-table-undo-logs.html)

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.