A detailed analysis of MySQL hidden space

The following article is mainly about the MySQL hidden space-related issues, we all know that the use of the MySQL database tmpdir value of the environment variable is to save the actual path name of the relevant directory of the temporary file. If Tmpdir,mysql is not set, the default value for the system is used, 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 1 directories in a filesystem with enough MySQL hidden space.

In MySQL 5.1, the "-tmpdir" option can be set to a list of several paths, used in a circular fashion. On UNIX platforms, the path is separated by the colon character ":" On Windows, NetWare, and OS/2 platforms with a semicolon character ";" Separated. Note that in order to efficiently distribute the load, these paths should be on different physical disks rather than in different partitions on the same disk.

If the MySQL server is being used as a copy from the server, you should not set "--tmpdir" to a directory that points to the memory-based file system or to the directory that will be emptied when the server host restarts. For replication from the server, some temporary files need to be retained when the machine restarts so that temporary tables can be replicated or the load DATA infile operation is performed. If the files in the temporary files directory are lost when the server restarts, replication will fail.

MySQL creates all the temporary files in an implied manner. This ensures that all temporary files are deleted when the mysqld is aborted. The disadvantage of using hidden files is that in the location of the temporary file directory, you cannot see the large temporary files that occupy the file system.

When sorting (order by or group by), MySQL typically uses 1 or more temporary files. The maximum required disk MySQL hidden space is determined by the following expression:

1. (Length of what is sorted + sizeof (row pointer))

2.* Number of matched rows

3.* 2

The "Row pointer" (row pointer) size is usually 4 bytes, but in the future, the value may increase for large tables.

For some select queries, MySQL also creates temporary SQL tables. They are not hidden tables and have names in sql_* form.

ALTER table creates a temporary table in the same directory as the original table directory. The above related content is to the MySQL hidden space question brief description, hoped will bring some help to you in this aspect.