A.4.4. Where MySQL Stores Temporary Files
MySQL uses the value of the TMPDIR
environment variable as the pathname of the directory in which
to store temporary files. If you don't have
TMPDIR set, MySQL uses the system default,
which is normally /tmp,
/var/tmp, or /usr/tmp.
If the filesystem containing your temporary file directory is
too small, you can use the --tmpdir option to
mysqld to specify a directory in a filesystem
where you have enough space.
In MySQL 5.1, the --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 characters (‘;’) on
Windows, NetWare, and OS/2.
Note: 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
not set --tmpdir to point to a directory on a
memory-based filesystem 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 tables or LOAD DATA
INFILE operations. If files in the temporary file
directory are lost when the server restarts, replication fails.
MySQL creates all temporary files as hidden files. This ensures
that the temporary files are removed if
mysqld is terminated. The disadvantage of
using hidden files is that you do not see a big temporary file
that fills up the filesystem in which the temporary file
directory is located.
When sorting (ORDER BY or GROUP
BY), MySQL normally uses one or two temporary files.
The maximum disk space required is determined by the following
expression:
(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.
For some SELECT queries, MySQL also creates
temporary SQL tables. These are not hidden and have names of the
form SQL_*.
ALTER TABLE creates a temporary table in the
same directory as the original table.
