Matomo 4.15
Mariadb 10.11 (4cpu, 12gb ram, ssd) config:
[mariadb]
max_allowed_packet=3G
innodb_buffer_pool_size=11G
innodb_flush_log_at_trx_commit=2
innodb_log_file_size=2G
tmp_table_size=128M
max_heap_table_size=128M
PHP 8.2 config:
max_execution_time=0
max_input_time=-1
I have a problem with fails on archiving reports on siteid=2 (all other siteid reports work fine) after updating from 3.14.2 to 4.14.2, now 4.15.0. Daily visits count for this siteid 2 is about 4000-6000.
Cronjob fails with error: “Mysql Server has gone away” on some days, for example, 2023-06-06, 2023-07-10. But works on all other days. In mariadb slow logs I see the queries with abnormal count of fetched rows:
# Thread_id: 1232078 Schema: matomo QC_hit: No
# Query_time: 27415.677997 Lock_time: 0.000232 Rows_sent: 0 Rows_examined: 13532189595
# Rows_affected: 0 Bytes_sent: 0
SET timestamp=1691055209;
SELECT
yyy.idvisit AS idvisit,
7 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 2 */ /* 2023-06-05,2023-06-06 */ /* 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-06-05 21:00:00'
AND log_conversion.server_time <= '2023-06-06 20:59:59'
AND log_conversion.idsite IN ('2')AND log_conversion.idgoal = 7
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 = 7 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-06-05 21:00:00'
AND logv.server_time <= '2023-06-06 20:59:59'
AND logv.idsite IN (2)
AND lac.type = 4
AND logv.server_time <= lvcon.server_time
) AS yyy
GROUP BY yyy.idaction
ORDER BY `9` DESC;
On other days same query executes for about 10-40 seconds and output about 30-40 rows.
In mariadb processlist I see select query with long execution time in status “Sending data”. Utilization of one cpu core is 100% for this query.
Due to an error in reporting for these days (06 june, 10 july) the reports for this weeks, monthes (june, july), year are broken too.
What’s wrong with these days?