Articles in this section

See more

MySQL on the server with Plesk shows the error: Too many open files

Avatar
Kuzma Ivanov
Updated
Follow
Plesk for Linux kb: technical ABT: Group B

Applicable to:

  • Plesk for Linux

Symptoms

  • An error message related to MySQL service appears in one of these log files:

    • /var/log/plesk/panel.log

    • /var/log/mysql/error.log

    • /var/log/mariadb/mariadb.log

    • /var/log/syslog

    and looks as follows:

    CONFIG_TEXT: Error: SQLSTATE[HY000]: General error: 23 Out of resources when opening file '/tmp/#***.MYD' (Errcode: 24 - Too many open files)

    CONFIG_TEXT: [ERROR] /usr/sbin/mysqld: Can't open file: './usr_web587_1/etqvi_languages.frm' (errno: 24 - Too many open files)

    CONFIG_TEXT: Failed to process database 'wordpress_f' from domain 'example.com': MySQL error 1018: Can't read dir of './wordpress_f/' (errno: 24 "Too many open files") executing query: SHOW TABLE STATUS FROM `wordpress_f`

    CONFIG_TEXT: [ERROR] Error in accept: Too many open files

    CONFIG_TEXT: DB query failed: SQLSTATE[HY000]: General error: 1016 Can't open file: './psa/misc.frm' (errno: 24), query was: select param, val from misc

  • Disk usage statistics in Plesk is not calculated with one of the errors above.

  • MySQL service goes down periodically.

  • Plesk fails to load or operations with MySQL databases (for example backup) fail with one of the following error messages:

    PLESK_INFO: ERROR: PleskDBException: Unable to connect to database: mysql_connect(): MySQL server has gone away (Error code: 2006) (Abstract.php:69)

    PLESK_INFO: DB query failed: SQLSTATE[HY000]: General error: 23 Out of resources when opening file '/tmp/#sql_2ff_0.MAI' (Errcode: 24 "Too many open files"), query was: DESCRIBE `sessions`

  • A Plesk backup is created with the following warning:

    CONFIG_TEXT: WARNING : (mysql object 'johndoe_opencart_e') Not all the data was backed up into /var/lib/psa/dumps/clients/john_doe/domains/example.com/databases/johndoe_opencart_e_1 successfully. mysqldump: Couldn't execute 'show table status like 'address'': Can't read dir of './john_doe_opencart_e/' (errno: 24 - Too many open files) (1018)

Cause

The open files limit has been reached by the MySQL service. When MySQL fails to open required files, the process gets hanged and systemd fails to stop MySQL process.

Resolution

Warning: Increasing the open file limits might affect the amount of used connections. In order to avoid instability, consider increasing it as well by following instructions from this KB article.

Note: systemd operating systems have MariaDB installed by default. If MariaDB has been replaced by MySQL, follow the resolution steps for MySQL.

 

With MariaDB installed

 

  1. Connect to the server via SSH.

  2. Modify MariaDB startup script:

    # systemctl edit mariadb

  3. Set open files limit for the MariaDB service:

    CONFIG_TEXT: [Service]
    LimitNOFILE=8192

  4. Restart the MariaDB service:

    # systemctl restart mariadb

  5. Verify the changes:

    # egrep "open files" /proc/$(cat `plesk db -Ne "show variables like 'pid_file'" | awk '{print $2}'`)/limits
    Max open files 8192 8192 files

 

With MySQL installed

 

  1. Connect to the server via SSH.

  2. Modify MySQL startup script:

    # systemctl edit mysql

  3. Set open files limit for the MySQL service:

    CONFIG_TEXT: [Service]
    LimitNOFILE=8192

  4. Restart the MySQL service:

    # systemctl restart mysql

  5. Verify the changes:

    # egrep "open files" /proc/$(cat `plesk db -Ne "show variables like 'pid_file'" | awk '{print $2}'`)/limits
    Max open files 8192 8192 files

 

Comments

9 comments

Sort by Date Votes
  • Avatar
    Marcio Nunes

    Dont Work! Ubuntu 16

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    @Marcio Nunes

    Hello!
    Please, could you share the details of the issue?
    Did you have trouble with particular step?
    For deeper investigation, I can suggest contacting Plesk Technical Support.

    0
    Comment actions Permalink
  • Avatar
    Arvind Kumar Madhukar

    Dear Respective !

    I'm very glad to say that, the steps you have shared us with on the URL > https://support.plesk.com/hc/en-us/articles/213393029-MySQL-values-open-files-limit-and-max-connections-are-not-applied is perfect.
    But same steps, if i follow on CloudLinux OS it is not giving any error not we are able to see the changes regarding the open_files_limit, it would be very thankful if you could work on this and let us know the way to get resolve this issue.

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello @Arvind,

    This issue will be investigated in the scope of a ticket.

    0
    Comment actions Permalink
  • Avatar
    Akshay

    Hello Team !

    @Plesk Suport Reply as below.

    I have noticed that the MySQL service changes were not loaded by systemd and therefore not applied:

    # systemctl status mariadb >/dev/null
    Warning: mysqld.service changed on disk. Run 'systemctl daemon-reload' to reload units.

    They were also not synced when the systemctl daemon-reload command was executed. Most likely it is due to the fact that the MariaDB service is a symlink to the MySQL:

    # ls -la /usr/lib/systemd/system/mariadb.service
    lrwxrwxrwx 1 root root 38 Sep 16 01:02 /usr/lib/systemd/system/mariadb.service -> /usr/lib/systemd/system/mysqld.service

    Therefore, I have moved the /usr/lib/systemd/system/mariadb.service.d and /etc/systemd/system/mariadb.service.d directories to /root and created an override using systemctl edit mysql.service command.

    After the override was created, limits were applied and after the MySQL server was restarted, proper limits were active:

    # plesk db "SHOW GLOBAL VARIABLES LIKE 'open_files_limit' \G"
    *************************** 1. row ***************************
    Variable_name: open_files_limit
            Value: 8192
    0
    Comment actions Permalink
  • Avatar
    Akshay

    Our issue was resolved.

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello @Arvind,

    Glad to hear that now it is resolved.

    The resolution may be useful for other Pleskians.

    0
    Comment actions Permalink
  • Avatar
    MarkC (Edited )

    Obsidian 18.0.28 and Debian 8.11 here. Command "systemctl edit" doesn't work:

    systemctl edit mariadb
    Unknown operation 'edit'.

    Probably I need a dist-upgrade.

    0
    Comment actions Permalink
  • Avatar
    Kuzma Ivanov

    Hi MarkC,

    You are right, Debian 8 does not support this option. It has also reached its end-of-life and is no longer supported.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles