Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

"Estimated database size after purge" causing long lasting SQL query #3196

Closed
anonymous-piwik-user opened this Issue · 10 comments

3 participants

Anonymous Piwik user Matthieu Aubry Benaka
Anonymous Piwik user

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.

Matthieu Aubry
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

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.

Benaka
Collaborator

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

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)

Benaka
Collaborator

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

Benaka
Collaborator

@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

Thx!

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

Benaka
Collaborator

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

Matthieu Aubry
Owner

Nice fixes & Very nicely commented!

Anonymous Piwik user

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

This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.