Monday, February 27, 2012

MySQL's Mystery Files

MySQL is a very powerful and easy to use - and administer RDBMS. For light use it mostly just works out of the box - but if you intend to use it for something serious you need to tweak it some.

For one thing, MySQL makes use of temporary space - that is, temporary space outside of the designated areas where it stores the database files themselves. It appears to be configured as follows:

On Unix, MySQL uses the value of the TMPDIR environment variable as the path name of the directory in which to store temporary files. If TMPDIR is not set, MySQL uses the system default, which is usually /tmp, /var/tmp, or /usr/tmp.

On Windows, Netware and OS2, MySQL checks in order the values of the TMPDIR, TEMP, and TMP environment variables. For the first one found to be set, MySQL uses it and does not check those remaining. If none of TMPDIR, TEMP, or TMP are set, MySQL uses the Windows system default, which is usually C:\windows\temp\.

Source: Where MySQL Stores Temporary Files

One potential caveat is that it uses "hidden" files that can not be detected by normal file system level tools. Here's a discussion on how that is possible. In short - MySQL creates files which are not linked in the file system and hence can not be found and simply vanish if the process making use of them - i.e., the MySQL daemon process - disappears. In other words, you are guaranteed that no left-over space will be used. From the IT standpoint it is a nuisance though as you get file system space consumed without being able to find the files responsible for such consumption.

As to the size of such consumption - I was actively using a database amounting to no more than 300 GB and due to the fact that I only had about 900 GB to spare in the filesystem I was forced to do "repairing with keycache" which is way slower than regular reindexing - but regular reindexing requires lots of temporary space which I apparently lacked.

1 comment:

Anonymous said...

IS this something like file entropy?? Can I store my mother in law there and then kill the process??