Mysql – Open files in MySQL increasing over time, eventually causing it to stop accepting connections

MySQL

Every few seconds MySQL is opening a file and not closing it, which we can see in real time by querying

SHOW GLOBAL STATUS WHERE variable_name = 'Open_files';

Looking at the open file descriptors using

sudo ls -la /proc/<pid>/fd | less

shows that these open files are mostly deleted temporary tables:

lrwx------ 1 mysql mysql 64 Apr 17 08:56 990 -> /tmp/mysql_temptable.xTHQV4 (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 991 -> /tmp/mysql_temptable.gr1swq (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 992 -> /tmp/mysql_temptable.sXackV (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 993 -> /tmp/mysql_temptable.Tom8Pa (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 994 -> /tmp/mysql_temptable.OqNhMl (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 995 -> /tmp/mysql_temptable.VOlk8X (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 996 -> /tmp/mysql_temptable.ti1nry (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 997 -> /tmp/mysql_temptable.EeXTiS (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 998 -> /tmp/mysql_temptable.r2GHks (deleted)
lrwx------ 1 mysql mysql 64 Apr 17 08:56 999 -> /tmp/mysql_temptable.NDCeta (deleted)

This is similar to the issue reported at MySQL crashing at about 70k open files, although we get a different error when MySQL runs out of file descriptors. There it's mentioned that this is fixed in 8.0.16, but who knows when that version will release. Meanwhile, we're having serious issues…

Is there any way we can find out what queries are causing these temporary tables to be created? Or is there a way to mitigate this problem in some other way?

Best Answer

We've followed the advice at What value of thread_cache_size should I use? and increased thread_cache_size, which was one of the suggested changes at MySQL crashing at about 70k open files as well. This seems to have stopped Open_files from increasing as it was before (it was increasing by 1 every few seconds).

I believe this makes sense: if the bug is that threads aren't correctly releasing certain files, then creating less threads (reusing more cached threads) could result in less files being left open. This is of course just guesswork of mine.

Related Question