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 it.
Note:`mysqld or mysql or mariadb` in this command is selected depending on the name of the running service name on the server.
2. Edit the file using the following command
Note:`mysqld or mysql or mariadb` in this command is selected depending on the name of the running service name on the server.
2. Edit the file using the following command
Use only mysqld or mysql or mariadb according the name of the service on your server
systemctl edit [mysqld|mysql|mariadb]
2.1 Get recommended LimitNOFILE from Releem Dashboard or calculate the LimitNOFILE according to this formula:
LimitNOFILE=10 + [max_connections] + ([table_open_cache] * 2)
[max_connections] and [table_open_cache] please get from the recommended configuration in the Releem Dashboard.
For example:
If [max_connections]=151 and [table_open_cache]=2048 then
LimitNOFILE=4257
2.2 Add the following text to the file and save it:
LimitNOFILE=10 + [max_connections] + ([table_open_cache] * 2)
[max_connections] and [table_open_cache] please get from the recommended configuration in the Releem Dashboard.
For example:
If [max_connections]=151 and [table_open_cache]=2048 then
LimitNOFILE=4257
2.2 Add the following text to the file and save it:
Please replace <calculated_value> with your value
[Service]
LimitNOFILE=<calculated_value>
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 run the following SQL Query to check that the value was set correctly:
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)
Troubleshooting / Frequently Asked Questions
1. For CloudLinux cPanel & WHM Users
Ensure the option "Allow cPanel & WHM to determine the best value for your MySQL open_files_limit configuration" is disabled in WHM >> Tweak Settings.
2. If LimitNOFILE > 1048576
Please edit the file /etc/sysctl.conf and add the following line at the end:
Ensure the option "Allow cPanel & WHM to determine the best value for your MySQL open_files_limit configuration" is disabled in WHM >> Tweak Settings.
2. If LimitNOFILE > 1048576
Please edit the file /etc/sysctl.conf and add the following line at the end:
Please replace <LimitNOFILE_value> with your value
fs.nr_open=<LimitNOFILE_value>
After that execute the command: sysctl -p
Next, restart MySQL/MariaDB
and check that open_files_limit was set correctly:
Next, restart MySQL/MariaDB
and check that open_files_limit was set correctly:
SHOW VARIABLES LIKE 'open_files_limit';