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

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.