On Thursday 20 November 2003 20:00, Matthew T. O'Connor wrote:
> Shridhar Daithankar wrote:
> > I will submit a patch that would account deletes in analyze threshold.
> > Since you want to delay the analyze, I would calculate analyze count as
>
> deletes are already accounted for in the analyze threshold.
Yes. My bad. Deletes are not accounted in initializing analyze count but later
they are used.
> > I am still wary of inverting vacuum analyze frequency. You think it is
> > better to set inverted default rather than documenting it?
>
> I think inverting the vacuum and analyze frequency is wrong.
Me. Too. ATM all I can think of this patch attached. Josh, is it sufficient
for you?..:-)
Matthew, I am confyused about one thing. Why would autovacuum count updates
while checking for analyze threshold? Analyze does not change statistics
right? ( w.r.t line 1072, pg_autovacuum.c). For updating statistics, only
inserts+deletes should suffice, isn't it?
Other than that, I think autovacuum does everything it can.
Comments?
Shridhar
*** README.pg_autovacuum.orig Thu Nov 20 19:58:29 2003
--- README.pg_autovacuum Thu Nov 20 20:26:39 2003
***************
*** 141,150 ****
depending on the mixture of table activity (insert, update, or
delete):
! - If the number of (inserts + updates + deletes) > AnalyzeThreshold, then
only an analyze is performed.
! - If the number of (deletes + updates) > VacuumThreshold, then a
vacuum analyze is performed.
VacuumThreshold is equal to:
--- 141,150 ----
depending on the mixture of table activity (insert, update, or
delete):
! - If the number of (inserts + updates + deletes) >= AnalyzeThreshold, then
only an analyze is performed.
! - If the number of (deletes + updates) >= VacuumThreshold, then a
vacuum analyze is performed.
VacuumThreshold is equal to:
***************
*** 158,163 ****
--- 158,186 ----
and running ANALYZE more often should not substantially degrade system
performance.
+ Examples:
+
+ Following table shows typical usage of pg_autovacuum settings.
+ These are put here so that a DBA can have some starting point while
+ tuning pg_autovacuum.
+
+ Vacuum is triggered by updates and deletes. So in case of vacuum,
+ last column indicates total of updates and deletes required
+ to trigger vacuum. In case of analyze, the operations would count total
+ number of inserts, updates and deletes.
+
+ Threshold Scaling factor Records No. of Operations
+ 1,000 1 10,000 11,000
+ 1,000 2 10,000 21,000
+ 1,000 0.5 10,000 6,000
+ 1,000 0.1 10,000 2,000
+
+ Although analyze is cheaper operation compared to vacuum,
+ it might be needed less often. The default is to analyze twice as much as
+ vacuum but that might be too aggressive for some installations. It is advised that
+ such installation tune their analyze threshold separately, rather than relying upon
+ the default behaviour.
+
Sleeping:
---------
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
