Huge performance problem with 1.7.1

Support & Bugs
1

Hi, I’m currently seeing a huge performance problem on our internal Piwik installation. We have about 7,000 employees and a fairly active Intranet that we’re tracking.

Currently running 1.7.1 and our MySQL slow query log is showing this query to be the main problem:

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

                                FROM tracker_log_visit
                                WHERE visit_last_action_time >= '2012-02-28 10:57:31' AND idsite = '7' AND idvisitor = '<91><CF>x6qwM<C0>'
                                ORDER BY visit_last_action_time DESC
                                LIMIT 1;

Doing an EXPLAIN on it shows that it’s not using an index for the ORDER BY clause:

+----+-------------+-------------------+------+---------------------------------------------------------------------------+------------------------+---------+-------------+------+-----------------------------+
| id | select_type | table             | type | possible_keys                                                             | key                    | key_len | ref         | rows | Extra                       |
+----+-------------+-------------------+------+---------------------------------------------------------------------------+------------------------+---------+-------------+------+-----------------------------+
|  1 | SIMPLE      | tracker_log_visit | ref  | index_idsite_config_datetime,index_idsite_datetime,index_idsite_idvisitor | index_idsite_idvisitor | 12      | const,const |    1 | Using where; Using filesort |
+----+-------------+-------------------+------+---------------------------------------------------------------------------+------------------------+---------+-------------+------+-----------------------------+
1 row in set (0.97 sec)

I see a reference to removing this clause in this changeset:

http://dev.piwik.org/trac/changeset/5897

So my question is: is it safe to go ahead and remove the ORDER BY clause to resolve the problem I’m currently seeing?

At present, I have had to remove access to Piwik as our database server runs out of connections and the server’s load average goes sky high with access enabled.

The exact same configuration was working perfectly yesterday!

Many thanks.

2

Thanks for the report. Yes it is safe to remove this ORDER BY. Please try that and report here if it fixes all performance issues.

3

Hi Matt. Thanks for confirming that it’s safe to remove this clause. I’ll make it so in a moment. The weird thing about this was that I bit the bullet and put Piwik back into service and after an initial flurry of traffic (and very high load averages), it subsided and went back to normal!

I’ll see whether this brings the load average down even more!

4

The performance today was, thankfully, back to normal but I’ll be keeping a close eye on things. Many thanks.

5

Do you mean that it is normal after making the change?

Or without removing ORDER BY?

6

Hi Matt. Apologies for not being clear.

I put Piwik back into service, without removing the ORDER BY clause, to see how it got on (and to try not to lose too much data) whilst I waited for confirmation that it was safe to remove it. Surprisingly it seemed to recover, after an initially high load.

After you confirmed that it was safe to remove that clause, I removed it and for the rest of the day, everything was working fine.

The only slow queries that have been logged since have been during our daily archive.sh run.

7

hillardn, can you please try again to put back the ORDER BY back in, and confirm that it is working fine?
I’m interested to see even if the load goes up. It shouldn’t. I plan to leave the ORDER BY in, unless you prove that it is causing a problem. Thanks for helping confirming this

8

hillardn, any possibilty to try with the ORDER BY back in ?

If I get no tip from you, since nobody else reported an issue, I will close the ticket…