Non-compressed, user-created temporary tables and on-disk internal
temporary tables are created in a shared temporary tablespace. The
variable defines the relative path, name, size, and attributes for
temporary tablespace data files. If no value is specified for
default behavior is to create an auto-extending data file named
ibtmp1 in the
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
system tablespace in the data directory if
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
InnoDB system tables.
Compressed temporary tables, which are temporary tables created
ROW_FORMAT=COMPRESSED attribute, are
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.
The Information Schema
provides metadata about the
tablespace. Issue a query similar to this one to view temporary
mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G
The Information Schema
INNODB_TEMP_TABLE_INFO table provides
metadata about user-created temporary tables that are currently
active within an
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
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.3.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
configuration option. If a value was not specified for this
option explicitly, a temporary tablespace data file named
ibtmp1 is created in
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
variable 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
requires restarting the server.
Alternatively, configure the
variables, which define the storage engine to use for
user-created and on-disk internal temporary tables,
respectively. Both variables 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.