Fix Out of Resource Problem with MySQL

Update:

This did not seem to work, and the only solution seems to be to upgrade to MySQL 5.5 11/27/2011

______________________________________________________________________

Possible errors you might be having:

mysqldump: Couldn't execute 'show fields from `tablename`': Out of resources when opening file './databasename/tablename#P#p125.MYD' (Errcode: 24) (23)

I started having problems with this when I upgraded to Ubuntu 10.10 with MySQL 5.1 installed. I am running a large database for the Open Dental Software in my office and was having the hardest time figuring out how to fix the problem. Finally I figured out it was not a MySQL problem, but a limit put on the number of files MySQL or my user could open by Ubuntu “security.”

View the problem

Here is how to test to see if this might be an issue:

sudo -u mysql bash
mysql@ubuntu:~$ ulimit -a

You will get a readout something like this:

core file size Â Â Â Â Â (blocks, -c) 0 data seg size Â Â Â Â Â (kbytes, -d) unlimited scheduling priority Â Â Â Â Â Â (-e) 0 file size Â Â Â Â Â Â Â (blocks, -f) unlimited pending signals Â Â Â Â Â Â Â Â (-i) 71680 max locked memory Â Â Â (kbytes, -l) 32 max memory size Â Â Â Â (kbytes, -m) unlimited open files Â Â Â Â Â Â Â Â Â Â Â (-n) 1024 pipe size Â Â Â Â Â Â (512 bytes, -p) 8 POSIX message queues Â Â (bytes, -q) 819200 real-time priority Â Â Â Â Â Â Â (-r) 0 stack size Â Â Â Â Â Â Â (kbytes, -s) 8192 cpu time Â Â Â Â Â Â Â (seconds, -t) unlimited max user processes Â Â Â Â Â Â Â (-u) 71680 virtual memory Â Â Â Â Â (kbytes, -v) unlimited file locks Â Â Â Â Â Â Â Â Â Â Â (-x) unlimited mysql@ubuntu:~$

Look at the line:

open files Â Â Â Â Â Â Â Â Â Â Â (-n) 1024

This tells you that your system is limiting you to 1024 files open and that is what cause the problem on large databases.

Fix the problem

Type:

sudo nano /etc/security/limits.conf

Add the following lines:

mysql soft nofile 24000 mysql hard nofile 32000

Reboot the system and then follow the steps above to see if the table is changed the number of files is very large now to match the settings you just inserted

March 1, 2011

drtech

Computers, Open Dental

6 responses to “Fix Out of Resource Problem with MySQL”

Puran Prajapati says:

February 20, 2012 at 2:40 am

Hi,

I have add the both line in /etc/security/limits.conf. Comparably, error coming less but still there are some error. Error are below

mysqldump: Couldn’t execute ‘SHOW TRIGGERS LIKE ‘content\_postsredirect”: Can’t read dir of ‘./contentdata_live/’ (errno: 24) (1018)
mysqldump: Couldn’t execute ‘SHOW TRIGGERS LIKE ‘tbl\_admin”: Can’t read dir of ‘./contestcentral/’ (errno: 24) (1018)
mysqldump: Couldn’t execute ‘show fields from `si_biller`’: Out of resources when opening file ‘/tmp/#sql_16b5_0.MYI’ (Errcode: 24) (23)

Please help me.

Thanks

Puran Prajapati
PL says:

August 21, 2012 at 5:18 am

Try open_files_limit in your /etc/my.cnf
Huzoor Bux Panhwar says:

September 5, 2012 at 12:42 am

Hi I did same as you explain and didn’t get any change in my server. 🙁
Michael Watkins says:

October 16, 2012 at 8:26 am

Working on RedHat here,

Didn’t understand the ulimit -a command but adding the mysql user to the limits.conf file and rebooting worked perfectly.

Thanks!
Wilson Hernandez says:

June 22, 2013 at 10:47 am

I got the ‘Out of resources when opening file’ error due to high traffic website but, can’t seem to resolve my problem.
Pavel says:

October 20, 2013 at 9:00 am

There are no need to configure PAM. On my system (Debian 7.2 with Percona 5.5.31-rel30.3-520.squeeze ) I have:

# cat /proc/12345/limits |grep “open files”
Max open files 1186 1186 files

After adding “open_files_limit = 4096” into my.cnf and mysqld restart, I got:

# cat /proc/23456/limits |grep “open files”
Max open files 4096 4096 files

All looks ok, while “ulimit” show no changes:

# su – mysql -c bash
# ulimit -n
1024

Smilecouple.org