MySQL values open_files_limit and max_connections are not applied – Plesk Help Center

Applicable to:

Plesk for Linux

Symptoms

The value of max_connections changes to 214 automatically after some time, although the value itself is defined in my.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:

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

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.

Note: This article may require additional administrative knowledge to apply. If any help required, contact server’s administrator or hosting support.

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, or mysqld.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:

CONFIG_TEXT: [Service]
LimitNOFILE=4096

Note: 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:

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.cnf and under the [mysqld] section:

CONFIG_TEXT: open_files_limit = 4096
Restart the MySQL service:

# service mysql restart

For RHEL-based distributions:

Add the following line in /etc/sysctl.conf:

CONFIG_TEXT: fs.file-max = 65536
Edit the file /etc/security/limits.conf and 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.conf and add lines:

CONFIG_TEXT: * 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:

CONFIG_TEXT: open_files_limit = 4096
Restart the MySQL service:

# service mariadb restart

Comments

3 comments

nisamudeen plackal May 19, 2017 00:47

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.

0

Andrey Ivanov May 24, 2017 13:39

Hello nisamudeen,

Thank you for your reply! A section regarding systemd systems was added accordingly.

0

sanel kape July 07, 2017 13:36

Yes it work,after setting params in /etc/systemd/system/mysql.service ,this is for newer ubuntu versions

0

Please sign in to leave a comment.

Have more questions? Submit a request

Articles in this section