[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
- To the file
/etc/security/limits.conf
, add the following lines:
mysql soft nofile 24000 mysql hard nofile 32000
- To the file
/etc/pam.d/common-session
, towards the end of the file, add the line:
session required pam_limits.so
- To the file
/etc/pam.d/common-session-noninteractive
, towards the end of the file, add the line:
session required pam_limits.so
- To the file
/etc/my.cnf
, under the section'[mysqld]'
, add the line:
open_files_limit = 24000
- 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:
- http://stackoverflow.com/questions/502545/too-many-open-files-error-on-u...
- http://secure.hens-teeth.net/orders/knowledgebase/109/MYSQL-Out-of-resou...
- https://forums.cpanel.net/f354/upgrade-mysql-5-5-sqlstate-hy000-general-...
- 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!
- 55304 reads
Comments
Antonio (not verified)
Tue, 05/05/2015 - 05:55
Permalink
Thanks!!
Thanks!
This solved my problems!
Alejandro (not verified)
Thu, 06/25/2015 - 08:51
Permalink
This solved my problem! Thanks!
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!
steward (not verified)
Sat, 11/07/2015 - 22:07
Permalink
Thanks for posting.
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