0

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.

1 Answer 1

0

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.

3
  • indeed it had exceeded the number of maximum number of open files. What I am trying to find is what caused too many files to be opened. We already have a huge number set for the open-files limit for mysql user and the mysqld is started with a huge number of open files limit as well. What I want to know if whether the sleeping connections will keep extra files open other than the network socket.
    – Sreeraj
    Mar 23, 2015 at 5:50
  • Sleeping connections could be keeping files open. Use disconnect() wherever possible when finished with work.
    – Rick James
    Mar 23, 2015 at 17:13
  • Let's see the settings I mentioned.
    – Rick James
    Mar 23, 2015 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.