m.ferreira
(Miguel Ferrreira)
July 3, 2017, 9:04am
1
Hi,
We are using Piwik with millions of daily page visits and in the last few days, we’ve detected some heavy activity in the database. After further inspection, we’ve detected a full table scan in the MySQL logs in, from what I understand, the archiving process:
– Connection Id: 545497
– User: piwik_u
– Host:
– DB: piwik
– Command: Query
– Time: 8
– State: query end
UPDATE piwik_log_link_visit_action SET time_spent = ‘204’ WHERE idlink_va = ‘66068057’
Here is the full log
– Connection Id: 539599
– User: piwik_u
– Host:
– DB: piwik
– Command: Query
– Time: 2
– State: Sending data
SELECT
CASE
WHEN counter = 50001 THEN '-1'
ELSE `idaction`
END AS `idaction`
,
CASE
WHEN counter = 50001 THEN '-1'
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
WHEN `type` = 8 AND @counter8 = 50001 THEN 50001
WHEN `type` = 8 THEN @counter8:=@counter8+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, ( SELECT @counter8:=0 ) initCounter8,
( /* 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
piwik_log_link_visit_action AS log_link_visit_action LEFT JOIN piwik_log_action AS log_action ON log_link_visit_action.idaction_url = log_action.idaction LEFT JOIN piwik_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.server_time >= '2017-06-30 22:00:00'
AND log_link_visit_action.server_time <= '2017-07-01 21:59:59'
AND log_link_visit_action.idsite = '1'
AND log_link_visit_action.idaction_url IS NOT NULL AND log_link_visit_action.idaction_event_category IS NULL
GROUP BY
log_action.idaction
ORDER BY
`12` DESC, name ASC ) actualQuery
) AS withCounter
GROUP BY counter, `type`
I’m worried that a fulltable scan might be to harsh for the amount of requests we are receiving, and that the DB might not be able to handle it. Can you help us out?
matthieu
(Matthieu Aubry)
August 3, 2017, 5:46am
2
Hello, make sure you use Piwik 3, and that your tables are INNODB engine
m.ferreira
(Miguel Ferrreira)
August 3, 2017, 7:05am
3
Hi,
Thank you for the response, but I’m already using Piwik 3 (3.0.5), I’ve checked all the tables, and they are all using InnoDB engine.
Best,