New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Performance regression in tracker for high traffic website #2944
Comments
Thanks for the report. there is a "quick fix" in the forum thread at: http://forum.piwik.org/read.php?2,85727 But I'm not sure how to fix the issue keeping the current logic, while not issuing a slow SQL query. |
thank you for the hotfix. --- ./core/Tracker/Visit.php 2012-02-15 03:42:31.000000000 +0100
in my simple mind it should be a qestion of indexing ... |
(In [5886]) Refs #2944
Note: after the fix, it is still not perfect performance wise:
|
(In [5887]) Refs #2944 |
(In [5892]) Fixes #2944
PLEASE EVERYONE LISTENING would you mind testing the new "core/Tracker/Visit.php" file simply replacing your existing file? It would be great if you could confirm what my tests show, that the code is now very fast. I don't have access to an enormous Piwik as you might have, so your test is very appreciated!! |
(In [5897]) Refs #2944 |
(In [5898]) Refs #2944 Aggressively only looking in the past for how long a visit can be. This is more efficient than looking further back. Note: the build will fail, i will commit test fixes separately |
It looks like it is working fine now. @Ghosts can you please test the new file and confirm it is very fast on your setup too? |
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 ....
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,
case when idvisitor = 'W?O????' then 1 else 0 end AS priority,
visitor_count_visits,
visit_goal_buyer
i have broken it down to the fact that according to an explain there are thousands of estimated rows:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: piwik_log_visit
type: ref
possible_keys: index_idsite_config_datetime,index_idsite_datetime,index_idsite_idvisitor
key: index_idsite_datetime
key_len: 4
ref: const
rows: 145600
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 ... :(
The text was updated successfully, but these errors were encountered: