As of MySQL 5.7.1, non-compressed
temporary tables are, by default, stored in a temporary
ibtmp1 that is located
in the MySQL
option can be used to specify a different file name and
tables are stored in their own independent tablespace files
.ibd files) in the path specified by t
TMPDIR environment variable.
On Unix, MySQL uses the value of the
environment variable as the path name of the directory in
which to store temporary files (with the exception of
InnoDB temporary tables, as
described above). If
TMPDIR is not set,
MySQL uses the system default, which is usually
On Windows, MySQL checks in order the values of the
TMP environment variables. For the first
one found to be set, MySQL uses it and does not check those
remaining. If none of
TMP are set,
MySQL uses the Windows system default, which is usually
If the file system containing your temporary file directory is
too small, you can use the
--tmpdir option to
mysqld to specify a directory in a file
system where you have enough space. On replication slaves, you
specify a separate directory for holding temporary files when
DATA INFILE statements.
--tmpdir option can be set
to a list of several paths that are used in round-robin
fashion. Paths should be separated by colon characters
:”) on Unix and semicolon
;”) on Windows.
To spread the load effectively, these paths should be located on different physical disks, not different partitions of the same disk.
If the MySQL server is acting as a replication slave, you
should be sure to set
--slave-load-tmpdir not to
point to a directory that is on a memory-based file system or
to a directory that is cleared when the server host restarts.
A replication slave needs some of its temporary files to
survive a machine restart so that it can replicate temporary
INFILE operations. If files in the slave temporary
file directory are lost when the server restarts, replication
MySQL arranges that temporary files are removed if mysqld is terminated. On platforms that support it (such as Unix), this is done by unlinking the file after opening it. The disadvantage of this is that the name does not appear in directory listings and you do not see a big temporary file that fills up the file system in which the temporary file directory is located. (In such cases, lsof +L1 may be helpful in identifying large files associated with mysqld.)
When sorting (
ORDER BY or
BY), MySQL normally uses one or two temporary files.
The maximum disk space required is determined by the following
(length of what is sorted + sizeof(row pointer)) * number of matched rows * 2
The row pointer size is usually four bytes, but may grow in the future for really big tables.
SELECT queries, MySQL
also creates temporary SQL tables. These are not hidden and
have names of the form
In most cases,
creates a temporary copy of the original table in the same
directory as the original table. However, if
ALTER TABLE uses the in-place
technique (online DDL),
temporary files in the temporary file directory. If this
directory is not large enough to hold such files, you may need
to set the
variable to a different directory. For more information about
online DDL, Section 14.11, “InnoDB and Online DDL”.