This is always an interesting one, comes up from time to time especially if you run a server with heavy database traffic.
Possibly you’ve modified you my.cnf with various tweaked settings for ‘max_connections’ or ‘open_files_limit’ but your your ‘mysqld.log’ you start getting:
[Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
[Warning] Buffered warning: Changed limits: max_connections: 214 (requested 800)
[Warning] Buffered warning: Changed limits: table_open_cache: 400 (requested 2000)
What gives ? Well the values of various settings get reset after system updates are installed.
So…
On systemd systems:
- Create
limit_nofile.conf
file:- For Debian-based distributions:Create
/lib/systemd/system/mysql.service.d/limit_nofile.conf
file with the following content:# cat /lib/systemd/system/mysql.service.d/limit_nofile.conf [Service] LimitNOFILE=4096
- For RHEL-based distributions:Create
/usr/lib/systemd/system/mariadb.service.d/limit_nofile.conf
file with the following content:# cat /usr/lib/systemd/system/mariadb.service.d/limit_nofile.conf [Service] LimitNOFILE=4096
- For Debian-based distributions:Create
- Reload daemons and restart MySQL service:
# systemctl daemon-reload # systemctl restart mysql
On System V systems:
- For Debian-based distributions:
- Add the following lines in
/etc/security/limits.conf
:mysql soft nofile 4096 mysql hard nofile 4096
- Add the following to the end of file into
/etc/pam.d/common-session
:session required pam_limits.so
- Add the following to the end of file into
/etc/pam.d/common-session-noninteractive
:session required pam_limits.so
- Add the following line in
/etc/mysql/my.cnf
and under the[mysqld]
section:open_files_limit = 4096
- Restart the MySQL service.
- Add the following lines in
- For RHEL-based distributions:
- Add the following line in
/etc/sysctl.conf
:fs.file-max = 65536
- Edit the file /
etc/security/limits.conf
and add lines:* soft nproc 40960 * hard nproc 40960 * soft nofile 40960 * hard nofile 40960
- Edit the file
/etc/security/limits.d/90-nproc.conf
and add lines:* soft nproc 40960 * hard nproc 40960 * soft nofile 40960 * hard nofile 40960
- Add the following line in
/etc/my.cnf
and under the[mysqld]
section:open_files_limit = 4096
- Restart the MySQL service.
- Add the following line in