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.
Connect to the server via SSH.
Modify MariaDB startup script:
# systemctl edit mariadb
Set open files limit for the MariaDB service:
[Service]
LimitNOFILE=8192Restart the MariaDB service:
# systemctl restart mariadb
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
Connect to the server via SSH.
Modify MySQL startup script:
# systemctl edit mysql
Set open files limit for the MySQL service:
[Service]
LimitNOFILE=8192Restart the MySQL service:
# systemctl restart mysql
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
