Search Postgresql Archives

Re: Are my autovacuum settings too aggressive for this table?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





Live tuples = 19,766,480
Analyze scale factor = 0.001
Analyze thresh = 5000
Thresh + live_tuples * factor = 24,766

So an autovacuum analyze should trigger around 24K tuples modified, is this to little or too much?

This seems too much to me.  Was there a specific problem occurring that spurred this change?  If many of the tuple modifications are occurring on a certain subset of the data which changes the distribution in an important way, then maybe this would be justified.  (But maybe partitioning between active and largely inactive would be a better solution)
 
Same goes for autvacuum vacuum, is it too aggressive?

#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------
autovacuum_naptime = 1h
autovacuum_vacuum_threshold = 10000
autovacuum_analyze_threshold = 5000
autovacuum_vacuum_scale_factor = 0.002
autovacuum_analyze_scale_factor = 0.001


The analyze settings don't seem defensible to me, but maybe you can make a case for them by referring to problems that were showing up in particular queries.
 
The naptime seems particularly indefensible.  If you think you overdid it with some changes, then you should back off those changes.  Not randomly whack around other settings in an attempt to compensate for the first ones, without having some identifiable theory which supports this.  If you do have such a theory, you haven't told us what it might be.

If index-only-scans (and hence rel_allvisible) are particularly important to you, then the autovac settings might make sense.  However, this is unlikely to be true at the global level, but rather on a table by table basis.  But, I don't see the point in setting autovacuum_vacuum_scale_factor = 0.002.  If it needs a drastic decrease (to pair with a well-considered increase in  autovacuum_vacuum_threshold) why would 99% be the correct decrease, rather than 100%?  A nice thing about exact 0 is it stands out as being intentional, as opposed to a typo or a mistranslation between percentage and fraction.

Cheers,

Jeff

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux