(mysql.info) temporary-files
Info Catalog
(mysql.info) full-disk
(mysql.info) administration-issues
(mysql.info) problems-with-mysql-sock
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.0, 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. * 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.
Info Catalog
(mysql.info) full-disk
(mysql.info) administration-issues
(mysql.info) problems-with-mysql-sock
automatically generated byinfo2html