MySQL: Too Many Open Files

Recently, I experienced an issue with a MySQL instance running on a CentOS 5.4 host whereby applications were unable to contact the daemon. This was despite there being being no network issues and the operating system not reporting any excessive load. Restarting MySQL fixed the issue and allowed the applications to continue working. While investigating the root cause of the problem, I found that the /var/log/mysqld.log file contained the following messages:

100804 4:43:00 [ERROR] /usr/libexec/mysqld: Can't open file: './test_db/test_table.frm' (errno: 24)
100804 4:43:00 [ERROR] /usr/libexec/mysqld: Can't open file: './test_db/test_table.frm' (errno: 24)
100804 4:43:03 [ERROR] Error in accept: Too many open files

In order to prevent the problem occurring again, I increased the maximum number of file descriptors available to MySQL. First, I increased the maximum number of open file descriptors available to the mysql user account by adding the following two lines to the file /etc/security/limits.conf:

mysql soft nofile 8192
mysql hard nofile 8192

Next, I modified the configuration of the MySQL instance to explicitly set the maximum number of open files allowed by simply adding the following line to the /etc/my.cnf file:

open-files-limit=8192

This configuration directive has a default value of 0, which means MySQL will attempt to allocate enough file descriptors itself. However, as I knew exactly how many open files where available to the MySQL user (8192), I could safely use this as the value for open-files-limit.
A restart of MySQL applied the changes and resolved the issue.