Setting 'open_files_limit' in Percona 5.7 and CentOS 7.5

Giorgio Sintichakis — Tue, 16 Oct 2018 21:57:49 +0000

The Issue

One of our database servers was crashing so I looked into the logs and found some typical warnings:

[Warning] Changed limits: max_open_files: 5000 (requested 5125)
[Warning] Changed limits: table_open_cache: 1983 (requested 2000)

These default values just wouldn't do, so I made sure the system limits were increased in /etc/security/limits.conf and restarted the server. However, modifying my.cnf settings to change the open_files_limit to use the new limits would not stick and MySQL continued to report the default value for open_files_limit.

Searching for the Answer

Countless hours later, after dozens of blog posts and StackOverflow guides, I still could not get that variable to stick! Each guide did something just a little differently and no two were the same. Some felt too "invasive" and touched too many system files and settings, and none worked to fix my issue. I wanted a simple, straightforward solution that worked for my particular version of Percona and CentOS.

Finally, I cracked it!

Thanks to some help from Sveta's post, I managed to come up with an elegant solution that worked for me.

The Fix

1. Ensure enough resources are available for processes

vi /etc/security/limits.conf

Paste the following contents:

* soft nofile 65535
* hard nofile 65535

Feel free to tweak this to your own needs, as you might want more open files or to specify separate values for the root or mysql user instead of using the * wildcard.

2. Create a new service configuration file for `mysqld`

systemctl edit mysqld

Paste the following contents:

[Service]
LimitNOFILE=infinity

Using infinity will simply inherit from the system. You may specify any value between your soft and hard limits.

3. Reload systemd configuration units

systemctl daemon-reload

That's it! Restart MySQL and the new open_files_limit value should appear when querying:

mysql> SELECT @@open_files_limit;
+--------------------+
| @@open_files_limit |
+--------------------+
|              65535 |
+--------------------+

These changes will also persist if you restart the machine or if MySQL is updated, as the settings in the service configuration file will not be overwritten (whereas the main service configuration file will).

Other guides also recommended creating a new service configuration file but some system differences lead to different spelling (systemctl edit mysql versus systemctl edit mysqld), which was my core frustration. Additionally, you do not need to modify my.cnf to specify the open_files_limit there as MySQL will inherit from the system. I feel this solution is the simplest and requires the least amount of modification to files.

Hopefully this works for you if you're having trouble getting this setting to stick!

DEV Community: Giorgio Sintichakis