Applicable to:
- Plesk for Linux
Symptoms
-
The value of
max_connectionschanges to214automatically 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'"
| max_connections | 214 -
The value of
open_files_limitis reset after system updates are installed. -
In the log file
/var/log/mysqld.logthe following records can be found:CONFIG_TEXT: [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
Cause
open_files_limit for MySQL has been reached.
Resolution
Connect to the server via SSH and adjust open_files_limit for the MySQL service. For that, check the current value:
# plesk db "SHOW GLOBAL VARIABLES LIKE 'open_files_limit'"
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+
On systemd systems:
- Create the directory and the file
/lib/systemd/system/mysql.service.d/limit_nofile.confwith configuration lines:
# mkdir -p /lib/systemd/system/mysql.service.d/
# vi /lib/systemd/system/mysql.service.d/limit_nofile.conf
[Service]
LimitNOFILE=4096 -
Reload system daemon and restart MySQL service:
# systemctl daemon-reload
# systemctl restart mysql
- Create the directory and the file
/usr/lib/systemd/system/mariadb.service.d/limit_nofile.confwith configuration lines:
# mkdir -p /usr/lib/systemd/system/mariadb.service.d/
# vi /usr/lib/systemd/system/mariadb.service.d/limit_nofile.conf
[Service]
LimitNOFILE=4096 - Reload system daemon and restart MySQL service:
# systemctl daemon-reload
# systemctl restart mariadb
On System V systems:
-
Add the following lines in
/etc/security/limits.conf:CONFIG_TEXT: mysql soft nofile 4096
mysql hard nofile 4096 -
Add the following to the end of file into
/etc/pam.d/common-session:CONFIG_TEXT: session required pam_limits.so
-
Add the following to the end of file into
/etc/pam.d/common-session-noninteractive:CONFIG_TEXT: session required pam_limits.so
-
Add the following line in
/etc/mysql/my.cnfand under the[mysqld]section:CONFIG_TEXT: open_files_limit = 4096
-
Restart the MySQL service:
# service mysql restart
-
Add the following line in
/etc/sysctl.conf:CONFIG_TEXT: fs.file-max = 65536
-
Edit the file /
etc/security/limits.confand add lines:CONFIG_TEXT: * soft nproc 40960
* hard nproc 40960
* soft nofile 40960
* hard nofile 40960 -
Edit the file
/etc/security/limits.d/90-nproc.confand add lines:CONFIG_TEXT: * soft nproc 40960
* hard nproc 40960
* soft nofile 40960
* hard nofile 40960 -
Add the following line in
/etc/my.cnfand under the[mysqld]section:CONFIG_TEXT: open_files_limit = 4096
-
Restart the MySQL service:
# service mariadb restart
Comments
3 comments
Ubuntu has moved from Upstart to Systemd from version 15.04 and no longer respects the limits in /etc/security/limits.conf for system services. These limits now apply only to user sessions.
The limits for the MySQL service are defined in the Systemd configuration file, which you should copy from its default location into /etc/systemd and then edit the copy.
sudo cp /lib/systemd/system/mysql.service /etc/systemd/system/
sudo vim /etc/systemd/system/mysql.service # or your editor of choice
Add the following lines to the bottom of the file:
LimitNOFILE=infinity
LimitMEMLOCK=infinity
You could also set a numeric limit, eg LimitNOFILE=4096
Now reload the Systemd configuration with:
sudo systemctl daemon-reload
Restart MySQL and it should now obey the max_connections directive.
Hello nisamudeen,
Thank you for your reply! A section regarding systemd systems was added accordingly.
Yes it work,after setting params in /etc/systemd/system/mysql.service ,this is for newer ubuntu versions
Please sign in to leave a comment.