Will 'sleeping' connections open files other than the socket in MySQL?

Question

Recently, in our MySQL replication setup, the replication slave crashed due to 'too many open files' error. We brought it backup, but trying to figure out what caused the unexpected hike in the number of open files. We habitually see too many connections in 'sleep' status. Would these 'sleeping' connections open files other than the sockets they keep open?

MySQL version is 5.6.20. OS is RHEL 6.4

Edit: Our MySQL is started with a huge number of 'open-files' limit and the OS has already allowed mysql user to open that many number of files. I am doing an RCA and would like to know whether the sleeping connections would open files other than the network socket they open.

Answer 1

SHOW VARIABLES LIKE '%open%';

Probably one of those is too low.

At a shell prompt, do

ulimit -n

That will tell you what the OS is letting you have. In some recent OS builds, it was the ridiculously low value of 1024. If so, increase it to, say, 16000. Then restart mysqld and see if the SHOW increased the values. (Some default values are computed.) If it did not, then go into my.cnf to increase some things.

"Sleep" processes may be hanging onto open files, but they won't be getting more.

Do you have a large max_connections?

Do you have lots of tables? Lots of PARTITIONs? Each PARTITION uses 1-3 files. (This is one of several reasons for not breaking a table into more than, say, 50 partitions.)

Oh. You are using MyISAM. It needs 3 files per table. Switch to InnoDB, with innodb_file_per_table = 1 -- that way you need only 1 file per table.