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:
<span class="pun">[</span><span class="pln">Warning</span><span class="pun">]</span><span class="pln"> Buffered warning</span><span class="pun">:</span><span class="pln"> Changed limits</span><span class="pun">:</span><span class="pln"> max_open_files</span><span class="pun">:</span> <span class="lit">1024</span> <span class="pun">(</span><span class="pln">requested </span><span class="lit">5000</span><span class="pun">)</span><br />
<span class="pun">[</span><span class="pln">Warning</span><span class="pun">]</span><span class="pln"> Buffered warning</span><span class="pun">:</span><span class="pln"> Changed limits</span><span class="pun">:</span><span class="pln"> max_connections</span><span class="pun">:</span> <span class="lit">214</span> <span class="pun">(</span><span class="pln">requested </span><span class="lit">800</span><span class="pun">)</span><br />
<span class="pun">[</span><span class="pln">Warning</span><span class="pun">]</span><span class="pln"> Buffered warning</span><span class="pun">:</span><span class="pln"> Changed limits</span><span class="pun">:</span><span class="pln"> table_open_cache</span><span class="pun">:</span> <span class="lit">400</span> <span class="pun">(</span><span class="pln">requested </span><span class="lit">2000</span><span class="pun">)</span>
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:
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 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</pre>
3. Edit the file `/etc/security/limits.d/90-nproc.conf` and add lines:
<pre>* soft nproc 40960
hard nproc 40960
soft nofile 40960
hard nofile 40960</pre>
4. Add the following line in `/etc/my.cnf` and under the `[mysqld]` section:
<pre>open_files_limit = 4096</pre>
5. Restart the MySQL service.