Analysis on the problem of hiding space in MySQL _ MySQL

Source: Internet
Author: User
Analysis of hidden space in MySQL uses the value of the environmental variable TMPDIR as the path name of the directory for saving temporary files. 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.