Hi all
Pls helppppppp~~
Very High CPU usage on MYSQL after upgrade to 1.7, CPU usage over 300%. What matter? can i return to 1.6? Tks!
Hanky
MYSQL show process & found many update action, it’s normal action?
UPDATE piwik_log_visit SET visit_total_actions = visit_total_actions +1,visit_exit_idaction
our MYSQL Server version: 5.1.48
PHP version 5.3.9 with PHP-FPM
nothing should have changed in the tracker in 1.7 compared to 1.6 - can you try restarting the server, optimize the table, and try again?
please enable mysql slow query log and if you find slow queries, post here?
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
, 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 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
LIMIT 1
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 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.
Same problem here on a Piwik installation for 80k visits/day. Optimizing the tables did not help. With disabling tracking (record_statistics = 0) everything went to normal.
MySQL console shows that a lot of “Sorting result” is going on for
SELECT
idvisitor,
visit_last_action_time,
visit_first_action_time,
idvisit,...
If disable tracking, it will lost visitor information???
Disabling tracking was only a test to know if the tracker is the problem. Do not set record_statistics = 0 if you do not want to loose visitor information.
Thanks for the report. Indeed, it might be a performance regression. The code was changed in: http://dev.piwik.org/trac/changeset/5531
- Can you try to set in your config/config.ini.php the following
[Tracker]
trust_visitors_cookies=1
It will disable the config_id matching, which might improve performance.
- if the above didn’t improve performance, then please apply the following “quick fix patch” which should hopefully fix performance issues:
Index: core/Tracker/Visit.php
===================================================================
--- core/Tracker/Visit.php (revision 5857)
+++ core/Tracker/Visit.php (working copy)
@@ -987,7 +987,7 @@
$selectCustomVariables
FROM ".Piwik_Common::prefixTable('log_visit').
" WHERE ".$where."
- ORDER BY priority DESC, visit_last_action_time DESC
+ ORDER BY visit_last_action_time DESC
LIMIT 1";
$visitRow = Piwik_Tracker::getDatabase()->fetch($sql, $bindSql);
Please report here. We will have to find a proper fix to this issue for sure, which might affect only very large piwik users?
Also, please enable mysql slow query log and put here the slow query log analysis, it will help a lot thanks
It’s work after I set option 1
[Tracker]
trust_visitors_cookies=1
On the other hand, It’s haven’t slow query on our server. Just see many update query running on MYSQL.
PS. Our website over 200k visits/day
Before applying the patch, there are some of the following entries in the slow log query, that logs SQL queries above 2s execution time. Most queries from piwik are below that trigger.
# Query_time: 2.846736 Lock_time: 0.000104 Rows_sent: 1 Rows_examined: 84959
SET timestamp=1329554784;
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 = '8¢÷iïè\Z' then 1 else 0 end AS priority,
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 log_visit WHERE visit_last_action_time >= '2012-02-17 08:46:22'
AND idsite = '1' AND (idvisitor = '8¢÷iïè\Z' OR config_id = '3â<89><8b><82>X¶^B')
ORDER BY priority DESC, visit_last_action_time DESC
LIMIT 1;
After applying the patch the CPU usage dropped significantly.
Thanks for the feedback, I created a ticket: Performance regression in tracker for high traffic website · Issue #2951 · matomo-org/matomo · GitHub
we will need to investigate more, hopefully there is a fix that we can apply that keeps the current logic with optimal performance.
The quick fix above does change the logic a bit, this is not a big issue, but still. I will post updates in the ticket
I think I have found the proper fix for this issue.
[b]Can you please replace your core/Tracker/Visit.php file with this one: http://dev.piwik.org/svn/trunk/core/Tracker/Visit.php
and confirm that Piwik is very fast as it should be?
Your tests are very appreciated! thank you![/b]
I just fixed another slowness in the new code. As far as I know, it now works fine.
But please I really need you to test this one, because my piwik server is not as big as yours, so please guys, let me know ASAP 
We would like to release this bug fix as soon as possible
Hi Matt,
sorry for the delay. Upgraded to 1.7.1 and the CPU performance is much better. Thanks a lot for your work!
Schtorch., great news thanks for following up. Please report any other performance problem you encounter in the future