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

This article mainly explains the "brief introduction of mysql hidden space", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "a brief introduction to mysql hidden space"!

The following article mainly introduces the analysis of the problems related to the hidden space of MySQL. We all know that the main function of the TMPDIR value of MySQL is to save the actual pathname of the relevant directory of temporary files. 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 with enough MySQL hidden 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 MySQL hidden space required 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 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.

Thank you for your reading, the above is the content of "brief introduction of mysql Hidden Space". After the study of this article, I believe you have a deeper understanding of the brief introduction of mysql Hidden Space, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.