Not the answer you need?
Register and ask your own question!

How to get Percona Server 5.7 respect open files limit?

LeonardChallisLeonardChallis EntrantCurrent User Role Novice
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 (https://jira.percona.com/browse/PS-1796?page=com.atlassian.jira.plugin.system.issuetabpanels%3Aall-tabpanel) and the work around there and also referenced in the Percona blog post about it (https://www.percona.com/blog/2017/10/12/open_files_limit-mystery/) 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

Comments

  • LeonardChallisLeonardChallis Entrant Current User Role Novice
    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 |
    +------------------+---------+
    
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.