Mysql-MySQL tutorial

Detailed analysis of mysql hidden space the following articles mainly introduce the analysis of MySQL hidden space problems, we all know that the main function of the TMPDIR value of the environment variable used by the MySQL database is to save the actual path name of the directory related to 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 that has enough space to hide MySQL.

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 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 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. The above content is a description of the hidden space problem of MySQL. I hope it will help you in this regard.