xtrabackup fails with error number 23

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
Modify /etc/sysctl.conf setting ‘fs.file-max‘ for permanent change.
fs.file-max=250000

Leave a Reply

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