Analysis on hidden space in MySQL

MySQL uses the value of the environmental variable TMPDIR as the path name of the directory for saving the temporary file. If TMPDIR is not set, MySQL uses the default value of the system, usually tmp, vartmp, or usrtmp. If the file system that contains the temporary file directory is too small, you can use the "-tmpdir" option for mysqld to specify a directory in the file system with sufficient space

MySQL uses the value of the environmental variable TMPDIR as the path name of the directory for saving the temporary file. If TMPDIR is not set, MySQL uses the default value of the system, usually/tmp,/var/tmp, or/usr/tmp. If the file system that contains the temporary file directory is too small, you can use the "-tmpdir" option for mysqld to specify a directory in the file system with sufficient space

MySQL uses the value of the environmental variable TMPDIR as the path name of the directory for saving the temporary file. If TMPDIR is not set, MySQL uses the default value of the system, usually/tmp,/var/tmp, or/usr/tmp. If the file system that contains the temporary file directory is too small, you can use the "-tmpdir" option for mysqld to specify a directory in the file system with sufficient space.

In MySQL 5.1, the "-tmpdir" option can be set to a list of several paths and used cyclically. On Unix platforms, the path is separated by the colon (:). On Windows, NetWare, and OS/2 platforms, the path is separated by the semicolon. Note: To effectively distribute loads, these paths should be located on different physical disks, rather than in different partitions of the same disk.

If the MySQL server is being used as a replication slave server, you should not set "-- tmpdir" to the directory of the memory-based file system, or the directory that will be cleared when the server host restarts. For replication from the server, some temporary files need to be retained when the machine restarts, so that you can copy the temporary table or perform the load data infile operation. If files in the temporary file directory are lost when the server is restarted, the replication will fail.

MySQL creates all temporary files implicitly. This ensures that all temporary files are deleted when mysqld is aborted. The disadvantage of using an implicit file is that you cannot see a large temporary file that occupies the file system in the location of the temporary file directory.

When sorting (order by or group by), MySQL usually uses one or more 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 size of "row pointer" (row pointer) is usually 4 bytes, but later, this value may increase for large tables.

For some SELECT queries, MySQL also creates temporary SQL tables. They are not implicit tables and have names in the SQL _ * format.

Alter table creates a temporary TABLE in the same directory as the original TABLE directory.