Optimising this query?


#1

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!


(Matthieu Aubry) #2

Thanks for the post. How many visits per day do you have?

is it an intranet or normal website?


#3

Thanks again for the response matt
It’s a somewhat private website, which gets around 22-35k pageviews per day. Sorry for not mentioning it before.

Hope that helps.


(Matthieu Aubry) #4

Can you try the suggestion in: How to - Analytics Platform - Matomo
IT might speed up the code in your use case?


#5

Thanks for the suggestion matt.
I’ve tried that, and I don’t seem to get any CPU spikes from MySQL. I’ll post back if I do.