Performance regression in tracker for high traffic website
|Reported by:||ghosts||Owned by:|
|Priority:||major||Milestone:||1.7.x - Piwik 1.7.1|
i have also a problem with very high load.
on my mysql server i see lots of querys like the following one, and each one is running over 1 minute ....
case when idvisitor = 'W?O????' then 1 else 0 end AS priority,
, custom_var_k1, custom_var_v1,
FROM piwik_log_visit WHERE visit_last_action_time >= '2012-02-16 13:39:17'
AND idsite = '3' AND (idvisitor = 'W?O????' OR config_id = '?j?_6?')
ORDER BY priority DESC, visit_last_action_time DESC
i have broken it down to the fact that according to an explain there are thousands of estimated rows:
* 1. row *
Extra: Using where; Using filesort
the complete piwik_log_visit table have 781971 entries in total
i played a little bit with the select statement and ended up with an interresting observation, after i told the select to ignore all indexes, the statement runs in 0,8 seconds "IGNORE INDEX (index_idsite_idvisitor,index_idsite_datetime,index_idsite_config_datetime)" which is a more acceptable time and got my server again up and running, but still is not optimal.
i don't understand why this query much better without indexes as with the given ones ...
I have found a changeset that might have changed the behavior to this: r5531
i think one of the problems is that with the OR in the WHERE part the indexes didn't match as good and the second is that for the ordering on priority the server will have examine each row for the case on idvisitors, which will take its time with that much estimatet rows ... :(
Change History (8)
comment:1 Changed 22 months ago by matt (mattab)
- Summary changed from High CPU Load because of long sortin in log_visit table to Performance regression in tracker for high traffic website
comment:5 Changed 22 months ago by matt (mattab)
- Resolution set to fixed
- Status changed from new to closed