Hello,
We faced similar problem on Matomo 3.13.1. The archiving process loads 1 database core to 100%, works many hours, does not load CPU and consumes lower than 100 MB memory after begining to archive segment. We have CustomDimensions (v3.1.9), Funnels (v3.1.19), MultiChannelConversionAttribution (v3.0.7), UsersFlow (v3.1.11) plugins deployed. The segments are based on a custom dimension. Matomo does not accept tracking and user requests during the archiving. log_visit now has 544 862 rows, log_link_visit_action has 6 436 197. Matomo 3.11 with another segments archived 14 500 000 rows in log_link_visit_action without such problems on this Database server.
Can we do something to speed up the archiving of the segments ?
The commands of archiving processes are like the following:
./console -vvv -n core:archive --concurrent-requests-per-website=1 --concurrent-archivers=1 --disable-scheduled-tasks '--php-cli-options=-d memory_limit=2500M' --force-periods=day --force-idsites=1 --force-date-range 2020-01-09,2020-01-09
Logs of archiving process looks like the following:
2020-01-17T06:55:00.6932951Z + time -v ./console -vvv -n core:archive --concurrent-requests-per-website=1 --concurrent-archivers=1 --disable-scheduled-tasks '--php-cli-options=-d memory_limit=2500M' --force-periods=day --force-idsites=1 --force-date-range 2020-01-09,2020-01-09
2020-01-17T06:55:02.187595689Z INFO [2020-01-17 06:55:02] 10 ---------------------------
2020-01-17T06:55:02.187656786Z INFO [2020-01-17 06:55:02] 10 INIT
2020-01-17T06:55:02.187670366Z INFO [2020-01-17 06:55:02] 10 Running Matomo 3.13.1 as Super User │
2020-01-17T06:55:02.187743071Z INFO [2020-01-17 06:55:02] 10 --------------------------- 2020-01-17T06:55:02.187864503Z INFO [2020-01-17 06:55:02] 10 NOTES
2020-01-17T06:55:02.305696869Z INFO [2020-01-17 06:55:02] 10 - Async process archiving supported, using CliMulti.
2020-01-17T06:55:02.305729125Z INFO [2020-01-17 06:55:02] 10 - Reports for today will be processed at most every 1 seconds. You can change this value in Matomo UI > Settings > General Settings.
2020-01-17T06:55:02.305742495Z INFO [2020-01-17 06:55:02] 10 - Reports for the current week/month/year will be requested at most every 3600 seconds.
2020-01-17T06:55:02.307042824Z INFO [2020-01-17 06:55:02] 10 - Archiving was last executed without error 162 days 20 hours ago
2020-01-17T06:55:02.31183431Z INFO [2020-01-17 06:55:02] 10 - Will only process the following periods: day (--force-periods)
2020-01-17T06:55:02.312614713Z INFO [2020-01-17 06:55:02] 10 - Will invalidate archived reports for 2020-01-16 for following websites ids: 2
2020-01-17T06:55:02.355420079Z INFO [2020-01-17 06:55:02] 10 - Will process 1 websites (--force-idsites) │
2020-01-17T06:55:02.378409563Z INFO [2020-01-17 06:55:02] 10 ---------------------------
2020-01-17T06:55:02.378438078Z INFO [2020-01-17 06:55:02] 10 START
2020-01-17T06:55:02.378478112Z INFO [2020-01-17 06:55:02] 10 Starting Matomo reports archiving...
2020-01-17T06:55:02.422456968Z INFO [2020-01-17 06:55:02] 10 0 out of 1 archivers running currently
2020-01-17T06:55:02.45496149Z INFO [2020-01-17 06:55:02] 10 Will pre-process for website id = 1, period = day, date = 2020-01-09,2020-01-09 │
2020-01-17T06:55:02.455022376Z INFO [2020-01-17 06:55:02] 10 - pre-processing all visits
2020-01-17T06:55:02.49717573Z DEBUG [2020-01-17 06:55:02] 10 /usr/local/bin/php -q -d memory_limit=2500M /var/www/html/console climulti:request -q --matomo-domain='' --superuser 'module=API&method=API.get&idSite=1&period=day&date=2020-01-09,2020-01-09&format=php&trigger=archivephp&pid=91b8c5276b90a550d050748dae35757e6d882ddbb563b67a57fd8f8e565491b37af289c992767738e5cf140a4b6eacd6e1050&runid=10' > /var/www/html/tmp/climulti/91b8c5276b90a550d050748dae35757e6d882ddbb563b67a57fd8f8e565491b37af289c992767738e5cf140a4b6eacd6e1050.output 2>&1 &
2020-01-17T07:39:40.964722784Z DEBUG [2020-01-17 07:39:40] 10 Earliest created time of segment 'dimension13=^17.8.' w/ idSite = 1 is found to be 2020-01-16. Latest edit time is found to be 2020-01-16.
2020-01-17T07:39:40.964837795Z DEBUG [2020-01-17 07:39:40] 10 process_new_segments_from set to beginning_of_time or cannot recognize value
2020-01-17T07:39:41.006938083Z DEBUG [2020-01-17 07:39:41] 10 Earliest created time of segment 'dimension13=^18.0.' w/ idSite = 1 is found to be 2020-01-16. Latest edit time is found to be 2020-01-16.
2020-01-17T07:39:41.00696993Z DEBUG [2020-01-17 07:39:41] 10 process_new_segments_from set to beginning_of_time or cannot recognize value
2020-01-17T07:39:41.055210847Z DEBUG [2020-01-17 07:39:41] 10 Earliest created time of segment 'dimension13=^18.0.;dimension15==smb%252Fdatabase%252Flist' w/ idSite = 1 is found to be 2020-01-16. Latest edit
time is found to be 2020-01-16.
2020-01-17T07:39:41.055274525Z DEBUG [2020-01-17 07:39:41] 10 process_new_segments_from set to beginning_of_time or cannot recognize value │
2020-01-17T07:39:41.269250718Z DEBUG [2020-01-17 07:39:41] 10 General tracker cache was re-created.
2020-01-17T07:39:41.290536523Z INFO [2020-01-17 07:39:41] 10 - pre-processing segment 1/3 dimension13=^17.8. [date = 2020-01-09,2020-01-09]
2020-01-17T07:39:41.313685223Z DEBUG [2020-01-17 07:39:41] 10 /usr/local/bin/php -q -d memory_limit=2500M /var/www/html/console climulti:request -q --matomo-domain='' --superuser 'module=API&method=API.get&id
Site=1&period=day&date=2020-01-09,2020-01-09&format=php&segment=dimension13%3D%5E17.8.&trigger=archivephp&pid=a1de52b716f2b5be819d1a5434ad644674bdfb36d55135cef2791eb47a9b0899a68dac23c4e52f5ff534bed730594993084f0&runid=10' > /var/www/html/tmp/climulti/a1de52b716f2b5be819d1a5434ad644674bdfb36d55135cef2791eb47a9b0899a68dac23c4e52f5ff534bed730594993084f0.output 2>&1 &
“mysql -e ‘show full processlist\G;’” shows slow queries like the following:
*************************** 1. row ***************************
Id: 18133
User: matomo-test
Host: n33:36386
db: matomo-test
Command: Sleep
Time: 7546
State:
Info: NULL
*************************** 2. row ***************************
Id: 18134
User: matomo-test
Host: n33:34692
db: matomo-test
Command: Sleep
Time: 8706
State:
Info: NULL
*************************** 3. row ***************************
Id: 18136
User: matomo-test
Host: n40:46926
db: matomo-test
Command: Sleep
Time: 9425
State:
Info: NULL
*************************** 4. row ***************************
Id: 18228
User: matomo-test
Host: n40:52236
db: matomo-test
Command: Query
Time: 9364
State: Sending data
Info: SELECT
CASE
WHEN counter = 50001 THEN '__mtm_ranking_query_others__'
ELSE `eventCategory`
END AS `eventCategory`
,
CASE
WHEN counter = 50001 THEN '__mtm_ranking_query_others__'
ELSE `eventAction`
END AS `eventAction`
,
CASE
WHEN counter = 50001 THEN '__mtm_ranking_query_others__'
ELSE `eventName`
END AS `eventName`
, `1`, sum(`34`) AS `34`, sum(`2`) AS `2`, sum(`38`) AS `38`, sum(`35`) AS `35`, min(`36`) AS `36`, max(`37`) AS `37`
FROM (
SELECT
`eventCategory`, `eventAction`, `eventName`,
CASE
WHEN @counter = 50001 THEN 50001
ELSE @counter:=@counter+1
END
AS counter
, `1`, `34`, `2`, `38`, `35`, `36`, `37`
FROM
( SELECT @counter:=0 ) initCounter,
( /* trigger = CronArchive */
SELECT
log_action_event_category.name as eventCategory,
log_action_event_action.name as eventAction,
log_action_event_name.name as eventName,
count(distinct log_link_visit_action.idvisit) as `2`,
count(distinct log_link_visit_action.idvisitor) as `1`,
count(*) as `34`,
sum(
case when custom_float is null
then 0
else custom_float
end
) as `35`,
sum( case when custom_float is null then 0 else 1 end )
as `38`,
min(custom_float) as `36`,
max(custom_float) as `37`
FROM
logtmpsegment540ac9654778034030a60d2c18c6bcc3 AS logtmpsegment540ac9654778034030a60d2c18c6bcc3 INNER JOIN log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = logtmpsegment540ac9654778034030a60d2c18c6bcc3.idvisit LEFT JOIN log_action AS log_action_event_category ON log_link_visit_action.idaction_event_category = log_action_event_category.idaction LEFT JOIN log_action AS log_action_event_action ON log_link_visit_action.idaction_event_action = log_action_event_action.idaction LEFT JOIN log_action AS log_action_event_name ON log_link_visit_action.idaction_name = log_action_event_name.idaction
WHERE
log_link_visit_action.idaction_event_category IS NOT NULL
GROUP BY
log_link_visit_action.idaction_event_category,
log_link_visit_action.idaction_event_action,
log_link_visit_action.idaction_name
ORDER BY
`2` DESC, `eventName` ) actualQuery
) AS withCounter
GROUP BY counter
*************************** 5. row ***************************
Id: 18307
User: matomo-test
Host: n33:49070
db: matomo-test
Command: Sleep
Time: 8704
State:
Info: NULL
*************************** 6. row ***************************
Id: 18308
User: matomo-test
Host: n33:49074
db: matomo-test
Command: Query
Time: 8688
State: Sending data
Info: SELECT
CASE
WHEN counter = 50001 THEN '__mtm_ranking_query_others__'
ELSE `idaction`
END AS `idaction`
,
CASE
WHEN counter = 50001 THEN '__mtm_ranking_query_others__'
ELSE `name`
END AS `name`
, `url_prefix`, min(`28`) AS `28`, sum(`29`) AS `29`, sum(`2`) AS `2`, `1`, sum(`12`) AS `12`, sum(`30`) AS `30`, sum(`31`) AS `31`, min(`32`) AS `32`, max(`33`) AS `33`, `type`
FROM (
SELECT
`idaction`, `name`,
CASE
WHEN `type` = 1 AND @counter1 = 50001 THEN 50001
WHEN `type` = 1 THEN @counter1:=@counter1+1
WHEN `type` = 2 AND @counter2 = 50001 THEN 50001
WHEN `type` = 2 THEN @counter2:=@counter2+1
WHEN `type` = 3 AND @counter3 = 50001 THEN 50001
WHEN `type` = 3 THEN @counter3:=@counter3+1
WHEN `type` = 4 AND @counter4 = 50001 THEN 50001
WHEN `type` = 4 THEN @counter4:=@counter4+1
ELSE 0
END
AS counter
, `url_prefix`, `28`, `29`, `2`, `1`, `12`, `30`, `31`, `32`, `33`, `type`
FROM
( SELECT @counter1:=0 ) initCounter1, ( SELECT @counter2:=0 ) initCounter2, ( SELECT @counter3:=0 ) initCounter3, ( SELECT @counter4:=0 ) initCounter4,
( /* trigger = CronArchive */
SELECT
log_action.name,
log_action.type,
log_action.idaction,
log_action.url_prefix, count(distinct log_link_visit_action.idvisit) as `2`, count(distinct log_link_visit_action.idvisitor) as `1`, count(*) as `12`, sum(
case when custom_float is null
then 0
else custom_float
end
) / 1000 as `30`, sum(
case when custom_float is null
then 0
else 1
end
) as `31`, min(custom_float) / 1000 as `32`, max(custom_float) / 1000 as `33`,
CASE WHEN (MAX(log_link_visit_action.custom_var_v5) = 0
AND log_link_visit_action.custom_var_k5 = '_pk_scount')
THEN 1 ELSE 0 END
AS `28`,
SUM( CASE WHEN log_action_name_ref.type = 8
THEN 1 ELSE 0 END)
AS `29`
FROM
logtmpsegment582e647d460e04e5984c7d9d29e7d67a AS logtmpsegment582e647d460e04e5984c7d9d29e7d67a INNER JOIN log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = logtmpsegment582e647d460e04e5984c7d9d29e7d67a.idvisit LEFT JOIN log_action AS log_action ON log_link_visit_action.idaction_name = log_action.idaction LEFT JOIN log_action AS log_action_name_ref ON log_link_visit_action.idaction_name_ref = log_action_name_ref.idaction
WHERE
log_link_visit_action.idaction_name IS NOT NULL AND log_link_visit_action.idaction_event_category IS NULL AND (log_action.type IN (1, 2, 3, 4) OR log_action.type IS NULL)
GROUP BY
log_link_visit_action.idaction_name
ORDER BY
`12` DESC, name ASC ) actualQuery
) AS withCounter
GROUP BY counter, `type`
*************************** 7. row ***************************
Id: 18431
User: matomo-test
Host: n33:32970
db: matomo-test
Command: Sleep
Time: 7546
State:
Info: NULL
*************************** 8. row ***************************
Id: 18432
User: matomo-test
Host: n33:32972
db: matomo-test
Command: Query
Time: 7528
State: Sending data
Info: SELECT
CASE
WHEN counter = 50001 THEN '__mtm_ranking_query_others__'
ELSE `idaction`
END AS `idaction`
,
CASE
WHEN counter = 50001 THEN '__mtm_ranking_query_others__'
ELSE `name`
END AS `name`
, `url_prefix`, min(`28`) AS `28`, sum(`29`) AS `29`, sum(`2`) AS `2`, `1`, sum(`12`) AS `12`, sum(`30`) AS `30`, sum(`31`) AS `31`, min(`32`) AS `32`, max(`33`) AS `33`, `type`
FROM (
SELECT
`idaction`, `name`,
CASE
WHEN `type` = 1 AND @counter1 = 50001 THEN 50001
WHEN `type` = 1 THEN @counter1:=@counter1+1
WHEN `type` = 2 AND @counter2 = 50001 THEN 50001
WHEN `type` = 2 THEN @counter2:=@counter2+1
WHEN `type` = 3 AND @counter3 = 50001 THEN 50001
WHEN `type` = 3 THEN @counter3:=@counter3+1
WHEN `type` = 4 AND @counter4 = 50001 THEN 50001
WHEN `type` = 4 THEN @counter4:=@counter4+1
ELSE 0
END
AS counter
, `url_prefix`, `28`, `29`, `2`, `1`, `12`, `30`, `31`, `32`, `33`, `type`
FROM
( SELECT @counter1:=0 ) initCounter1, ( SELECT @counter2:=0 ) initCounter2, ( SELECT @counter3:=0 ) initCounter3, ( SELECT @counter4:=0 ) initCounter4,
( /* trigger = CronArchive */
SELECT
log_action.name,
log_action.type,
log_action.idaction,
log_action.url_prefix, count(distinct log_link_visit_action.idvisit) as `2`, count(distinct log_link_visit_action.idvisitor) as `1`, count(*) as `12`, sum(
case when custom_float is null
then 0
else custom_float
end
) / 1000 as `30`, sum(
case when custom_float is null
then 0
else 1
end
) as `31`, min(custom_float) / 1000 as `32`, max(custom_float) / 1000 as `33`,
CASE WHEN (MAX(log_link_visit_action.custom_var_v5) = 0
AND log_link_visit_action.custom_var_k5 = '_pk_scount')
THEN 1 ELSE 0 END
AS `28`,
SUM( CASE WHEN log_action_name_ref.type = 8
THEN 1 ELSE 0 END)
AS `29`
FROM
logtmpsegment7db0fa0a71f6b22382eb37c8828ec125 AS logtmpsegment7db0fa0a71f6b22382eb37c8828ec125 INNER JOIN log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = logtmpsegment7db0fa0a71f6b22382eb37c8828ec125.idvisit LEFT JOIN log_action AS log_action ON log_link_visit_action.idaction_name = log_action.idaction LEFT JOIN log_action AS log_action_name_ref ON log_link_visit_action.idaction_name_ref = log_action_name_ref.idaction
WHERE
log_link_visit_action.idaction_name IS NOT NULL AND log_link_visit_action.idaction_event_category IS NULL AND (log_action.type IN (1, 2, 3, 4) OR log_action.type IS NULL)
GROUP BY
log_link_visit_action.idaction_name
ORDER BY
`12` DESC, name ASC ) actualQuery
) AS withCounter
GROUP BY counter, `type`
*************************** 9. row ***************************
Id: 19257
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show full processlist
