Too many open files error on Ubuntu 8.04

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?

From stackoverflow

• There is no guarantee that "24" is an OS-level error number, so don't assume that this means that too many file handles are open. It could be some type of internal error code used within mysql itself. I'd suggest asking on the mysql mailing lists about this.

• 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?

• 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).

• i still cant make it work...

• am using mysql 5.1 and am still experiencing the error

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

• havent found a good solution yet

• add --single_transaction to your mysqldump command

• 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 you OS vendor ships this version with the basic Linux configuration which is 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