Symptoms
-
The value of
max_connections
changes to214
automatically after some time, although the value itself is defined inmy.cnf
:# grep -i ‘max_connections’ /etc/my.cnf
max_connections=2048# plesk db “SHOW GLOBAL VARIABLES LIKE ‘max_connections’ G”
*************************** 1. row ***************************
Variable_name: max_connections
Value: 214 -
The value of
open_files_limit
is reset after system updates are installed. -
In the log file
/var/log/mysqld.log
the following records can be found:[Warning] Changed limits: max_open_files: 1024 max_connections: 214 table_cache: 400
-
When restoring a MySQL backup, the following error is shown:
Warning: mysql “databasename”
Not all the data was backed up into […]/databasename_1 successfully. mysqldump: Got error: 23: Out of resources when opening file ‘[…]’ (Errcode: 24 – Too many open files) when using LOCK TABLES - Unable to export database in Plesk:
PLESK_ERROR: Unable to export a dump of databasename:
mysqldump: Got error: 1016: “Can’t open file: ‘./databasename/table.frm’ (errno: 24)” when using LOCK TABLES
Cause
Default open_files_limit
for MySQL has been reached.
Resolution
Connect to the server via SSH and adjust open_files_limit
for the MySQL service as below.
On systemd-based systems (RHEL/CentOS/CloudLunix 7 and above, Ubuntu 16.04 and above, Debian 8 and above)
-
Find out whether MySQL, or MariaDB service is used:
# systemctl list-unit-files | grep -E ‘mysql|mariadb’
mariadb.service enabled -
Create an override for the service file from the previous step:
# systemctl edit mariadb.service
Note: In case
mysql.service
, ormysqld.service
was displayed on the previous step, replace “mariadb.service
” with it. -
Add the following content to the opened text editor and save the file:
[Service]
LimitNOFILE=4096Note: Value might be increased if required, 4096 is only a suggested value.
-
Restart the MySQL/MariaDB server:
# systemctl restart mysql mysqld mariadb 2>/dev/null
On System V/Upstart systems (Ubuntu 14.04, RHEL/CentOS/CloudLinux 6)
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:
# service mysql restart
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:
# service mariadb restart