How to change MySQL open_files_limit variable value

Today I wanted to change the value of open_files_limit variable value in mysql but I was not able to do so with setting variable in /etc/my.cnf so I have followed below steps to troubleshoot that issue.

To see currently set value of open_files_limit you can use below command on mysql prompt

MariaDB [(none)]> show global variables like 'open%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 16364 |
+------------------+-------+
1 row in set (0.01 sec)

To change this value I have set below variable in /etc/my.cnf

[mysqld]
open_files_limit = 100000

and restarted mysql service but still mysql was showing limit 16364 after some digging I have found that limit of 16364 was set in file

/usr/lib/systemd/system/mariadb.service

I have edited that file and added below lines

LimitNOFILE=infinity
LimitMEMLOCK=infinity

restarted mysql service

[root@mail system]# service mariadb restart
Redirecting to /bin/systemctl restart mariadb.service
Warning: mariadb.service changed on disk. Run 'systemctl daemon-reload' to reload units.

This should set the value you have mentioned in the my.cnf file.

Leave a Comment