MySQL not releasing temp file descriptors

Question

Since a few days ago, we’ve been experiencing some serious problems with our MySQL installation: MySQL keeps opening temporal files (normal behaviour) but these files are never released. The consequence is that, eventually, the disk space is exhausted and we have to restart the service and clean up /tmp manually.

Using lsof, we see something like this:

mysqld    16866     mysql    5u      REG    8,3          0        692 /tmp/ibyWJylQ     (deleted)
mysqld    16866     mysql    6u      REG    8,3          0        707 /tmp/ibf5adsT  (deleted)
mysqld    16866     mysql    7u      REG    8,3          0        728 /tmp/ibGjPRyW (deleted)
mysqld    16866     mysql    8u      REG    8,3          0       5678 /tmp/ibMQDLMZ (deleted)
mysqld    16866     mysql   13u      REG    8,3          0       5679 /tmp/ibQAnM42 (deleted)

Maybe it's not related, but when we shutdown the server, the files are finally freed, and we can see the following warnings in the MySQL log:

121029  7:44:27 [Warning] /usr/local/mysql/bin/mysqld: Forcing close of thread 1333  user: 'xxx'
121029  7:44:27 [Warning] /usr/local/mysql/bin/mysqld: Forcing close of thread 1156  user: 'yyy'
121029  7:44:27 [Warning] /usr/local/mysql/bin/mysqld: Forcing close of thread 1151  user: 'zzz'

where 'xxx', 'yyy' and 'zzz' are distinct mysql users (and the only 3 users with active connections to the database).

We have a few theories:

• There is a problem in the OS, that keeps file handlers open. Could it be possible that the OS "delete" operation blocks the threads until shutdown? This may explain the warning at shutdown and the fact that files are finally deleted when the process dies.

• Until now, data sets were so small that temp files were relatively small and there was enough time to release the file handles without exhausting disk space.

We are using Mysql 5.5 on a RHEL 6.2 with the default kernel.

Solution

Well... It's not really a solution, but i consider it the end of the research.

It seems to be a bug in MySQL. We found it through this bug that seems a duplicate of this one

As workaround, to avoid generating so many temporal files, we increased binlog_cache_size to a value reasonable for us (after benchmarking the application a bit, and checking the size of the files with lsof). If you want to explore more, you can find an article about other options to solve this.

Hope it helps someone ;)

Comments

[Wakaru44]

Funny enough that Bug was reported by a guy I know. It was fixed on 5.6 :)