MySQL values open_files_limit and max_connections are not applied

Created:

2016-11-16 13:06:08 UTC

Modified:

2017-07-28 06:31:48 UTC

6

Was this article helpful?


Have more questions?

Submit a request

MySQL values open_files_limit and max_connections are not applied

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'"
    | max_connections | 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

Cause

open_files_limit for MySQL has been reached.

Resolution

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:

  1. 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
  2. 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:

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

      fs.file-max = 65536
    2. Edit the file /etc/security/limits.conf and add lines:

      *          soft     nproc          40960
      * hard nproc 40960
      * soft nofile 40960
      * hard nofile 40960
    3. 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
    4. Add the following line in /etc/my.cnf and under the [mysqld] section:

      open_files_limit = 4096
    5. Restart the MySQL service.

Have more questions? Submit a request

2 Comments

  • 0
    Avatar
    nisamudeen plackal

    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
    Avatar
    Andrey Ivanov

    Hello nisamudeen,

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

Please sign in to leave a comment.