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

Symptoms

• An error message related to MySQL/MariaDB 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:

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

  ---

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

  ---

  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`

  ---

  [ERROR] Error in accept: Too many open files

  ---

  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/MariaDB service goes down periodically.

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

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

  ---

  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:

  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/MariaDB 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.