When I open the privacy tab in the admin settings, I can see that the MySQL server is blocked for around 1-2 minutes with this query:
SELECT idvisit FROM piwik_log_visit WHERE '2009-12-18 00:00:00' > visit_last_action_time AND idvisit > 0 ORDER BY idvisit DESC LIMIT 1
I guess that this is caused by the "Estimated database size after purge" function. Our DB is around 10 GB big. "Delete logs older than" is set to 900. CPU is a Dual Xeon 3050. RAM 2 gb.
As a result of that query, multiple other queries get blocked until it is finished.
IMHO that value should only be fetched if the user explicitly clicks on a button or something like that.
Thanks for the report! good point, I guess we should run the stats report by default only if the number of rows is not huge eg. below 1M we run by default, above it requires a click to generate estimate?
Yes, sounds good in case that first query doesnt also load the server.
btw: shouldnt the milestone be 1.8.3? 1.8.2 is out already.
I think this could be fixed in the same way that the log deletion feature deals w/ large DELETEs: looping over a chunk of the table at a time. The only other issue I can see is the amount of requests that get made when changing form values, though I can think of a couple ways to fix that. What do you guys think?
@larson Out of curiosity, what storage engine are you using?
Storage engine is MyISAM.
Just to make sure: Its the SELECT thats causing the problem, not the DELETE (not sure if I understood you right)
(In ) Fixes #3196, modified long running queries in PrivacyManager to use segmented strategy. Added option to disable automatic database size estimate in data purging feature, and made estimate load only by AJAX and never when getting the index.
@larsen Just committed a fix for this. The long SELECT (and other long SELECTs) are now broken up into smaller queries so a table will never be locked for too long (in my tests, the log_visit table was locked for somewhere between 5s-12s for each small query).
Also, I added a config option, 'enable_auto_database_size_estimate', which you can set to 0 if you use the PrivacySettings page a lot and don't want the extra queries to be run.
Let me know if you still have problems.
Is there already a date scheduled for the next release? I cannot use a pre-release on our production server.
(In ) Refs #3196, forgot to use segmented query strategy w/ log_action purging.
Nice fixes & Very nicely commented!
Just installed the new version 1.8.3 and it works as expected. Thx!