Mysql get to max connections when creating segments

Support & Bugs
1

Hi everyone!

I’m using piwik a lot but I have one problem when creating segments.
When I try to create a new one, I select a variable to add, and it starts to “think”. After that, it breaks.

What I’ve found is the following:
Piwik starts to make again and again the same query with different idvisitors:


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_region,
							location_city,
							location_latitude,
							location_longitude,
							referer_name,
							referer_keyword,
							referer_type,
							visitor_count_visits,
							visit_goal_buyer
							 
		
				FROM piwikito_log_visit
				WHERE visit_last_action_time >= '2013-08-23 08:43:05' AND visit_last_action_time <= '2013-08-23 09:43:05' AND idsite = '9' AND idvisitor = '��=��'
				ORDER BY visit_last_action_time DESC
				LIMIT 1

The idvisitor is as it seems, with a strange encoding.

Then, the mysql get to the top of 1000 connections and it breaks.

The machine has the following:
Nginx
php5-fpm
mysql5
24GB RAM
2GB maximum per each php proccess

I hope you can help me with this.
Thank yo so much!

2

Hi,
In addition, I’ve tried to exec the query from mysql directly:


mysql> 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_region, location_city, location_latitude, location_longitude, 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 FROM piwikito_log_visit WHERE visit_last_action_time >= '2013-08-23 08:39:38' AND visit_last_action_time <= '2013-08-23 09:39:38' AND idsite = '36' ORDER BY visit_last_action_time DESC LIMIT 1\G

The difference is that I’ve ommited

AND idvisitor = ‘��=��’
.

This culd be the problem? (The idvisitor rare encoding)
Could it be a piwik code bug?

Thak you so much again.

3

Anyone can help me?

4

idvisitor is a binary string, so that is fine. Can you simply increase the connection limit for MySQL?

5

Hello,
The MySQL connections limit is set on 1000. It should be enough, the thing is that the querys doesn’t finish…

6

These queries dontfinish because they are blocked by another query most likely.

7

Do you know what query could cause the problem? On the server there’s only running piwik.

Why querys execute again and again getting to 1000 querys? If one is blocked it shoud stop querying, shouldn’t it?