Hallo zusammen,
ich habe aktuell leider das Problem, dass sich unter Matomo 4.15.1 keine Archivierung mehr anstoßen lässt.
Bei dem Server handelt es sich um einen Hetzner Managed Virtual Private Server mit 4 Kernen, 16 GB RAM und SSD, auf dem nur ein kleiner Shop läuft. Die Last liegt in der Regel um die 0.4. Sobald ich die Archivierung über den Cronjob oder den Weblink anstoße, geht die Last auf dem Server teils auf über 6 oder sogar 10 hoch.
Der Matomo Archive Log zeigt die folgenden Einträge:
INFO [2023-08-30 14:00:02] 16319 ---------------------------
INFO [2023-08-30 14:00:02] 16319 INIT
INFO [2023-08-30 14:00:02] 16319 Running Matomo 4.15.1 as Super User
INFO [2023-08-30 14:00:02] 16319 ---------------------------
INFO [2023-08-30 14:00:02] 16319 NOTES
INFO [2023-08-30 14:00:02] 16319 - Async process archiving supported, using CliMulti.
INFO [2023-08-30 14:00:02] 16319 - Reports for today will be processed at most every 3600 seconds. You can change this value in Matomo UI > Settings > General Settings.
INFO [2023-08-30 14:00:02] 16319 - Archiving was last executed without error 23 hours 56 min ago.
INFO [2023-08-30 14:00:02] 16319 ---------------------------
INFO [2023-08-30 14:00:02] 16319 START
INFO [2023-08-30 14:00:02] 16319 Starting Matomo reports archiving...
INFO [2023-08-30 14:00:02] 16319 Found 3 failed jobs (ts_invalidated older than 1 day), resetting status to try them again.
INFO [2023-08-30 14:00:02] 16319 Start processing archives for site 1.
INFO [2023-08-30 14:00:02] 16319 Will invalidate archived reports for today in site ID = 1's timezone (2023-08-30 00:00:00).
INFO [2023-08-30 14:00:02] 16319 Will invalidate archived reports for yesterday in site ID = 1's timezone (2023-08-29 00:00:00).
INFO [2023-08-30 14:00:28] 16319 Archived website id 1, period = day, date = 2023-08-30, segment = '', 1789 visits found. Time elapsed: 11.270s
INFO [2023-08-30 14:00:28] 16319 Archived website id 1, period = day, date = 2023-08-29, segment = 'pageUrl=@https%3A%2F%2Fwww.domain.de%2Fen%2F', 8032 visits found. Time elapsed: 25.696s
INFO [2023-08-30 14:00:28] 16319 Archived website id 1, period = day, date = 2023-08-29, segment = 'pageUrl!@https%3A%2F%2Fwww.domain.de%2Fen%2F', 920 visits found. Time elapsed: 25.696s
Der Cronjob selbst sieht wie folgt aus:
/usr/bin/php81 /usr/www/users/user_13/_matomo/console --matomo-domain=matomo.domain.de core:archive --url=https://matomo.domain.de > /usr/www/users/user_13/_matomo/logs/matomo-archive.log
Seitens Hetzner habe ich die folgende Info erhalten:
Wir haben festgestellt, dass Ihr Managed Server aktuell überlastet ist.
Folgende Prozesse führen zu der Überlastung:
MySQL
| 689443 | user_13 | 94.111.10.100:40544 | matomo_database | Query | 175429 | Sending data | SELECT /*+ MAX_EXECUTION_TIME(7200000) */
yyy.idvisit AS idvisit,
12 AS idgoal,
4 AS `type`,
yyy.idaction AS idaction,
COUNT(*) AS `1`,
ROUND(SUM(yyy.revenue_total),2) AS `2`,
COUNT(yyy.idvisit) AS `3`,
ROUND(SUM(yyy.revenue_subtotal),2) AS `4`,
ROUND(SUM(yyy.revenue_tax),2) AS `5`,
ROUND(SUM(yyy.revenue_shipping),2) AS `6`,
ROUND(SUM(yyy.revenue_discount),2) AS `7`,
SUM(yyy.items) AS `8`,
yyy.pages_before AS `9`,
SUM(yyy.attribution) AS `10`,
COUNT(*) AS `12`,
ROUND(SUM(yyy.revenue),2) AS `15`
FROM (
SELECT
num_total AS pages_before,
1 / num_total AS attribution,
r.idvisit AS idvisit,
lac.idaction AS idaction,
lvcon.revenue AS revenue_total,
1 / num_total * lvcon.revenue AS revenue,
1 / num_total * lvcon.revenue_subtotal AS revenue_subtotal,
1 / num_total * lvcon.revenue_tax AS revenue_tax,
1 / num_total * lvcon.revenue_shipping AS revenue_shipping,
1 / num_total * lvcon.revenue_discount AS revenue_discount,
1 / num_total * lvcon.items AS items
FROM (
SELECT /* segmenthash d47ae978cdef0d0a30bb0b76f14d31d8 */ /* sites 1 */ /* 2023-08-23,2023-08-24 */ /* Actions */ /* trigger = CronArchive */
log_conversion.idvisit, COUNT(*) AS num_total
FROM
matomo_logtmpsegmentb97a8ba18189d04de26a38be6c3b5d9a AS logtmpsegmentb97a8ba18189d04de26a38be6c3b5d9a INNER JOIN matomo_log_conversion AS log_conversion ON log_conversion.idvisit = logtmpsegmentb97a8ba18189d04de26a38be6c3b5d9a.idvisit RIGHT JOIN matomo_log_link_visit_action AS log_vpast ON log_conversion.idvisit = log_vpast.idvisit LEFT JOIN matomo_log_action AS lac_past ON log_vpast.idaction_name = lac_past.idaction
WHERE
log_conversion.idgoal = 12
AND log_vpast.server_time <= log_conversion.server_time
AND lac_past.type = 4
GROUP BY
log_conversion.idvisit
) AS r
LEFT JOIN matomo_log_conversion lvcon ON lvcon.idgoal = 12 AND lvcon.idvisit = r.idvisit
RIGHT JOIN matomo_log_link_visit_action logv ON logv.idvisit = r.idvisit
LEFT JOIN matomo_log_action lac ON logv.idaction_name = lac.idaction
WHERE logv.server_time >= '2023-08-23 22:00:00'
AND logv.server_time <= '2023-08-24 21:59:59'
AND logv.idsite IN (1)
AND lac.type = 4
AND logv.server_time <= lvcon.server_time
) AS yyy
GROUP BY yyy.idaction
ORDER BY `9` DESC | 0.000 |
| 689443 | user_13 | 94.111.10.100:40544 | matomo_database | Query | 148872 | Sending data | SELECT /*+ MAX_EXECUTION_TIME(7200000) */
yyy.idvisit AS idvisit,
8 AS idgoal,
4 AS `type`,
yyy.idaction AS idaction,
COUNT(*) AS `1`,
ROUND(SUM(yyy.revenue_total),2) AS `2`,
COUNT(yyy.idvisit) AS `3`,
ROUND(SUM(yyy.revenue_subtotal),2) AS `4`,
ROUND(SUM(yyy.revenue_tax),2) AS `5`,
ROUND(SUM(yyy.revenue_shipping),2) AS `6`,
ROUND(SUM(yyy.revenue_discount),2) AS `7`,
SUM(yyy.items) AS `8`,
yyy.pages_before AS `9`,
SUM(yyy.attribution) AS `10`,
COUNT(*) AS `12`,
ROUND(SUM(yyy.revenue),2) AS `15`
FROM (
SELECT
num_total AS pages_before,
1 / num_total AS attribution,
r.idvisit AS idvisit,
lac.idaction AS idaction,
lvcon.revenue AS revenue_total,
1 / num_total * lvcon.revenue AS revenue,
1 / num_total * lvcon.revenue_subtotal AS revenue_subtotal,
1 / num_total * lvcon.revenue_tax AS revenue_tax,
1 / num_total * lvcon.revenue_shipping AS revenue_shipping,
1 / num_total * lvcon.revenue_discount AS revenue_discount,
1 / num_total * lvcon.items AS items
FROM (
SELECT /* segmenthash d47ae978cdef0d0a30bb0b76f14d31d8 */ /* sites 1 */ /* 2023-08-24,2023-08-25 */ /* Actions */ /* trigger = CronArchive */
log_conversion.idvisit, COUNT(*) AS num_total
FROM
matomo_logtmpsegment49b8d3569e62742b93981d7518fed0cd AS logtmpsegment49b8d3569e62742b93981d7518fed0cd INNER JOIN matomo_log_conversion AS log_conversion ON log_conversion.idvisit = logtmpsegment49b8d3569e62742b93981d7518fed0cd.idvisit RIGHT JOIN matomo_log_link_visit_action AS log_vpast ON log_conversion.idvisit = log_vpast.idvisit LEFT JOIN matomo_log_action AS lac_past ON log_vpast.idaction_name = lac_past.idaction
WHERE
log_conversion.idgoal = 8
AND log_vpast.server_time <= log_conversion.server_time
AND lac_past.type = 4
GROUP BY
log_conversion.idvisit
) AS r
LEFT JOIN matomo_log_conversion lvcon ON lvcon.idgoal = 8 AND lvcon.idvisit = r.idvisit
RIGHT JOIN matomo_log_link_visit_action logv ON logv.idvisit = r.idvisit
LEFT JOIN matomo_log_action lac ON logv.idaction_name = lac.idaction
WHERE logv.server_time >= '2023-08-24 22:00:00'
AND logv.server_time <= '2023-08-25 21:59:59'
AND logv.idsite IN (1)
AND lac.type = 4
AND logv.server_time <= lvcon.server_time
) AS yyy
GROUP BY yyy.idaction
ORDER BY `9` DESC | 0.000 |
| 689443 | user_13 | 94.111.10.100:40544 | matomo_database | Query | 135371 | Sending data | SELECT /*+ MAX_EXECUTION_TIME(7200000) */
yyy.idvisit AS idvisit,
12 AS idgoal,
4 AS `type`,
yyy.idaction AS idaction,
COUNT(*) AS `1`,
ROUND(SUM(yyy.revenue_total),2) AS `2`,
COUNT(yyy.idvisit) AS `3`,
ROUND(SUM(yyy.revenue_subtotal),2) AS `4`,
ROUND(SUM(yyy.revenue_tax),2) AS `5`,
ROUND(SUM(yyy.revenue_shipping),2) AS `6`,
ROUND(SUM(yyy.revenue_discount),2) AS `7`,
SUM(yyy.items) AS `8`,
yyy.pages_before AS `9`,
SUM(yyy.attribution) AS `10`,
COUNT(*) AS `12`,
ROUND(SUM(yyy.revenue),2) AS `15`
FROM (
SELECT
num_total AS pages_before,
1 / num_total AS attribution,
r.idvisit AS idvisit,
lac.idaction AS idaction,
lvcon.revenue AS revenue_total,
1 / num_total * lvcon.revenue AS revenue,
1 / num_total * lvcon.revenue_subtotal AS revenue_subtotal,
1 / num_total * lvcon.revenue_tax AS revenue_tax,
1 / num_total * lvcon.revenue_shipping AS revenue_shipping,
1 / num_total * lvcon.revenue_discount AS revenue_discount,
1 / num_total * lvcon.items AS items
FROM (
SELECT /* segmenthash d47ae978cdef0d0a30bb0b76f14d31d8 */ /* sites 1 */ /* 2023-08-23,2023-08-24 */ /* Actions */ /* trigger = CronArchive */
log_conversion.idvisit, COUNT(*) AS num_total
FROM
matomo_logtmpsegmentb97a8ba18189d04de26a38be6c3b5d9a AS logtmpsegmentb97a8ba18189d04de26a38be6c3b5d9a INNER JOIN matomo_log_conversion AS log_conversion ON log_conversion.idvisit = logtmpsegmentb97a8ba18189d04de26a38be6c3b5d9a.idvisit RIGHT JOIN matomo_log_link_visit_action AS log_vpast ON log_conversion.idvisit = log_vpast.idvisit LEFT JOIN matomo_log_action AS lac_past ON log_vpast.idaction_name = lac_past.idaction
WHERE
log_conversion.idgoal = 12
AND log_vpast.server_time <= log_conversion.server_time
AND lac_past.type = 4
GROUP BY
log_conversion.idvisit
) AS r
LEFT JOIN matomo_log_conversion lvcon ON lvcon.idgoal = 12 AND lvcon.idvisit = r.idvisit
RIGHT JOIN matomo_log_link_visit_action logv ON logv.idvisit = r.idvisit
LEFT JOIN matomo_log_action lac ON logv.idaction_name = lac.idaction
WHERE logv.server_time >= '2023-08-23 22:00:00'
AND logv.server_time <= '2023-08-24 21:59:59'
AND logv.idsite IN (1)
AND lac.type = 4
AND logv.server_time <= lvcon.server_time
) AS yyy
GROUP BY yyy.idaction
ORDER BY `9` DESC | 0.000 |
| 689443 | user_13 | 94.111.10.100:40544 | matomo_database | Query | 91082 | Sending data | SELECT /*+ MAX_EXECUTION_TIME(7200000) */
yyy.idvisit AS idvisit,
12 AS idgoal,
4 AS `type`,
yyy.idaction AS idaction,
COUNT(*) AS `1`,
ROUND(SUM(yyy.revenue_total),2) AS `2`,
COUNT(yyy.idvisit) AS `3`,
ROUND(SUM(yyy.revenue_subtotal),2) AS `4`,
ROUND(SUM(yyy.revenue_tax),2) AS `5`,
ROUND(SUM(yyy.revenue_shipping),2) AS `6`,
ROUND(SUM(yyy.revenue_discount),2) AS `7`,
SUM(yyy.items) AS `8`,
yyy.pages_before AS `9`,
SUM(yyy.attribution) AS `10`,
COUNT(*) AS `12`,
ROUND(SUM(yyy.revenue),2) AS `15`
FROM (
SELECT
num_total AS pages_before,
1 / num_total AS attribution,
r.idvisit AS idvisit,
lac.idaction AS idaction,
lvcon.revenue AS revenue_total,
1 / num_total * lvcon.revenue AS revenue,
1 / num_total * lvcon.revenue_subtotal AS revenue_subtotal,
1 / num_total * lvcon.revenue_tax AS revenue_tax,
1 / num_total * lvcon.revenue_shipping AS revenue_shipping,
1 / num_total * lvcon.revenue_discount AS revenue_discount,
1 / num_total * lvcon.items AS items
FROM (
SELECT /* sites 1 */ /* 2023-08-23,2023-08-24 */ /* Actions */ /* trigger = CronArchive */
log_conversion.idvisit, COUNT(*) AS num_total
FROM
matomo_log_conversion AS log_conversion RIGHT JOIN matomo_log_link_visit_action AS log_vpast ON log_conversion.idvisit = log_vpast.idvisit LEFT JOIN matomo_log_action AS lac_past ON log_vpast.idaction_name = lac_past.idaction
WHERE
log_conversion.server_time >= '2023-08-23 22:00:00'
AND log_conversion.server_time <= '2023-08-24 21:59:59'
AND log_conversion.idsite IN ('1')AND log_conversion.idgoal = 12
AND log_vpast.server_time <= log_conversion.server_time
AND lac_past.type = 4
GROUP BY
log_conversion.idvisit
) AS r
LEFT JOIN matomo_log_conversion lvcon ON lvcon.idgoal = 12 AND lvcon.idvisit = r.idvisit
RIGHT JOIN matomo_log_link_visit_action logv ON logv.idvisit = r.idvisit
LEFT JOIN matomo_log_action lac ON logv.idaction_name = lac.idaction
WHERE logv.server_time >= '2023-08-23 22:00:00'
AND logv.server_time <= '2023-08-24 21:59:59'
AND logv.idsite IN (1)
AND lac.type = 4
AND logv.server_time <= lvcon.server_time
) AS yyy
GROUP BY yyy.idaction
ORDER BY `9` DESC
In der Datenbank sind auch diverse Tabellen im Format “matomo_archive_blog_20xx_xx” sowie “matomo_archive_numberic_20xx_xx” zu finden, welche größtenteils ohne Einträge sind.
Folgende Tabellen haben Datensätze:
matomo_archive_blob_2023_01 -> 699 Einträge
matomo_archive_numeric_2023_01 -> 464 Einträge
matomo_archive_numeric_2023_07 -> 1.245 Einträge
matomo_archive_numeric_2023_08 -> 12.876 Einträge
matomo_archive_numeric_2023_09 -> 250 Einträge
Die Tabelle “matomo_archive_invalidations” hat diverse Einträge, welche teils einfach nur “done” als “Name” aufweisen, aber teils auch “done” mit einem Hash dahinter haben (z.B. doned47ae978cdef0d0a30bb0b76f14d31d9).
.
Der Systemcheck beinhaltet folgende Infos und zeigt keine Fehler an:
### Mandatory checks
#### PHP-Version >= 7.2.5:
✔ 8.1.22
#### PDO Erweiterung:
✔
#### PDO\MYSQL Erweiterung:
✔
#### MYSQLI Erweiterung:
✔
#### Weitere erforderliche Anforderungen:
✔ zlib ✔ json ✔ filter ✔ hash ✔ session
#### Erforderliche Funktionen:
✔ debug_backtrace ✔ eval ✔ hash ✔ gzcompress ✔ gzuncompress ✔ pack
#### Benötigte PHP Konfiguration (php.ini):
✔ session.auto_start = 0 ✔ max_execution_time = 0 OR = -1 OR >= 30
#### Verzeichnisse mit Schreibzugriff:
✔ $DOC_ROOT/tmp ✔ $DOC_ROOT/tmp/assets ✔ $DOC_ROOT/tmp/cache ✔ $DOC_ROOT/tmp/climulti ✔ $DOC_ROOT/tmp/latest ✔ $DOC_ROOT/tmp/logs ✔ $DOC_ROOT/tmp/sessions ✔ $DOC_ROOT/tmp/tcpdf ✔ $DOC_ROOT/tmp/templates_c
#### Verzeichnisse mit Schreibzugriff auf Tag Manager:
✔ $DOC_ROOT/js
### Optional checks
#### Verzeichnisse, welche privat sein müssen:
✔ Alle privaten Verzeichnisse sind nicht aus dem Internet zugänglich.
#### Empfohlene private Verzeichnisse:
✔ Alle privaten Verzeichnisse sind nicht aus dem Internet zugänglich.
#### 64-bit PHP Binär:
✔
#### Tracker-Status:
✔
#### Speicherlimit:
✔ 512M
#### Zeitzone:
✔
#### Öffnen einer URL:
✔ curl
#### PageSpeed ist deaktiviert:
✔
#### GD > 2.x + FreeType (Grafiken):
✔
#### Andere Erweiterungen:
✔ json ✔ libxml ✔ dom ✔ SimpleXML ✔ openssl
#### Andere Funktionen:
✔ shell_exec ✔ set_time_limit ✔ mail ✔ parse_ini_file ✔ glob ✔ gzopen ✔ md5_file
#### Dateisystem:
✔
#### Cron einrichten - Prozesse via CLI steuern:
nicht unterstützt (wahlweise) (Gründe: unbekannt) Erfahren Sie mehr
#### Letzter erfolgreicher Abschluss der Archivierung:
⚠ Error: Die Archivierung ist zuletzt am Dienstag, 29. August 2023 14:03:09 erfolgreich gelaufen, also vor 5 Tage 01:52:59.<br/><br/>Bitte überprüfen Sie, ob Sie einen Crontab eingerichtet haben, der den <code>core:archive</code> Konsolenbefehl aufruft, und ob Sie einen <code>MAILTO</code> konfiguriert haben, um Fehler per E-Mail zu erhalten, wenn die Archivierung fehlschlägt. Sie können auch versuchen, den Konsolenbefehl auszuführen, um Ihre Berichte manuell zu archivieren: <code>$DOC_ROOT/console --matomo-domain=matomo.domain.de core:archive</code>. Mehr erfahren.
#### Datenbankfähigkeiten:
✔ UTF8mb4 charset ✔ LOAD DATA INFILE ✔ CREATE TEMPORARY TABLES ✔ Changing transaction isolation level
#### Maximale Packetgröße:
✔
#### Erzwungene SSL Verbindung:
✔
#### Standorterkennung:
✔ geoip2php (continent_code, continent_name, country_code, country_name, region_code, region_name, city_name, postal_code, lat, long)
#### Update über HTTPS:
✔
#### Schreibbarer JavaScript-Tracker ("/matomo.js"):
✔
### Informational results
#### Matomo Version:
4.15.1
#### Matomo Update History:
4.15.0,
#### Matomo Install Version:
4.15.0
#### Latest Available Version:
4.15.1
#### Is Git Deployment:
0
#### PHP_OS:
Linux
#### PHP_BINARY:
/home/httpd/cgi-bin/php81.bin.fcgi
#### PHP SAPI:
cgi-fcgi
#### Timezone Version:
2023.3
#### PHP Timezone:
UTC
#### PHP Time:
1693756568
#### PHP Datetime:
2023-09-03 15:56:08
#### PHP INI max_execution_time:
240
#### PHP INI post_max_size:
200M
#### PHP INI max_input_vars:
1000
#### PHP INI zlib.output_compression:
0
#### Curl Version:
7.74.0, OpenSSL/1.1.1n
#### Suhosin Installed:
0
#### DB Prefix:
matomo_
#### DB Charset:
utf8mb4
#### DB Adapter:
PDO\MYSQL
#### MySQL Version:
10.5.19-MariaDB-0+deb11u2
#### Num Tables:
99
#### Browser Segment Archiving Enabled:
1
#### Development Mode Enabled:
0
#### Internet Enabled:
1
#### Multi Server Environment:
0
#### Auto Update Enabled:
1
#### Custom User Path:
0
#### Custom Include Path:
0
#### Release Channel:
latest_stable
#### Plugins Activated:
API, Actions, Annotations, BulkTracking, Contents, CoreAdminHome, CoreConsole, CoreHome, CorePluginsAdmin, CoreUpdater, CoreVisualizations, CoreVue, CustomDimensions, CustomJsTracker, Dashboard, DevicePlugins, DevicesDetection, Diagnostics, Ecommerce, Events, Feedback, GeoIp2, Goals, Heartbeat, ImageGraph, Insights, Installation, Intl, IntranetMeasurable, InvalidateReports 4.1.1, LanguagesManager, Live, Login, Marketplace, MobileMessaging, Monolog, Morpheus, MultiSites, Overlay, PagePerformance, PrivacyManager, ProfessionalServices, Proxy, Referrers, Resolution, RssWidget, SEO, ScheduledReports, SegmentEditor, SitesManager, TagManager, Tour, Transitions, TwoFactorAuth, UserCountry, UserCountryMap, UserId, UserLanguage, UsersManager, VisitFrequency, VisitTime, VisitorInterest, VisitsSummary, WebsiteMeasurable, Widgetize
#### Plugins Deactivated:
DBStats, MobileAppMeasurable
#### Plugins Invalid:
#### Server Info:
Apache
#### Had visits in last 1 day:
1
#### Had visits in last 3 days:
1
#### Had visits in last 5 days:
1
#### Archive Time Last Started:
2023-08-30 14:00:02
#### Archive Time Last Finished:
2023-08-29 14:03:09
#### User Agent:
Mozilla/5.0 (Macintosh; Intel Mac OS X 11.11; rv:109.0) Gecko/20100101 Firefox/115.0
#### Browser Language:
de,en-us,en
#### Total Invalidation Count:
31
#### In Progress Invalidation Count:
9
#### Scheduled Invalidation Count:
22
#### Earliest invalidation ts_started:
2023-08-27 16:57:02
#### Latest invalidation ts_started:
2023-08-30 16:00:29
#### Earliest invalidation ts_invalidated:
2023-08-24 22:00:02
#### Latest invalidation ts_invalidated:
2023-08-31 13:37:11
#### Number of segment invalidations:
20
#### Number of plugin invalidations:
0
#### List of plugins being invalidated:
#### Anonymize Referrer:
#### Do Not Track enabled:
0
</details>
Ich hoffe, dass mir hier jemand weiterhelfen kann.
Vielen lieben Dank!
Beste Grüße,
Tim