Full table scan overloading the DB

m.ferreira · July 3, 2017, 9:04am

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 · August 3, 2017, 5:46am

Hello, make sure you use Piwik 3, and that your tables are INNODB engine

m.ferreira · August 3, 2017, 7:05am

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,