Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 38.0Mb
PDF (RPM) - 36.6Mb
HTML Download (TGZ) - 9.9Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.7Mb
Man Pages (TGZ) - 207.1Kb
Man Pages (Zip) - 315.4Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Temporary Tablespace

14.4.12 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.

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.15.7, “InnoDB INFORMATION_SCHEMA Temporary Table Info Table”.

Managing Temporary Tablespace Data File Size

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 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 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

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

Alternatively, you can 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, you can restart the MySQL server. Restarting the server removes and recreates the temporary tablespace data file according to the attributes defined by innodb_temp_data_file_path.

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:

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

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, you can configure the default_tmp_storage_engine and 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.

Temporary Table Undo Logs

Temporary table undo logs reside in the temporary tablespace and are used for temporary tables and related objects. Temporary table undo logs are not redo-logged, as they are not required for crash recovery. They are only used for rollback while the server is running. This special type of undo log benefits performance by avoiding redo logging I/O. 32 rollback segments are reserved for temporary table undo logs for transactions that modify temporary tables and related objects.


User Comments
Sign Up Login You must be logged in to post a comment.