I’m running Piwik on a small VPS (384MB RAM) and I’ve been having some performance issues I’m trying to solve. Note that I’m running MariaDB 5.5 as the database server, and tables are using the Aria engine (I’m a bit afraid to use InnoDB due to its memory usage).
For some reason, the following query that Piwik executes, seems to run very slowly:
( SELECT idvisitor,
visit_last_action_time,
visit_first_action_time,
idvisit,
visit_exit_idaction_url,
visit_exit_idaction_name,
visitor_returning,
visitor_days_since_first,
visitor_days_since_order,
location_country,
location_continent,
referer_name,
referer_keyword,
referer_type,
visitor_count_visits,
visit_goal_buyer
,
custom_var_k1, custom_var_v1,
custom_var_k2, custom_var_v2,
custom_var_k3, custom_var_v3,
custom_var_k4, custom_var_v4,
custom_var_k5, custom_var_v5
,
0 as priority
FROM piwik_log_visit
WHERE visit_last_action_time >= '2012-05-05 10:49:31' AND idsite = '1' AND config_id = 'xxxxxxxx'
ORDER BY visit_last_action_time DESC
LIMIT 1
)
UNION
( SELECT idvisitor,
visit_last_action_time,
visit_first_action_time,
idvisit,
visit_exit_idaction_url,
visit_exit_idaction_name,
visitor_returning,
visitor_days_since_first,
visitor_days_since_order,
location_country,
location_continent,
referer_name,
referer_keyword,
referer_type,
visitor_count_visits,
visit_goal_buyer
,
custom_var_k1, custom_var_v1,
custom_var_k2, custom_var_v2,
custom_var_k3, custom_var_v3,
custom_var_k4, custom_var_v4,
custom_var_k5, custom_var_v5
,
1 as priority
FROM piwik_log_visit
WHERE visit_last_action_time >= '2012-05-05 10:49:31' AND idsite = '1' AND idvisitor = 'yyyyyyyy'
LIMIT 1
)
ORDER BY priority DESC
LIMIT 1
I’ve been looking at trying to figure out why it’s slow and improve it. I found that adding an index on (idsite, config_id) greatly sped up the first SELECT (from ~70secs to ~0.3s) on a largish >1mil rows. But that doesn’t seem to have exactly solved the problem.
Executing the query myself, I get a pretty fast response (again ~0.3s) and sticking EXPLAIN before it doesn’t show anything particularly interesting.
But MySQL often takes up 100% CPU load, and MySQL’s SHOW PROCESSLIST points to the above query as the culprit - it’s often spending 60+ seconds in the ‘sending data’ state (and there’s often a bunch of queued INSERT/UPDATE queries waiting for a table lock).
So I’ve tried to reduce the size of the piwik_log_visit table by pruning old entries. This seems to have greatly reduced the problem, however I still get spikes every now and then.
Currently I’ve got about >60k rows and I notice that the above query takes a few seconds (much better than 60+s, but still undesirably slow).
I believe Piwik automatically optimises tables periodically (and I tried doing this manually myself) but that doesn’t seem to be the issue.
I’ve also tried removing, then readding the keys to see if that would help, but it didn’t.
I realise that this may not be a Piwik problem (still seems odd that the query should be slow), but was wondering if anyone here would have more insight into the issue and maybe possible pointers?
Thanks a lot for reading!