i noticed that (starting with end of november) some days where missing in awstats at most of the websites, they show 0 accesses for this day. it looks like every few days awstats is not running correctly instead it aborts, reporting a mysql-error: Code: Dec 13 01:47:12 mail-nibbler php: PHP Fatal error: Uncaught mysqli_sql_exception: MySQL server has gone away in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php:270 Dec 13 01:47:12 mail-nibbler php: #0 /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php(270): mysqli_ping(Object(mysqli)) Dec 13 01:47:12 mail-nibbler php: #1 /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php(339): db->_query('SELECT domain F...', '298') Dec 13 01:47:12 mail-nibbler php: #2 /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php(395): db->query('SELECT domain F...', '298') Dec 13 01:47:12 mail-nibbler php: #3 /usr/local/ispconfig/server/lib/classes/cron.d/150-awstats.inc.php(99): db->queryAllRecords('SELECT domain F...', '298') Dec 13 01:47:12 mail-nibbler php: #4 /usr/local/ispconfig/server/lib/classes/cronjob.inc.php(90): cronjob_awstats->onRunJob() Dec 13 01:47:12 mail-nibbler php: #5 /usr/local/ispconfig/server/cron.php(116): cronjob->run() Dec 13 01:47:12 mail-nibbler php: #6 {main} Dec 13 01:47:12 mail-nibbler php: thrown in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php on line 270 i never noticed any problems with the database but a handfull of slow queries were logged everyday, but after some db-cleanup the slow queries are gone. raising the mysql variable connect_timeout to 60 did not help and max_clients was never reached. i am running out of ideas, the server is running stable for quiet a long time, no big changes made recently, just regular updates. my system: Code: ISPConfig version is 3.2.9 lsb_release -a No LSB modules are available. Distributor ID: Debian Description: Debian GNU/Linux 10 (buster) Release: 10 Codename: buster php -v PHP 7.3.33-8+0~20221028.101+debian10~1.gbpb248c7 (cli) (built: Oct 28 2022 18:39:13) ( NTS ) Copyright (c) 1997-2018 The PHP Group Zend Engine v3.3.33, Copyright (c) 1998-2018 Zend Technologies with Zend OPcache v7.3.33-8+0~20221028.101+debian10~1.gbpb248c7, Copyright (c) 1999-2018, by Zend Technologies mysqltuner shows: Code: >> MySQLTuner 2.0.9 * Jean-Marie Renouard <[email protected]> * Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.pl/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Logged in using credentials from Debian maintenance account. [OK] Currently running supported MySQL version 10.3.36-MariaDB-0+deb10u2-log [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [OK] Log file /var/log/mysql/error.log exists [--] Log file: /var/log/mysql/error.log (67K) [OK] Log file /var/log/mysql/error.log is not empty [OK] Log file /var/log/mysql/error.log is smaller than 32 Mb [OK] Log file /var/log/mysql/error.log is readable. [!!] /var/log/mysql/error.log contains 416 warning(s). [!!] /var/log/mysql/error.log contains 413 error(s). [--] 0 start(s) detected in /var/log/mysql/error.log [--] 0 shutdown(s) detected in /var/log/mysql/error.log -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in InnoDB tables: 4.6G (Tables: 5954) [--] Data in MyISAM tables: 669.1M (Tables: 1369) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Views Metrics ----------------------------------------------------------------------------- -------- Triggers Metrics -------------------------------------------------------------------------- -------- Routines Metrics -------------------------------------------------------------------------- -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [--] There are 620 basic passwords in the list. -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 7d 19h 8m 8s (82M q [123.116 qps], 1M conn, TX: 286G, RX: 25G) [--] Reads / Writes: 95% / 5% [--] Binary logging is disabled [--] Physical Memory : 125.8G [--] Max MySQL memory : 59.5G [--] Other process memory: 0B [--] Total buffers: 8.7G global + 259.6M per thread (200 max threads) [--] Performance_schema Max memory usage: 112M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 33.1G (26.34% of installed RAM) [OK] Maximum possible memory usage: 59.5G (47.29% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (1/82M) [OK] Highest usage of available connections: 48% (96/200) [OK] Aborted connections: 0.00% (0/1092860) [!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 11M sorts) [!!] Joins performed without indexes: 652534 [OK] Temporary tables created on disk: 18% (1M on disk / 6M total) [OK] Thread cache hit rate: 95% (50K created / 1M connections) [OK] Table cache hit rate: 89% (137M hits / 154M requests) [OK] table_definition_cache (8000) is greater than number of tables (7484) [OK] Open file limit used: 0% (378/60K) [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance_schema is activated. [--] Memory used by Performance_schema: 112.6M [--] Sys schema is not installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.3% (93.4M used / 512.0M cache) [OK] Key buffer size / total MyISAM indexes: 512.0M/91.7M [OK] Read Key buffer hit rate: 99.3% (36M cached / 262K reads) [!!] Write Key buffer hit rate: 9.0% (5M cached / 499K writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 8.0G / 4.6G [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/8.0G should be equal to 25% [OK] InnoDB buffer pool instances: 8 [--] Number of InnoDB Buffer Pool Chunk: 64 for 8 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 100.00% (17257112982 hits / 17257362769 total) [OK] InnoDB Write log efficiency: 98.30% (29695552 hits / 30210140 total) [OK] InnoDB log waits: 0.00% (0 waits / 514588 writes) -------- Aria Metrics ------------------------------------------------------------------------------ [--] Aria Storage Engine is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/0B [OK] Aria pagecache hit rate: 98.4% (50M cached / 824K reads) -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: MIXED [--] XA support enabled: ON [--] Semi synchronous replication Master: OFF [--] Semi synchronous replication Slave: OFF [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Check warning line(s) in /var/log/mysql/error.log file Check error line(s) in /var/log/mysql/error.log file Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found. See https://dev.mysql.com/doc/internals/en/join-buffer-size.html (specially the conclusions at the bottom of the page). Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL Variables to adjust: skip-name-resolve=1 join_buffer_size (> 1.0M, or always use indexes with JOINs) key_buffer_size (~ 98M) for more details see attached htf_report.txt
One other Idea that comes to my mind besides what I mentioned in your issue report is that the reason might be related to MariaDB closing the connection too early due to inactivity. Let's assume some of your sites got more traffic so that processing stats may take longer. While AWStats is processing the log files, no further MySQL queries will be made by ISPConfig, and maybe MariaDB will shut down the connection in the meantime. So you might want to check the timeout for MariaDB to shutdown inactive connections is and if raising it solves the issue.
ahh, you are a true hero! i remember a very long time ago i had a problem of hitting max_connections in mysql and lots of idle connections, therefore i reduced wait_timeout down to 1800 - yes, some statistics may take longer than 30min. i never thought that mysql needs to keep an idle connection more than 1800s, usually mysql operations on the same connection are quite frequently - for now i raised the wait_timeout to 7200 - on the other hand, generating statistic can always take some time, wouldn't it be better/possible to close/make a new connection on every domain in this case?
