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' or `/usr/tmp'. If the filesystem containing your temporary file directory is too small, you should edit mysqld_safe to set TMPDIR to point to a directory in a filesystem where you have enough space! You can also set the temporary directory using the --tmpdir option to mysqld.

MySQL creates all temporary files as hidden files. This ensures that the temporary files will be removed if mysqld is terminated. The disadvantage of using hidden files is that you will 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 needed is:

(length of what is sorted + sizeof(database pointer))
* number of matched rows
* 2

sizeof(database pointer) is usually 4, 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.

If you use MySQL 4.1 or later you can spread load between several physical disks by setting --tmpdir to a list of paths separated by colon : (semicolon ; on Windows). They will be used in round-robin fashion. Note: These paths should end up on different physical disks, not different partitions of the same disk.

User Comments

Suggestion:
To speed up MySQL's table joins, I would like MySQL to allow a cascading list of directories
for its temporary files. For Windows it would look like "x:\ramtemp;d:\temp2;e:\tempx".
If space from the first drive is consumed, additional space is used from the next directory
etc.. Why go to all this trouble? The first drive could be a RAM disk which will run at least
10x faster than the fastest hard drive. User's can easily allocate 100MB ram drives which
handles most of the temporary tables. This will speed up table joins tremendously! Sorting
on non-indexed columns would also be much faster. When space runs out on this ram
drive, it overflows to the hard disk.

Add your own comment.