How to increase open_files_limit
Complete list of steps to increase open_files_limit and prevent limiting MySQL variables
During MySQL tuning sometimes you may have a problem increasing variable table_open_cache / max_connections value. It seems like you apply new configuration but Releem tell you that variable value hasn't changed.
The root cause of that issue is in limitations of Operating System on opened files.
To fix this problem please do the following steps:
1. Find out if any other .conf files are being used with MySQL that overrides the values for open limits. Run `systemctl status mysqld/mysql/mariadb` command and it will show something like this
The root cause of that issue is in limitations of Operating System on opened files.
To fix this problem please do the following steps:
1. Find out if any other .conf files are being used with MySQL that overrides the values for open limits. Run `systemctl status mysqld/mysql/mariadb` command and it will show something like this
Drop-In:
/etc/systemd/system/(mysqld/mysql/mariadb).service.d
└─limits.conf
This means there is `/etc/systemd/system/(mysqld/mysql/mariadb).service.d/limits.conf` file which is loaded with MySQL Server. If this file does not exist, you should create create it.
Note:`mysqld/mysql/mariadb` is selected depending on the name of the running service name on the server, which is also defined in the output of the command `systemctl status mysqld/mysql/mariadb`
2. Edit the file and add the following and change `[table_open_cache]` to your value
Note:`mysqld/mysql/mariadb` is selected depending on the name of the running service name on the server, which is also defined in the output of the command `systemctl status mysqld/mysql/mariadb`
2. Edit the file and add the following and change `[table_open_cache]` to your value
[Service]
LimitNOFILE=10 + [max_connections] + ([table_open_cache] * 2)
3. Run the following command to apply the changes.
systemctl daemon-reload
4. Reboot your MySQL server.
5. After the successful reboot of the server, we will again run below SQL Queries.
SHOW VARIABLES LIKE 'open_files_limit';
You should see the following:
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| open_files_limit | 102400 |
+------------------+--------+
1 row in set (0.00 sec)
