Opened 23 months ago

Closed 22 months ago

Last modified 20 months ago

#3196 closed Task (fixed)

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

Reported by: larsen Owned by:
Priority: normal Milestone: 1.8.3 - Piwik 1.8.3
Component: Core Keywords:
Cc: cappedfuzz Sensitive: no

Description

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.

Change History (11)

comment:1 Changed 23 months ago by matt (mattab)

  • Cc cappedfuzz added
  • Milestone set to 1.8.2 - Piwik 1.8.2

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?

comment:2 Changed 23 months ago by larsen

Yes, sounds good in case that first query doesn´t also load the server.

btw: shouldn´t the milestone be 1.8.3? 1.8.2 is out already.

comment:3 Changed 23 months ago by capedfuzz (diosmosis)

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?

comment:4 Changed 23 months ago by larsen

Storage engine is MyISAM.

Just to make sure: It´s the SELECT that´s causing the problem, not the DELETE (not sure if I understood you right)

comment:5 Changed 22 months ago by capedfuzz (diosmosis)

  • Resolution set to fixed
  • Status changed from new to closed

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

comment:6 Changed 22 months ago by capedfuzz (diosmosis)

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

comment:7 Changed 22 months ago by larsen

Thx!

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

comment:8 Changed 22 months ago by parisbonbon

comment:9 Changed 21 months ago by capedfuzz (diosmosis)

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

comment:10 Changed 21 months ago by matt (mattab)

Nice fixes & Very nicely commented!

comment:11 Changed 20 months ago by larsen

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

Note: See TracTickets for help on using tickets.