CODEWITHSUNDEEP
mysql
Naresh kumar Sanda
Naresh kumar Sanda

Reputation: 157

How to fix "too many open files" in MySQL?

I am very frequently getting this error in MySQL:

OS errno 24 - Too many open files

What's the cause and what are the solutions?

Upvotes: 10

Views: 35524

Answers (4)

Harshit Rastogi
Harshit Rastogi

Reputation: 2122

If you are using mysql on Ubuntu, you can try below:

# Edit MySQL configuration file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add the following line under [mysqld]
open-files-limit = 65535

# Restart Mysql
sudo systemctl restart mysql

Upvotes: -1

Geoffroy
Geoffroy

Reputation: 53

I faced the same problem and found a solution on another stackoverflow-question.

By running the following snippet with Bash:

ulimit -n 30000

Upvotes: 2

giannis.epp
giannis.epp

Reputation: 1529

I was getting the errno: 24 - Too many open files too often when i was using many databases at the same time.

Solution

  • ensure that the connections to db server close propertly

  • edit /etc/systemd/system.conf. Uncomment and make

     DefaultLimitNOFILE=infinity
 DefaultLimitMEMLOCK=infinity

    then run systemctl daemon-reload and service mysql restart.

You can check the results with the query: SHOW GLOBAL VARIABLES LIKE 'open_files_limit' and you may notice that the value has changed. You should not have any errno 24 now.

Please notice that the solution may differ from other OS/versions. You can try to locate the variables first.Tested with Ubuntu 16.04.3 and mysql 5.7.19.

In my case it was useless to setting up the open_files_limit variable in mysql configuration files as the variable is flagged as a readonly.

I hope it helped!

Upvotes: 11

Darshan Mehta
Darshan Mehta

Reputation: 30839

You probably have a connection leak in your application, that is why open connections are not closed once the function completes it's execution.

I would probably look into the application code and see where the connections/preparedstatement (if it's java) objects are not closed and fix it.

A quick workaround is to increase ulimit of the server (explained here) which would increase number of open file descriptors (i.e. connections). However, if you have a connection leak, you will encounter this error again, at later stages.

Upvotes: 4

Related Questions