Setting 'open_files_limit' in Percona 5.7 and CentOS 7.5
Tags: percona, centos, openfileslimit, systemd
Published: 2018-10-16 21:57:49

---
title: Setting 'open_files_limit' in Percona 5.7 and CentOS 7.5
published: true
description: Quick guide to get this tricky variable to stick
tags: percona, centos, open_files_limit, systemd
cover_image: https://thepracticaldev.s3.amazonaws.com/i/vncvd71cuxckuvd2l9fc.png
---

# 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](https://www.percona.com/blog/2017/10/12/open_files_limit-mystery/), 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
```

<sub>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.</sub>


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

```
systemctl edit mysqld
```

Paste the following contents:

```
[Service]
LimitNOFILE=infinity
```

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

### 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!