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:
CentOS, RedHat, CloudLinux, RockyLinux, AlmaLinux, Oracle Linux
The root cause of that issue is in limitations of Operating System on opened files.
To fix this problem please do the following steps:
- Edit the service file using the following command for your database server distribution and operating system:
CentOS, RedHat, CloudLinux, RockyLinux, AlmaLinux, Oracle Linux
systemctl edit mysqldDebian, Ubuntu
systemctl edit mysqlMariaDB
systemctl edit mariadb1.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
1.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
1.2 Add the following text to the file and save it:
Please replace <calculated_value> with your value
[Service]
LimitNOFILE=<calculated_value>2. Run the following command to apply the changes.
systemctl daemon-reload3. Reboot your MySQL server.
4. 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';