Home / server / Questions / 894261
In Process
Karem
Asked: 2018-01-27 06:00:53 +0800 CST

MySQL: Too many open files (24) BUT open_files has not reached open_files_limit?

  • 772

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?

mysql

2 Answers

  1. 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

  2. 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

    • 0