Mysql – Will ‘sleeping’ connections open files other than the socket in MySQL

linuxmyisamMySQL

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.

Best Answer

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.

Related Solutions

Mysql – ny way to increase the thesql reserved pool for super users beyond 1

Is there some sort of sane upper limit any one user should never exceed? Maybe your busiest application has at most 20 open connections during peak normal operation. Let's assume you have 20 non root users.

set max_user_connections = 30 (to give a bit of a safe buffer)

set max_connections = max_user_connections * # users + extra buffer, 
   or 30 * 20 + 50 = 650 max connections.

This way your global max connections is safe from every getting reached and there should always be room for you to get in even if one particularly application or user is getting maxed out.

Mysql takes up to 50% cpu after aborting DELETE query

Since sv is a MyISAM table, I would expect a backlog of DB Connections attempting to write to the table in a high-traffic environment.

In fact, any DML (INSERT, UPDATE, DELETE) against the sv table will result in a full table lock on sv.

You should still run a CHECK TABLE sv; as a precaution.

Please keep in mind that data for MyISAM tables are never cached. It is possible that the OS is just swapping used memory as a result of the heavy activity against the MyISAM.

All you can really do in that instance is reboot the OS

Best Answer

Related Solutions

Mysql – ny way to increase the thesql reserved pool for super users beyond 1

Mysql takes up to 50% cpu after aborting DELETE query

Related Question