Slow query with goals in 1.1.1

Support & Bugs
1

Hi,

my server is borked since I upgraded to 1.1.1.

Symptoms: a slow query blocks many other queries.

The slow query:
SELECT piwik_log_visit.* ,
piwik_goal.match_attribute as goal_match_attribute,
piwik_goal.name as goal_name,
piwik_goal.revenue as goal_revenue,
piwik_log_conversion.idaction_url as goal_idaction_url,
piwik_log_conversion.server_time as goal_server_time,
count(*) as count_goal_conversions
FROM piwik_log_visit
LEFT JOIN piwik_log_conversion
ON piwik_log_visit.idvisit = piwik_log_conversion.idvisit
LEFT JOIN piwik_goal
ON (piwik_goal.idsite = piwik_log_visit.idsite
AND piwik_goal.idgoal = piwik_log_conversion.idgoal)
AND piwik_goal.deleted = 0
WHERE piwik_log_visit.idsite = '2’
GROUP BY idvisit
ORDER BY visit_last_action_time DESC LIMIT 10

I am now trying to disable the goals plugin - hopefully this is a short-term workaround…

2

Deactivating goals did not help.

I used “EXPLAIN” and am getting the impression that the existing indices are not actually used by MySQL:

mysql> explain SELECT piwik_log_visit.* , piwik_goal.match_attribute as goal_match_attribute, piwik_goal.name as goal_name, piwik_goal.revenue as goal_revenue, piwik_log_conversion.idaction_url as goal_idaction_url, piwik_log_conversion.server_time as goal_server_time, count(*) as count_goal_conversions FROM piwik_log_visit LEFT JOIN piwik_log_conversion ON piwik_log_visit.idvisit = piwik_log_conversion.idvisit LEFT JOIN piwik_goal ON (piwik_goal.idsite = piwik_log_visit.idsite AND piwik_goal.idgoal = piwik_log_conversion.idgoal) AND piwik_goal.deleted = 0 WHERE piwik_log_visit.idsite = ‘2’ GROUP BY idvisit ORDER BY visit_last_action_time DESC LIMIT 10;
±—±------------±---------------------±-------±---------------------------------------------------------------------------±-----------------------------±--------±------±--------±-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±---------------------±-------±---------------------------------------------------------------------------±-----------------------------±--------±------±--------±-----------------------------+
| 1 | SIMPLE | piwik_log_conversion | system | PRIMARY | NULL | NULL | NULL | 0 | const row not found |
| 1 | SIMPLE | piwik_goal | system | PRIMARY | NULL | NULL | NULL | 0 | const row not found |
| 1 | SIMPLE | piwik_log_visit | ref | index_idsite_date_config,index_idsite_datetime_config,index_idsite_idvisit | index_idsite_datetime_config | 4 | const | 3576775 | Using where; Using temporary |
±—±------------±---------------------±-------±---------------------------------------------------------------------------±-----------------------------±--------±------±--------±-----------------------------+
3 rows in set (0.01 sec)

If the index is not used, this SQL statement basically breaks Piwik. No insertions are possible while it runs, and it runs practically forever.

How can I disable this statement or, even better, force MySQL to actually use the indices?

3

Located the culprit.

It is actually the Live plugin that makes Piwik 1.1.1 unusable if you have a reasonable amount of traffic, not the goals plugin.

I revitalized my server by removing the plugin from the dashboard, then disabling it.

I am looking forward to a repaired version of the Live plugin!

4

Thanks for the report, I created a ticket in Live! plugin performance issues · Issue #1966 · matomo-org/matomo · GitHub

5

thanks for that.

Piwik without Live is now much more responsive than my previous version (1.0) with the Live plugin. Therefore, this was always a problem, but it seems to have gotten noticeably worse in 1.1.1.