Shulou(Shulou.com)05/31 Report--

The editor will share with you an example analysis of hidden space problems in MySQL. I hope you will gain something after reading this article. Let's discuss it together.

The hidden space of MySQL is realized by setting the environment variable TMPDIR. If TMPDIR,MySQL is not set, the default value of the system is used, usually / tmp, / var/tmp, or / usr/tmp. MySQL uses the value of the environment variable TMPDIR as the pathname of the directory where the temporary files are saved. If TMPDIR,MySQL is not set, the default value of the system is used, usually / tmp, / var/tmp, or / usr/tmp. If the file system that contains temporary file directories is too small, use the "- tmpdir" option for d to specify 1 directory within the file system that has enough space.

In MySQL 5.1, the "- tmpdir" option can be set to a list of several paths, which can be used in a circular manner. On Unix platforms, paths are separated by the colon character ":", and on Windows, NetWare, and OS/2 platforms, paths are separated by semicolon characters ";". Note that in order to effectively distribute the load, these paths should be on different physical disks, not on different partitions of the same disk.

If the MySQL server is being used as a replication slave server, the "--tmpdir" should not be set to point to the directory of the memory-based file system, or the directory that will be emptied when the server host restarts. For the replication slave server, you need to keep some temporary files when the machine is rebooted so that you can copy temporary tables or perform LOAD DATA INFILE operations. If the files in the temporary files directory are lost during the server restart, 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 implicit files is that large temporary files that occupy the file system cannot be seen in the same location as 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 the "row pointer" (row pointer) is usually 4 bytes, but this value may increase later for large tables.

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

ALTER TABLE creates temporary tables in the same directory as the original table directory.

After reading this article, I believe you have some understanding of "sample Analysis of Hidden Space problems in MySQL". If you want to know more about it, please follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.