0

I have an issue with the following error:

Too many open files (24)

And after lots of hours researching, we noticed that Open_files status do show a count of files opened, but the error occured when this number was below the open_files_limit setting? How is this possible?

We are using table_open_cache setting as well and after reading how MySQL documentation about table_open_cache and how MySQL opens and closes tables, we start to think it's because that the status count open_files does not calculate the tables opened that goes into table_open_cache, but not sure.

Any clear answer on how this can be possible?

5
  • What Linux distribution? How did you install the MySQL server? How did you start it? – Michael Hampton Jan 26 '18 at 22:26
  • From dev.mysql.com/doc/refman/5.7/en/server-status-variables.html "It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so." And that has nothing to do with the os ulimit. – symcbean Jan 26 '18 at 23:47
  • @MichaelHampton Amazon Linux AMI, installed the MySQL like normal? Also started it like normal? sudo service mysqld start. Not really sure if these answers help. – Karem Jan 27 '18 at 10:34
  • @symcbean I have taken notes to that line, but what does that even mean? Is table_open_cache then not being calculated to the count of open files? – Karem Jan 27 '18 at 10:35
  • What were the hard and soft ulimit values? How many files were open? Code 24 is EMFILE, which means you hit the system/process limit for open file descriptors. – David Schwartz Nov 19 '18 at 6:27
2

The current version of Amazon Linux AMI utilises systemd. If that's what you are using whatever you set in /etc/security/limits.conf will be ignored by systemd.

Find the .service file for your version of MySQL and update it. For example:

/usr/lib/systemd/system/mysqld.service

Under the Service section add the following:

[Service]
LimitNOFILE=4096

Change 4096 to the limit you need.

Afer you update that file reload the systemd configuration:

systemctl daemon-reload

Then restart the mysql server.

2
  • But i have already changed the limit in limits.conf which is confirmed when i check with query: SHOW VARIABLES LIKE 'open_files_limit'; ? and can see that the open_files status count is using more files than the previous limitation there was. So this is not the cause to my case? – Karem Jan 31 '18 at 8:45
  • Maybe check to see how many threads are being opened by the mysql process? – Schrute Feb 1 '18 at 22:06
0

Run following commands to see open-files limit:

mysql> select @@max_open_files ;

And from system side:

#sudo -u mysql bash
$ulimit -Sn
$ulimit -Hn

Add following line into your my.cnf:

open-files-limit = 4096

Eximine /etc/security/limits.conf for strings with mysql, and add it if needed:

# echo "mysql           soft    nofile          4096" >> /etc/security/limits.conf
# echo "mysql           hard    nofile          4096" >> /etc/security/limits.conf

Then restart mysql-server Сhange the value to the one you need

2
  • Thanks for instructions on how to raise the limit, although this does not answer my question at all. ulimit are already set to another number and the open files limit is also set to it. – Karem Jan 27 '18 at 10:37
  • As I know, open_files_limit should be increased after increasing parametrs like table_cache, max_connections, max_tmp_tables. Which OS mysql-server is running on? If you are using CentOS 7, you should check cat /proc/$(pidof mysqld)/limits. And check init-file in systemd directory for limits. Add LimitNOFILE=4096 in [Service] block if needed. Then run systemctl daemon-reload && systemctl restart mysqld – Artem Budaev Jan 27 '18 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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