HostOnNet Blog

MySQL Out of resources when opening file

When i take backup of a MySQL Database with large number of tables, i get following error

# mysqldump freeforums > freeforums.sql
mysqldump: Got error: 23: Out of resources when opening file './freeforums/casualcity_topics_watch.MYD' (Errcode: 24) when using LOCK TABLES

When restoring this Database, i got similar error

# mysql freeforums < freeforums.sql
ERROR 23 (HY000) at line 25: Out of resources when opening file './freeforums/' (Errcode: 24 - Too many open files)


This was caused due to open_files_limit limit.

To fix, i edit /etc/my.cnf

vi /etc/my.cnf

Added open_files_limit under [mysqld_safe] section.

open_files_limit = 50000

Here is the content of /etc/my.cnf file with open_files_limit added

# cat  /etc/my.cnf
# Disabling symbolic-links is recommended to prevent assorted security risks

open_files_limit = 50000


Now you need to restart MySQL

service mysql stop
service mysql start

Related Kernel Parameters

To see maximum allowed file handlers on your system, run

cat /proc/sys/fs/file-max

To see current usage, run

cat /proc/sys/fs/file-nr

This will show

  1. Total allocated file handles.
  2. Number of currently used file handles.
  3. Maximum file handles that can be allocated.

To change value of file-max, edit /etc/sysctl.conf

vi /etc/sysctl.conf



Now run

sysctl -a

This will apply the changes to Kernel with out rebooting.

Posted in MySQL