[SOLVED] How to Fix on Ubuntu/Debian linux - MySQL/MariaDB: Out of resources when opening file (Errcode: 24)

This is an issue which occurs due to the maximum number of open files allowed for a process (mysql in this case) being very less that what is required - something that happens because you have lots of large tables / a large DB. To fix this, you just have to increase the limits in a few places.

Here is how you do it:

Step 1: Identify the issue
as limit on the max number of open files (1024) for a single process (mysql) by doing these as root user:
ulimit -a | grep open

    root@server:/home/user# ulimit -a | grep open
    open files                      (-n) 1024

and
SHOW VARIABLES LIKE 'open%'; done in MySQL commandline, logged in as MySQL root user.

  root@server:/home/user# mysql -u root -p
  MariaDB [(none)]> SHOW VARIABLES LIKE 'open%';
  +------------------+-------+
  | Variable_name    | Value |
  +------------------+-------+
  | open_files_limit | 1024  |
  +------------------+-------+
  1 row in set (0.00 sec)


Step 2: Do the Fix

  1. To the file /etc/security/limits.conf, add the following lines:
          mysql             soft    nofile           24000
          mysql             hard    nofile           32000
    
  2. To the file /etc/pam.d/common-session, towards the end of the file, add the line:
    session required pam_limits.so
  3. To the file /etc/pam.d/common-session-noninteractive, towards the end of the file, add the line:
    session required pam_limits.so
  4. To the file /etc/my.cnf, under the section '[mysqld]', add the line:
    open_files_limit = 24000
  5. Restart the server.

Step 3: Verify the fix with:
root@server:/home/user# ulimit -a | grep open
and
MariaDB [(none)]> SHOW VARIABLES LIKE 'open%';

--

References:

  1. http://stackoverflow.com/questions/502545/too-many-open-files-error-on-u...
  2. http://secure.hens-teeth.net/orders/knowledgebase/109/MYSQL-Out-of-resou...
  3. https://forums.cpanel.net/f354/upgrade-mysql-5-5-sqlstate-hy000-general-...
  4. http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit

--

Did this help you out? Please let me know using the Comments box below.

Cheers!

Comments

Thanks!
This solved my problems!

I'm running Debian Wheezy and Mysql 5.6. My problem was that after reboot, mysql was always starting with 1024, and after a manual restart this parameter got the right value.
I was missing the "session required pam_limits.so" lines.

Thanks you!

Thanks for posting.

ulimit- a still showed 1024 after reboot. But sql showed 24000.

It fixed my issue, was able to continue working. Great!

I understand increasing the limits for sql. The pam stuff made no sense.

Add new comment

randomness