SST xtrabackup: open files limit requested x, set to y

[matthiaz]

Ubuntu 16.04

I upgraded my nodes from 5.7.18 to 5.7.19 something went wrong and one node had to do a complete SST.
Unfortunatly when attempting an SST xtrabackup v2 hits an open files limit and I can’t figure out why. I followed the blog post Peter wrote a while back:
https://www.percona.com/blog/2016/12/28/using-percona-xtrabackup-mysql-instance-large-number-tables/
That was very helpful and it seems like it solves many other people’s problems.

On the donor node and on the requester node my limits are now:
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) 201131
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 820000
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 201131
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

So that should be correct.
I’ve also edited my.cnf to include the open_files_limit
[xtrabackup]
open_files_limit=2000000

However when I attempt an SST, xtrabackup resets it to 5000. Where is this 5000 coming from? How can I make it respect the limit I’m asking for?

/var/log/mysql/error.log on donor:

171103 17:15:17 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/run/mysqld/mysqld.sock’ as ‘sstuser’ (using password: YES).
171103 17:15:17 version_check Connected to MySQL server
171103 17:15:17 version_check Executing a version check against the server…
171103 17:15:17 version_check Done.
171103 17:15:17 Connecting to MySQL server host: localhost, user: sstuser, password: set, port: not set, socket: /var/run/mysqld/mysqld.sock
Using server version 5.7.19-17-57-log
xtrabackup version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 2000000, set to 5000
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
171103 17:15:17 >> log scanned up to (991500860571)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 651125 for himalaya_novocar_lukkien/Expert, old maximum was 0
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 http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
InnoDB: File ./himalaya_novocar_lgconcept/Extern_ubench_orderline.ibd: ‘open’ returned OS error 124. Cannot continue operation
InnoDB: Cannot continue operation.

[krunalbauskar]

magic number of 5000 is default number of open files that mysql configure as one of the limit

/* Try to allocate no less than 5000 by default. */
limit_3= open_files_limit ? open_files_limit : 5000;

you can read more about the logic in this function. adjust_open_files_limit() of MySQL.

---

Why XB ignored setting in [xtrabackup] section ? I will let XB team answer it but I can tell you how you can get your limit set.

xtrabackup: open files limit requested 100000, set to 200000

[mysqld]
open_files_limit=200000

[xtrabackup]
open_files_limit=100000

So eventually [mysqld] limit is honored though that will also alter limit for normal running server.

[matthiaz]

Hi Krunalbauskar,

Thanks for your input. Strange thing is, I already have
[mysqld]
open_files_limit=500000
in my my.cnf file. So according to that logic it should say: xtrabackup: open files limit requested 2000000, set to 500000

But the open_files_limit global variable is set to 5000
mysql> show global variables like “open_files%”;
±-----------------±------+
| Variable_name | Value |
±-----------------±------+
| open_files_limit | 5000 |
±-----------------±------+

I’ll give the server another reboot tonight because you cannot set that variable at runtime. I’ll post back if that solves the issue.

But I agree with you, if the xtrabackup variable is being ignored, it has no point… Without it we could simply set the mysqld variable and be done with it. But that is obviously not always what you want.

[matthiaz]

Rebooted last night. DIdn’t work ;-( somehow the open_files_limit is still on 5000

Any ideas on why the setting is ignored even when it is set ?

[mysqld]
open_files_limit=500000

[matthiaz]

I had a call with Percona and they pointed out the article written by Sveta Sminorva
https://www.percona.com/blog/2017/10/12/open_files_limit-mystery/

For me the 5000 limit was found in the systemd config for mysql. Changing that to infinity fixed it.