We are experiencing a behavior where all the nodes are being disconnected from the database due to exceeding the database connetions limit. And this all happens on a particular time-slot where we don’t have any purge configured …
Do you guys have experienced something like this? Where the connections are being raising exponentially till the database shuts off?
MariaDB [piwik]> SHOW VARIABLES LIKE “max_connections”;
±----------------±------+
| Variable_name | Value |
±----------------±------+
| max_connections | 2048 |
±----------------±------+
Maria db log:
Version: ‘10.4.10-MariaDB’ socket: ‘/DB/mysql/mysql.sock’ port: 3306 MariaDB Server
2020-01-14 10:03:26 843 [Warning] InnoDB: Cannot add field custom_var_v4 in table piwik.piwik_log_conversion because after adding it,
the row size is 8392 which is greater than maximum allowed size (8126) for a record on index leaf page.
2020-01-14 10:06:00 6185 [Warning] Aborted connection 6185 to db: ‘piwik’ user: ‘matomo’ host: ‘server’ (Got timeout reading communication packets)
Is this value causing the huge amount of time_wait connections?
MariaDB [piwik]> SHOW VARIABLES LIKE ‘wait_timeout’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| wait_timeout | 28800 |
±--------------±------+
1 row in set (0.001 sec)
[mysql@server]$ cat /proc/net/sockstat
sockets: used 513
TCP: inuse 40 orphan 0 tw 1505 alloc 46 mem 9
UDP: inuse 5 mem 4
UDPLITE: inuse 0
RAW: inuse 0
FRAG: inuse 0 memory 0
This is the normal values on a random time slot on the day use:
-bash-4.2$ while true; do netstat -an|grep 3306 |grep TIME_WAIT -wc; sleep 2; done
488
510
533
-bash-4.2$ while true; do netstat -an| grep 3306 |grep ESTABLISHED -wc; sleep 2; done
28
19
28
17
On that particular time-slot where the erros occur the numbers are around 2000 connections
php-error.log
[14-Jan-2020 09:15:55] NOTICE: [pool www] child 58924 exited with code 0 after 1057.058314 seconds from start
[14-Jan-2020 09:15:55] NOTICE: [pool www] child 73525 started
[14-Jan-2020 09:17:01] NOTICE: Terminating …
[14-Jan-2020 09:17:01] NOTICE: exiting, bye-bye!
[14-Jan-2020 09:17:02] NOTICE: fpm is running, pid 73662
[14-Jan-2020 09:17:02] NOTICE: ready to handle connections
[14-Jan-2020 09:17:02] NOTICE: systemd monitor interval set to 10000ms
[14-Jan-2020 09:20:24] WARNING: [pool www] server reached max_children setting (150), consider raising it
php-error.log:
[14-Jan-2020 10:30:21 UTC] Error in Matomo (tracker): Error query: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away In query:
[14-Jan-2020 10:02:47 UTC] Error in Matomo (tracker): SQLSTATE[HY000] [2002] Connection refused
[14-Jan-2020 10:02:47 UTC] Error in Matomo (tracker): SQLSTATE[HY000] [2002] Connection refused
[14-Jan-2020 10:02:47 UTC] Error in Matomo (tracker): SQLSTATE[HY000] [2002] Connection refused
[14-Jan-2020 10:02:47 UTC] Error in Matomo (tracker): SQLSTATE[HY000] [2002] Connection refused
[14-Jan-2020 10:02:47 UTC] Error in Matomo (tracker): SQLSTATE[HY000] [2002] Connection refused
[14-Jan-2020 10:02:47 UTC] Error in Matomo (tracker): SQLSTATE[HY000] [2002] Connection refused
[14-Jan-2020 10:02:47 UTC] Error in Matomo (tracker): SQLSTATE[HY000] [2002] Connection refused
[14-Jan-2020 10:02:47 UTC] Error in Matomo (tracker): SQLSTATE[HY000] [2002] Connection refused
Matomo.log:
ERROR Piwik\Tracker\Handler[2020-01-14 10:30:36 UTC] [40901] Exception: /apps/matomo/matomo/core/Tracker.php(227): SQLSTATE[HY000] [2002] Connection refused