InnoDB: Operating system error number 24 in a file operation

MySQL & MariaDB Percona XtraBackup
1

Hi Guys,

I was trying to make MySQL backup using xtrabackup and I kept getting following error:

7f9c33d0a720 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: [url]http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html[/url]
InnoDB: Error: could not open single-table tablespace file ./optiweber2/top_keyword_urls#P#p61.ibd
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:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2672.

However, the issue is resolved when, I simply increase the ‘open-files-limit’ in my.cnf from

open-files-limit = 131072

to

open-files-limit = 151072

Please note, MySQL has not picked up these changes, its merely editing my.cnf file.

And, my systems open file hard limit is set to 131072 as well.

Excerpt of /etc/security/limits.conf

  • soft nofile 131072
  • hard nofile 131072

~ # ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 118674
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 131072
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 118674
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

The solution did not make sense. Posting it here to get some insight.

Thanks,
Ashim

2

Hi,

Normally, settings in my.cnf will override system settings. Also xtrabackup uses open-files-limit setting from [mysqld] section in my.cnf.
So when you update my.cnf, xtrabackup will read that settings and will not check system settings.

Btw: 31072 is too small for the number of partitions / tables. you should increase it anyway.

3

Hi,

Re: The number is 131072, is that still too small? How do you decide what’s too small and what’s the downside of increasing it?
For example if file system max is

sysctl fs.file-max

fs.file-max = 1509191
How should I decide if it’s safe to increase the process limit number?

Why would the backup need so much more than mysql itself? We don’t have problems with too many open fd’s before this. How do we calculate it’s need?

In this system the my.cnf value and the system fd hard limit are the same number.
I’m assuming that the xtrabackup ran as root and that’s why it could happily exceed the user hard limit number for the system when it read a higher number in the /etc/my.cnf.
However mysql itself couldn’t have reached that higher number, as it doesn’t run as root but as the mysql user.