Resolved Debian 8.6 Jessie + Plesk 12.5.30 problem MariaDB "[ERROR] Error in accept: Too many open files"

[David Abuin Fontan]

Today when I upgraded to the latest version MariaDB server has stopped responding to requests to display the log've seen this error:

Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.column_stats' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.event' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.func' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.help_category' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.help_keyword' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.help_relation' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.help_topic' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.index_stats' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.plugin' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.proc' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.table_stats' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.time_zone' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.time_zone_leap_second' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.time_zone_name' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.time_zone_transition' doesn't exist
Sep 18 01:02:47 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'mysql.time_zone_transition_type' doesn't exist
Sep 18 01:02:48 s-ares mysql[20549]: ERROR 1146 (42S02) at line 1: Table 'victoregs_EGSBD.lethe_campaign_groups' doesn't exist
Sep 18 01:02:50 s-ares mysqld: 160918  1:02:50 [ERROR] Error in accept: Too many open files
Sep 18 01:06:44 s-ares systemd[1]: Stopping LSB: Start and stop the mysql database server daemon...
Sep 18 01:07:06 s-ares mysqld: 160918  1:07:06 [ERROR] Error in accept: Too many open files

looking for google I have seen that this is a problem that must be 'open_files_limit' increase its value in systemd.

I doing the steps in the guide https://kb.plesk.com/en/128643 see that does not work for debian jessie

Create a new directory for MariaDB service changes:

# mkdir -p /etc/systemd/system/mariadb.service.d/
# touch /etc/systemd/system/mariadb.service.d/limits.conf
Configure systemd open files limit for MariaDB.

# cat /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=1024000
Restart MariaDB and daemon loader:

#systemctl daemon-reload

#systemctl restart mariadb <--- Not found (is /etc/init.d/mysql restart)

i check and:

root@s-ares:~# mysql -uadmin -p`cat /etc/psa/.psa.shadow` mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7280
Server version: 10.0.27-MariaDB-0+deb8u1 (Debian)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mysql]> SHOW VARIABLES LIKE 'Open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

Someone can give me a step by step guide

Thanks!

 

[IgorG]

Check this article, maybe there is sysltem limit - https://mariadb.com/kb/en/mariadb/server-system-variables/#open_files_limit

 

[David Abuin Fontan]

Debian Jessie does not work, the problem persists:

root@s-ares:~# cat /proc/$(cat /var/run/mysqld/mysqld.pid)/limits | grep files
Max open files            1024                 4096                 files

root@s-ares:~# cat /etc/systemd/system/mysqld.service.d/filelimit.conf
[service]
LimitNOFILE=infinity

root@s-ares:~# cat /etc/security/limits.conf | grep mysql
mysql soft nofile 65535
mysql hard nofile 65535

Help pls

 

[Bitpalast]

You need to change MariaDB/MySQL limits, but you need to change sysctl.conf and limits.conf and restart systemctl afterwards, too.

Assume you want to set the max to 150.000 handles. Example instructions:

Increase the general maximum file descriptor value. Edit /etc/sysctl.conf, add/modify:
fs.file-max = 150000
This is a general system limit, it should probably be set higher than what you need for MariaDB, because other services need handles, too.

Edit /etc/security/limits.conf and add:
mysql soft nofile 150000
mysql hard nofile 150000

Increase the configuration of "open_files_limit" in MariaDB:
# mkdir -p /etc/systemd/system/mariadb.service.d/
# cd /etc/systemd/system/mariadb.service.d/

Edit/create limits.conf, enter:
[Service]
LimitNOFILE=150000

Restart system daemon:
# systemctl --system daemon-reload
# sysctl -p

Restart MariaDB:
service mariadb restart

Verify in SQL interface (Tools>Settings>Database Servers>tools icon in the far right column; then “SQL”) that the new settings are in effect:
show variables like 'open_files_limit';

 

[David Abuin Fontan]

You need to change MariaDB/MySQL limits, but you need to change sysctl.conf and limits.conf and restart systemctl afterwards, too.

Assume you want to set the max to 150.000 handles. Example instructions:

Increase the general maximum file descriptor value. Edit /etc/sysctl.conf, add/modify:
fs.file-max = 150000
This is a general system limit, it should probably be set higher than what you need for MariaDB, because other services need handles, too.

Edit /etc/security/limits.conf and add:
mysql soft nofile 150000
mysql hard nofile 150000

Increase the configuration of "open_files_limit" in MariaDB:
# mkdir -p /etc/systemd/system/mariadb.service.d/
# cd /etc/systemd/system/mariadb.service.d/

Edit/create limits.conf, enter:
[Service]
LimitNOFILE=150000

Restart system daemon:
# systemctl --system daemon-reload
# sysctl -p

Restart MariaDB:
service mariadb restart

Verify in SQL interface (Tools>Settings>Database Servers>tools icon in the far right column; then “SQL”) that the new settings are in effect:
show variables like 'open_files_limit';

Your solution does not work on debian jessie, i tried looking at my first post. Thanks.


PD: Please post tested solutions Debian Jessie

 

[Bitpalast]

I am sorry that my post does not perfectly match your operating system type and version. There are too many os/version combinations out in the wild, that I cannot possibly know each and every single specific configuration. My post was meant as an example of the steps that you need to take in order to solve your issue. Unfortunately, I am unable to serve your individual solution on a plate.

You could simply adapt the concepts of the example to Debian, as your issue of too many open file handles is not caused by Plesk but by a misconfiguration of your operating system for the high resource usage you require from your operating system. You can also search on Google to find out how to increase open file handles in your operating system environment. Your issue of too many open file handles burns down to being an operating system issue, so if you cannot adapt the example given by yourself I suggest you request support in a Debian forum or from a Debian expert.

 

[David Abuin Fontan]

I am sorry that my post does not perfectly match your operating system type and version. There are too many os/version combinations out in the wild, that I cannot possibly know each and every single specific configuration. My post was meant as an example of the steps that you need to take in order to solve your issue. Unfortunately, I am unable to serve your individual solution on a plate.

You could simply adapt the concepts of the example to Debian, as your issue of too many open file handles is not caused by Plesk but by a misconfiguration of your operating system for the high resource usage you require from your operating system. You can also search on Google to find out how to increase open file handles in your operating system environment. Your issue of too many open file handles burns down to being an operating system issue, so if you cannot adapt the example given by yourself I suggest you request support in a Debian forum or from a Debian expert.

Thank you very much for your help , I've been searching google but none of the solutions are for this version of Debian.

 

[UFHH01]

Hi David Abuin Fontan,

your Maria-DB - version needs an upgrade, because your issue is a bug, fixed in versions, higher than "10.1.10" for Maria-DB.

Consider to use an official Maria-DB - mirror in your "sources.list" for apt-get, to make it easier for you, to update/upgrade/patch your used Maria-DB-server:

Example ( or use "https://downloads.mariadb.org/mariadb/repositories/" to create your own, unique sources.list - entry! ):

Check your current installed version and see if you can upgrade without adding additional sources:

apt-cache policy mariadb-server​

If you would like to add an additional source, you could use for example:

apt-get install software-properties-common
apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
add-apt-repository 'deb [arch=amd64,i386] http://ftp.hosteurope.de/mirror/mariadb.org/repo/10.1/debian jessie main'

... followed by:

apt-get update && apt-get install mariadb-server​

​

 

[David Abuin Fontan]

SOLVED for Debian 8.x:

nano /etc/security/limits.conf

Copy & paste:

mysql soft nofile 65535
mysql hard nofile 65535

reboot

in Debian Jessie is mysql.service.d (don't mariadb.service.d or mysqld.service.d)

mkdir -p /etc/systemd/system/mysql.service.d/
nano /etc/systemd/system/mysql.service.d/limits.conf

Copy & paste:

[Service]
LimitNOFILE=infinity

systemctl daemon-reload
/etc/init.d/mysql restart

Check:

cat /proc/$(cat /var/run/mysqld/mysqld.pid)/limits

Max open files 65536 65536 files

Thanks for all!!!

 

[9musashi]

SOLVED for Debian 8.x:

nano /etc/security/limits.conf

Copy & paste:

mysql soft nofile 65535
mysql hard nofile 65535

reboot

in Debian Jessie is mysql.service.d (don't mariadb.service.d or mysqld.service.d)

mkdir -p /etc/systemd/system/mysql.service.d/
nano /etc/systemd/system/mysql.service.d/limits.conf

Copy & paste:

[Service]
LimitNOFILE=infinity

systemctl daemon-reload
/etc/init.d/mysql restart

Check:

cat /proc/$(cat /var/run/mysqld/mysqld.pid)/limits

Thanks for all!!!

Thank you I folllow your instructions and solves the problem (hopefully - its still just 30 minutes ago .. )