How to Fix ‘Too many open files’ Problem

I have been facing following problem when executing Percona Xtrabackup in my CentOS 6.3 box:

xtrabackup_55 version 2.1.3 for Percona Server 5.5.16 Linux (x86_64) 
(revision id: 608) 
xtrabackup: uses posix_fadvise(). 
xtrabackup: cd to /var/lib/mysql 
xtrabackup: Target instance is assumed as followings. 
xtrabackup: innodb_data_home_dir = ./ 
xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend 
xtrabackup: innodb_log_group_home_dir = ./ 
xtrabackup: innodb_log_files_in_group = 2 
xtrabackup: innodb_log_file_size = 67108864 
xtrabackup: using O_DIRECT 
130619 12:57:36 InnoDB: Warning: allocated tablespace 2405, old maximum 
was 9 
130619 12:57:37 InnoDB: Operating system error number 24 in a file 
operation. 
InnoDB: Error number 24 means 'Too many open files'. 
InnoDB: Some operating system error numbers are described at 
InnoDB: 
http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html 
InnoDB: Error: could not open single-table tablespace file 
InnoDB: We do not continue the crash recovery, because the table may become 
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it. 
InnoDB: To fix the problem and start mysqld:

Linux / UNIX sets soft and hard limit for the number of file handles and open files. By default the value is too low as you can check using following command:

$ ulimit -n
1024

To increase open files limitation, you can use several ways:

1. Set the limit using ulimit command

$ ulimit -n 8192

This is temporary solution as it will increase the limit accordingly per login session.  Once you logged out and login again, this value will back to default.

2. Permanently define in /etc/security/limits.conf

To make it permanent, you can define the values (soft and hard limit) at /etc/security/limits.conf by adding following lines:

* soft nofile 8192
* hard nofile 8192

The soft limit is the value that the kernel enforces for the corresponding resource. The hard limit acts as a ceiling for the soft limit. Reboot the server to apply the changes. Or, if you do not want to reboot, add following line into the respective user’s .bashrc file, as in my case is root:

$ echo "ulimit -n 8192" >> ~/.bashrc

You will then need to relogin into the session to see the changes.

If the problem still persists, you might need to increase the limit higher and retry again the failed process.

Warning

Do not set the value to unlimited as it can caused PAM to fail and you will not able to SSH or console into the box with following error:

Apr 19 09:22:15 rh02 sshd[5679]: error: PAM: pam_open_session(): Permission denied

This issue has been reported in this bug report.

Further reading:

http://ss64.com/bash/ulimit.html
http://ss64.com/bash/limits.conf.html