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.
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.
3 comments:
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.
Greg
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.
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.
Cheers,
Dennis van Zuijlekom
Post a Comment