188.8.131.52 The Temporary Tablespace
Non-compressed, user-created temporary tables and on-disk internal temporary tables are created in a shared temporary tablespace. The
innodb_temp_data_file_path configuration option defines the relative path, name, size, and attributes for temporary tablespace data files. If no value is specified for
innodb_temp_data_file_path, the default behavior is to create an auto-extending data file named
ibtmp1 in the
innodb_data_home_dir directory that is slightly larger than 12MB.
In MySQL 5.6, non-compressed temporary tables are created in individual file-per-table tablespaces in the temporary file directory, or in the
InnoDB system tablespace in the data directory if
innodb_file_per_table is disabled. The introduction of a shared temporary tablespace in MySQL 5.7 removes performance costs associated with creating and removing a file-per-table tablespace for each temporary table. A dedicated temporary tablespace also means that it is no longer necessary to save temporary table metadata to the
InnoDB system tables.
Compressed temporary tables, which are temporary tables created using the
ROW_FORMAT=COMPRESSED attribute, are created in file-per-table tablespaces in the temporary file directory.
The temporary tablespace is removed on normal shutdown or on an aborted initialization, and is recreated each time the server is started. The temporary tablespace receives a dynamically generated space ID when it is created. Startup is refused if the temporary tablespace cannot be created. The temporary tablespace is not removed if the server halts unexpectedly. In this case, a database administrator can remove the temporary tablespace manually or restart the server, which removes and recreates the temporary tablespace automatically.
The temporary tablespace cannot reside on a raw device.
INFORMATION_SCHEMA.FILES provides metadata about the
InnoDB temporary tablespace. Issue a query similar to this one to view temporary tablespace metadata:
mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G
INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO provides metadata about user-created temporary tables that are currently active within an
InnoDB instance. For more information, see Section 14.16.7, “InnoDB INFORMATION_SCHEMA Temporary Table Info Table”.
By default, the temporary tablespace data file is autoextending and increases in size as necessary to accommodate on-disk temporary tables. For example, if an operation creates a temporary table that is 20MB in size, the temporary tablespace data file, which is 12MB in size by default when created, extends in size to accommodate it. When temporary tables are dropped, freed space can be reused for new temporary tables, but the data file remains at the extended size.
An autoextending temporary tablespace data file can become large in environments that use large temporary tables or that use temporary tables extensively. A large data file can also result from long running queries that use temporary tables.
To determine if a temporary tablespace data file is autoextending, check the
mysql> SELECT @@innodb_temp_data_file_path; +------------------------------+ | @@innodb_temp_data_file_path | +------------------------------+ | ibtmp1:12M:autoextend | +------------------------------+
To check the size of temporary tablespace data files, query the
INFORMATION_SCHEMA.FILES table using a query similar to this:
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 value reports the current size of the temporary tablespace data file. For information about other field values, see Section 24.9, “The INFORMATION_SCHEMA FILES Table”.
Alternatively, check the temporary tablespace data file size on your operating system. By default, the temporary tablespace data file is located in the directory defined by the
innodb_temp_data_file_path configuration option. If a value was not specified for this option explicitly, a temporary tablespace data file named
ibtmp1 is created in
innodb_data_home_dir, which defaults to the MySQL data directory if unspecified.
To reclaim disk space occupied by a temporary tablespace data file, restart the MySQL server. Restarting the server removes and recreates the temporary tablespace data file according to the attributes defined by
To prevent the temporary data file from becoming too large, you can configure the
innodb_temp_data_file_path option to specify a maximum file size. For example:
When the data file reaches the maximum size, queries fail with an error indicating that the table is full. Configuring
innodb_temp_data_file_path requires restarting the server.
Alternatively, configure the
internal_tmp_disk_storage_engine options, which define the storage engine to use for user-created and on-disk internal temporary tables, respectively. Both options are set to
InnoDB by default. The
MyISAM storage engine uses an individual file for each temporary table, which is removed when the temporary table is dropped.