Why am I seeing “[ERROR] /usr/sbin/mysqld: Can’t open file: ‘./databasename/tablename.frm’ (errno: 24)”?

Problem:

MySQL stops responding for a few minutes and during that time, the logs show messages like “Error in accept: Too many open files” and “Can’t open file”:

Dec  2 07:14:34 www mysqld: 141202  7:14:34 [ERROR] Error in accept: Too many open files
Dec  2 07:15:06 www mysqld: 141202  7:15:06 [ERROR] /usr/sbin/mysqld: Can't open file: './databasename/tablename.frm' (errno: 24)
Dec  2 07:15:13 www mysqld: 141202  7:15:13 [ERROR] /usr/sbin/mysqld: Can't open file: './databasename/tablename.frm' (errno: 24)

Upon starting MySQL, you might also see messages like:

ERROR 23 (HY000) at line 1: Out of resources when opening file './totallyfreeimages/word_occurs.MYD' (Errcode: 24)

(Detected after upgrade to Debian package “mysql-server-5.5”, version “5.5.40-0+wheezy1”.)

Explanation:

The MySQL server is running out of file handles, as it’s hitting the per-process open file handle limit. If your server has a lot of databases and tables (or if you use the server option “innodb_file_per_table”, or if you need to increase your table cache size) then this can appear. Every table the server is accessing will use up a system file-handle.

To see the current server file-handles limit:

mysql> SHOW VARIABLES LIKE 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

mysql> 

You can see how many open files the server is currently using:

mysql> SHOW GLOBAL STATUS LIKE 'Open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 296   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

About “upstart” and /etc/init/mysql.conf

Debian and Ubuntu use a new service management package championed by Ubuntu called “Upstart”, configured in /etc/init/ (not to be confused with /etc/init.d/). Files in /etc/init are configuration files telling Upstart how and when to start, stop, reload the configuration, or query the status of a service. In newer versions of Debian and Ubuntu, there’s a transition from SysVinit to Upstart, which is why many services come with SysVinit scripts even though Upstart configuration files are preferred. In fact, the SysVinit scripts are processed by a compatibility layer in Upstart.

Solution:

Add this to the [mysqld] section of your /etc/mysql/my.cnf:

open_files_limit=8192

..and re-start MySQL. If the “open_files_limit” hasn’t increased, you may need to do the following:

In the past, these per-process file-handle limits were set in /etc/security/limits.conf, and you would add the following to your /etc/security/limits.conf:

mysql            hard    nofile          8192
mysql            soft    nofile          1200

On newer Debian and Ubuntu systems, MySQL is started by “upstart”.

Modify the upstart config file for MySQL and add the following lines before the pre-start block:

# We need to raise the open-file limits here
limit nofile 32000 32000
limit nproc 32000 32000

..or create a new /etc/init/mysql.conf, if you don’t have one:

# We need to raise the open-file limits here
limit nofile 32000 32000
limit nproc 32000 32000

References:

http://serverfault.com/questions/440878/changing-open-files-limit-in-mysql-5-5
http://askubuntu.com/questions/5039/what-is-the-difference-between-etc-init-and-etc-init-d
http://dev.mysql.com/doc/refman/5.1/en/table-cache.html

Share: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Twitter
  • Facebook
  • Google Bookmarks
  • LinkedIn
  • Reddit
  • StumbleUpon

Leave a Reply

Your email address will not be published. Required fields are marked *