Re: "VACUUM FULL ANALYZE" vs. Autovacuum Contention

That's a great point about autovacuum_vacuum_scale_factor; I will lower the
value there to 0.2 and see if autovacuum starts doing a better job. (We use
Postgresql 8.3.5 currently, by the way.)

Thanks for the notes and the useful page link on "vacuum full". We are
running "vacuum full" primarily because a number of tables in our database
have a very large amount of data added to them during each day, all of which
is deleted in one large series of "delete from" statements early in the
morning before we perform the vacuum. Comments like the one here (
http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html) led us to
think that with this type of situation (very large deletes daily) autovacuum
would not in the end be sufficient over the long run.

That said, it sounds like if we switched to daily "trucates" of each table
(they can be purged entirely each day) rather than "delete froms", then
there truly would not be any reason to use "vacuum full". Does that sound
plausible?

Thanks again,

Daniel

On Thu, Jul 7, 2011 at 5:30 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> On 07/07/2011 04:30 PM, D C wrote:
>
>>
>> autovacuum_naptime = 30s
>> autovacuum_vacuum_threshold = 200
>> autovacuum_vacuum_scale_factor = 0.5
>> autovacuum_vacuum_cost_delay = 10
>>
>>
> These are slightly strange settings. How did you come up with them? The
> autovacuum_vacuum_scale_factor being so high is particularly dangerous. If
> anything, you should be reducing that from its default of 0.2, not
> increasing it further.
>
>
> In addition to autovacuuming, each day, early, in the morning, we run a
>> full vacuum, like this: "vacuumdb --all --full --analyze". We do not have
>> any special variable set for vacuum in postgresql.conf.
>>
>>
> VACUUM FULL takes an exclusive lock on the table while it runs, and it
> extremely problematic for several other reasons too. See
> http://wiki.postgresql.org/**wiki/VACUUM_FULL<http://wiki.postgresql.org/wiki/VACUUM_FULL>for more information.
>
> You didn't mention your PostgreSQL version so I can't be sure exactly how
> bad of a problem you're causing with this, but you should almost certainly
> stop doing it.
>
>
>
> The problem is that once or twice a week, the "vacuum full analyze" seems
>> to cancel out the autovacuum that has already started at the same time.
>> E.g.,
>>
>>
> Yes. VACUUM FULL needs to take a large lock on the table, and it will kick
> out autovacuum in that case, and cause countless other trouble too. And if
> the VACUUM FULL is already running, other things will end up getting stuck
> waiting for it, and all sorts of locking issues can come out of that.
>
> You should remove the "--full" from your daily routine, reduce
> autovacuum_vacuum_scale_factor back to a reasonable number again, and see
> how things go after that. You're trying to use PostgreSQL in a way it's
> known not to work well right now.
>
>
> I am guessing that we can do the above by setting the
>> "autovacuum_vacuum_cost_limit" to a fairly high value (rather than it not
>> being set at all, as it is right now, and thus inheriting the "200" default
>> value from vacuum_cost_limit).
>>
>>
> The cost limit has nothing to do with the issue you're seeing. It adjust
> how much work autovacuum does at any moment in time, it isn't involved in
> any prioritization.
>
> --
> Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
> Comprehensive and Customized PostgreSQL Training Classes:
> http://www.2ndquadrant.us/**postgresql-training/<http://www.2ndquadrant.us/postgresql-training/>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)**
> org <pgsql-performance(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-performance<http://www.postgresql.org/mailpref/pgsql-performance>
>