innobackupex fails with below error:
InnoDB: Operating system error number 23 in a file operation. InnoDB: Error number 23 means 'Too many open files in system'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html InnoDB: Error: could not open single-table tablespace file
Issue :
Innobackupex is failing due to server restriction on open file descriptors for ‘mysql’ user.
[email protected][/mysql/data] ulimit -Hn 32000
Ulimit hard limit is only set to 32k, we can estimate the ulimit required by Mysql user by looking at the number of partitions/tables we have.
[email protected][/mysql/data] find . -name "*.ibd"|wc -l 185293
Or
mysql> select count(*) from information_schema.partitions; +----------+ | count(*) | +----------+ | 185433 | +----------+ 1 row in set (1.68 sec)
Fix :
1. Change ulimits for Mysql user, add below lines to /etc/security/limits.conf. We are making it 2.5 lakh.
mysql soft nofile 50000 mysql hard nofile 250000
2. If we are passing –defaults-file option to innobackupex command, we need to set open_files_limit=250000 in the cnf file (xtrabackup section). We should see following output while running backup.
xtrabackup: open files limit requested 400000, set to 400000
3. Even though we have set the ulimits, In some cases server restricts the open file descriptors. To determine the maximum number of file handles we need to check system level setting file-max.
[[email protected] ~]$ cat /proc/sys/fs/file-max 65536
Set this to higher value :
sysctl -w fs.file-max=250000
fs.file-max=250000