How to get Percona Server 5.7 respect open files limit?

MySQL & MariaDB Percona Server for MySQL 5.7
1

My system:

Linux x.x.x.x.hostname 3.10.0-862.14.4.el7.x86_64 #1 SMP Wed Sep 26 15:12:11 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

I am seeing errors like:

mysqldump: Got error: 23: Out of resources when opening file './someschema/somerelation.MYD' (Errcode: 24 - Too many open files) when using LOCK TABLES

from my cron output on some backup scripts. Reading into this I believe it’s because I need to up the open files limit. Currently it is 5000:

mysql> select @@open_files_limit;
+--------------------+
| @@open_files_limit |
+--------------------+
| 5000 |
+--------------------+
1 row in set (0.00 sec)

I was reading about the bug where open file limits are ignored in 5.7 ([url][PS-1796] LP #1673712: Open files limit no longer applied after in place upgrade from 5.6 to 5.7 - Percona JIRA) and the work around there and also referenced in the Percona blog post about it ([url]https://www.percona.com/blog/2017/10/12/open_files_limit-mystery/[/url]) and so I tried to bump these up to huge values to test - this is what I’ve done:

/etc/security/limits.conf:

* soft nofile 1024000
* hard nofile 1024000
* soft nproc 10240
* hard nproc 10240

$ ulimit -n
1024000

I’m not sure if it’s actually needed, after reading those links above, but in the [mysqld] section of my MySQL conf (/etc/percona-server.conf.d/mysqld.cnf) I also have:

open-files-limit=1024000

After restarting, my open files limit in MySQL is still 5000. When I look at the limits…

[root@yoshi ~]# systemctl status mysql.service | grep PID
Main PID: 31668 (mysqld)
[root@yoshi ~]# cat /proc/31668/limits | grep files
Max open files 5000 5000 files

In the blog post it says:

You also need to have a row session required pam_limits.so in the file /etc/pam.d/common-session.

I don’t actually see a file called /etc/pam.d/common-session anywhere though. I read somewhere that the alternative on Cents 7 is etc/pam.d/system-auth. In that file I see:

session required pam_limits.so

So I presumed this should now work. I’ not sure where to go from here, can anyone advise how I can make Percona take the open files limit into account (or indeed, properly set the open files limit of the system?)

Thanks

2

So it looks like I finally came across the correct solution. But if someone has any comments on this that’d be great.

[root@yoshi ~]# find / -type f -name mysqld.service
/usr/lib/systemd/system/mysqld.service
[root@yoshi ~]# vim /usr/lib/systemd/system/mysqld.service # changed LimitNOFILE = 5000 to LimitNOFILE = 1024000
[root@yoshi ~]# systemctl daemon-reload
[root@yoshi ~]# systemctl restart mysql.service
[root@yoshi ~]# mysql -e "show global variables like 'open%';"
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| open_files_limit | 1024000 |
+------------------+---------+