mysql temporary_Mysql：Temporary Tablespaces

Global Temporary Tablespace

The global temporary tablespace (ibtmp1) stores rollback segments for changes made to user-created temporary tables.

The innodb_temp_data_file_path variable defines the relative path, name, size, and attributes for global temporary tablespace data files. If no value is specified for innodb_temp_data_file_path, the default behavior is to create a single auto-extending data file named ibtmp1 in the innodb_data_home_dir directory. The initial file size is slightly larger than 12MB.

The global temporary tablespace is removed on normal shutdown or on an aborted initialization, and recreated each time the server is started. The global temporary tablespace receives a dynamically generated space ID when it is created. Startup is refused if the global temporary tablespace cannot be created. The global temporary tablespace is not removed if the server halts unexpectedly. In this case, a database administrator can remove the global temporary tablespace manually or restart the MySQL server. Restarting the MySQL server removes and recreates the global temporary tablespace automatically.

The global temporary tablespace cannot reside on a raw device.

INFORMATION_SCHEMA.FILES provides metadata about the global temporary tablespace. Issue a query similar to this one to view global temporary tablespace metadata:

mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G

By default, the global temporary tablespace data file is autoextending and increases in size as necessary.

To determine if a global temporary tablespace data file is autoextending, check the innodb_temp_data_file_path setting:

mysql> SELECT @@innodb_temp_data_file_path;

+------------------------------+

| @@innodb_temp_data_file_path |

+------------------------------+

| ibtmp1:12M:autoextend |

+------------------------------+

To check the size of global temporary tablespace data files, query the INFORMATION_SCHEMA.FILES table using a query similar to this one:

mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE

AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES

WHERE TABLESPACE_NAME = 'innodb_temporary'\G

*************************** 1. row ***************************

FILE_NAME: ./ibtmp1

TABLESPACE_NAME: innodb_temporary

ENGINE: InnoDB

INITIAL_SIZE: 12582912

TotalSizeBytes: 12582912

DATA_FREE: 6291456

MAXIMUM_SIZE: NULL

TotalSizeBytes shows the current size of the global temporary tablespace data file. For information about other field values, see Section 25.14, “The INFORMATION_SCHEMA FILES Table”.

Alternatively, check the global temporary tablespace data file size on your operating system. The global temporary tablespace data file is located in the directory defined by the innodb_temp_data_file_path variable.

To reclaim disk space occupied by a global temporary tablespace data file, restart the MySQL server. Restarting the server removes and recreates the global temporary tablespace data file according to the attributes defined by innodb_temp_data_file_path.

To limit the size of the global temporary tablespace data file, configure innodb_temp_data_file_path to specify a maximum file size. For example:

[mysqld]

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

Configuring innodb_temp_data_file_path requires restarting the server.