Too many open files error on Ubuntu 8.04

Question

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

on checking the error 24 on the shell it says

>>perror 24

OS error code  24:  Too many open files

how do I solve this?

Solution 1

At first, to identify the certain user or group limits you have to do the following:

root@ubuntu:~# sudo -u mysql bash
mysql@ubuntu:~$ ulimit -a
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:~$

The important line is:

open files (-n) 1024

As you can see, your operating system vendor ships this version with the basic Linux configuration - 1024 files per process.

This is obviously not enough for a busy MySQL installation.

Now, to fix this you have to modify the following file:

/etc/security/limits.conf

mysql             soft    nofile           24000
mysql             hard    nofile           32000

Some flavors of Linux also require additional configuration to get this to stick to daemon processes versus login sessions. In Ubuntu 10.04, for example, you need to also set the pam session limits by adding the following line to /etc/pam.d/common-session:

session required pam_limits.so

Solution 2

Quite an old question but here are my two cents.

The thing that you could be experiencing is that the mysql engine didn't set its variable "open-files-limit" right.

You can see how many files are you allowing mysql to open mysql> SHOW VARIABLES;

Probably is set to 1024 even if you already set the limits to higher values.

You can use the option --open-files-limit=XXXXX in the command line for mysqld.

Cheers

Solution 3

You can increase your OS limits by editing /etc/security/limits.conf.

You can also install "lsof" (LiSt Open Files) command to see Files <-> Processes relation.

Solution 4

It could also be possible that by some code that accesses the tables dint close those properly and over a point of time, the number of open files could be reached.

Please refer to http://dev.mysql.com/doc/refman/5.0/en/table-cache.html for a possible reason as well.

Restarting mysql should cause this problem to go away (although it might happen again unless the underlying problem is fixed).

Solution 5

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

Before my.cnf changes:

\#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

12345 and 23456 is mysqld process PID, of course.

SHOW VARIABLES LIKE 'open_files_limit' show 4096 now.

All looks ok, while "ulimit" show no changes:

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

View more solutions

Comments

[Nik Reiman]

Wait, I lied. In this case, 24 probably does mean what you think it means. At least one other person seems to have experienced this -- see this bug report on the mysql bug tracker, though this is a rather old report. Are you using mysql 5.0 by chance?

[gawbul]

I had exactly the same issue previously and this solved it for me! +1 for simple, user-friendly answer :-)

[peppered]

Probably, if /etc/pam.d/common-session-noninteractive exists, you should append session required pam_limits.so to this file too.

[David Goodwin]

I experienced this exact problem with Debian Wheezy + MySQL 5.6. Thanks for the answer.