MySQL Buffered warning: Changed limits: max_connections: 214 (requested 800)

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
```

Reload daemons and restart MySQL service:

# systemctl daemon-reload
# systemctl restart mysql

On System V systems:

For Debian-based distributions:
1. Add the following lines in /etc/security/limits.conf:
```
mysql             soft    nofile           4096
mysql             hard    nofile           4096
```
2. Add the following to the end of file into /etc/pam.d/common-session :
```
session required pam_limits.so
```
3. Add the following to the end of file into /etc/pam.d/common-session-noninteractive:
```
session required pam_limits.so
```
4. Add the following line in /etc/mysql/my.cnf and under the [mysqld] section:
```
open_files_limit = 4096
```
5. Restart the MySQL service.

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.

Leave a Reply Cancel reply