@anonymous-piwik-user opened this Issue on June 5th 2012

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.

@mattab commented on June 6th 2012 Owner

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?

@anonymous-piwik-user commented on June 6th 2012

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.

@diosmosis commented on June 7th 2012 Member

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?

@anonymous-piwik-user commented on June 7th 2012

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)

@diosmosis commented on June 20th 2012 Member

(In [6484]) 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.

@diosmosis commented on June 20th 2012 Member

@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.

@anonymous-piwik-user commented on June 20th 2012


Is there already a date scheduled for the next release? I cannot use a pre-release on our production server.

@diosmosis commented on July 19th 2012 Member

(In [6519]) Refs #3196, forgot to use segmented query strategy w/ log_action purging.

@mattab commented on July 26th 2012 Owner

Nice fixes & Very nicely commented!

@anonymous-piwik-user commented on August 16th 2012

Just installed the new version 1.8.3 and it works as expected. Thx!

This Issue was closed on August 16th 2012
Powered by GitHub Issue Mirror