Slow Queries

Support & Bugs
1

We’ve been running Piwik for a year now and never experienced any problems. Recently we added another site to Piwik, both combined have about 50.000 pageviews a day, so that shouldn’t be the problem. MySQL also has sufficient memory (we have enough free memory as well). We purge old logs every 2 months (61 days).

Our slow query log shows a lot of these:

# Time: 120402 22:25:20
# User@Host: nsane_piwik[nsane_piwik] @ localhost []
# Query_time: 43.315927  Lock_time: 0.000127 Rows_sent: 0  Rows_examined: 824
use piwik_analytics;
SET timestamp=1333405520;
( 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-04-02 21:54:37' AND idsite = '1'  AND config_id = '_8Í\r•ô°'
					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-04-02 21:54:37' AND idsite = '1'  AND idvisitor = 'óPHÙÖÈìâ'
					LIMIT 1
			 ) 
					ORDER BY priority DESC 
					LIMIT 1;

Always the same query (SELECT idvisitor). I see no reason for this delay, do any of you? The time it takes for the query to run is between 10 seconds and 60 seconds…

2

Setting ‘trust_visitors_cookies = 1’ fixes this problem, but I’m not sure whether we want to keep this setting enabled at all times…

3

It seems our ‘purge old visitors’ task wasn’t running properly due to some faulty MySQL settings.

The issue is fixed now, without setting trust_visitors_cookies :slight_smile:

I still don’t see why this query should be so slow, even on a large table, it uses an index and only takes the last 30 minutes into account right?

4

Is it an intranet?

5

Nope.

But it was suggested in another topic to enable that setting and it worked (I guess it bypasses the ‘problematic’ query).

But all is well now we setup the ‘Purge old visitor logs’-function correctly.

6

I’m wondering about this too. That query is not using any index as far as being reported on my server. It doesn’t run THAT slow yet but its strange that such an often run query is not using indexes.
Any ideas?

7

Using an index is never a bad idea, I guess.

If I’m not mistaken this specific query only takes into account the last 15 or 30 minutes? It might be a good idea to create a setting which enables admins to use a memory table which stores the visits initially and a normal MyISAM table which is updated every x minutes (this way there would only be 30 to 60 minutes of visits in memory).

Downside of this would obviously be that if the MySQL server were to go down (or restarted) the last 30 to 60 minutes of visits would be lost.

When thinking about this it came to mind that it wouldn’t even be necessary to use a memory table: even a separate MyISAM table which is periodically ‘emptied’ and written to the ‘real’ visits table would improve performance (especially for high traffic sites).

Moving the visits from the ‘fast’ table to the ‘actual’ table could cause temporary locking issues, but then again I think it would be safe to run this query without locking as the data which is moved from the ‘fast’ table to the ‘actual’ table will not be written to anyway. (So just select all data from the ‘fast’ table that is over 15 or 30 (whichever it is) minutes old, save the unique identifier and timestamp of the ‘latest row’ marked for moving, move the selected data to the ‘actual’ table, remove the data from the ‘fast’ table.)