Thursday, May 21, 2009

Setting the MySQL Temporary Directory

One of the common mistakes made by new MySQL DBAs is forgetting to consider the MySQL temporary directory. If an internal heap table exceeds the size of the MySQL parameter TMP_TABLE_SIZE, MySQL will create a temporary MyISAM file on the disk to buffer the data.

MySQL will use the TMPDIR environmental variable to set the default location for storing temporary files. If this environmental variable is not set, the OS default will be used (i.e. /tmp, /usr/tmp or /var/tmp, etc.).

The mysqld --tmpdir can be set to multiple paths used in a rotating method. Paths are separated by colon characters (Unix) or semi-colons (Windows).

Remember that temporary files are created as hidden files. This makes sure that temporary files are deleted if mysqld is terminated.

Make sure to NOT set the TMPDIR path to the MySQL data directory filesystem.


Anonymous said...

Make sure to NOT set the TMPDIR path to the MySQL data directory filesystem.Does this advice apply to RAID setups?
If so, why? I would think that data is being written to all disks by all file systems so the placement of tmpdir, or any other dir, shouldn't matter.


Unknown said...

Hi Greg, Its not just an issue of balancing the I/O. Its also about making sure if a lot of temporary space is used that it cannot fill up the file system where the data resides.

Anonymous said...

Why not set the MySQL tmpdir to /dev/shm on Linux systems? This is a 'partition' in RAM space and could speed up I/O considerably.

Do note that the size of these partitions is limited to about half the size of total installed RAM memory. On properly scaled database servers this should normally not be a problem.

Dennis van Zuijlekom