Re: blocking automatic vacuum

Hi Tom,

hmm. thanks for your answer.
i'm pretty sure there is no repetitive ddl happen on this or any other
table. i checked this with the developers.

but if i had an anti-wraparound vacuum, then i should see warnings like
these in the log. am i right? I don't have any warnings that day.

WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

the table shared_gameset belonging to the vacuumed table
pg_toast.pg_toast_42964236 is new and exists only for about one month.

the table was also vacuumed the day before.
2010-06-17 20:20:41.044 CEST :16787 0 LOG: automatic
vacuum of table "gd.pg_toast.pg_toast_42964236": index scans: 1
pages: 0 removed, 1758012 remain
tuples: 718132 removed, 703020 remain
system usage: CPU 0.02s/0.01u sec elapsed 12354.51 sec

other statements on that table are
- delete ... where timstamp < ....
- select * from ....
but, that's it.

If you wish i can send you the complete log for that day.

best regards,
Uwe

On 22 June 2010 16:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com> writes:
> > last ween i've seen a blocking "automatic vacuum".
> > as i understood, this is not supposed to happen. in the past i saw vacuum
> > processes disappear, in case of the need of a lock.
>
> What that sounds like is it was an anti-wraparound vacuum. Autovacuum
> won't cancel those to avoid delaying other processes.
>
> Now, RowExclusiveLock doesn't conflict with an autovacuum, so there is
> more going on here than you've showed us. The other obvious question is
> how did you get to the point where an anti-wraparound vacuum became
> necessary.
>
> I speculate that you are doing something that does conflict with vacuum
> (ie, SHARE UPDATE EXCLUSIVE lock or higher), and are doing it so often
> that regular autovacuum runs on the table never manage to complete.
> This is very bad, because you're going to have a serious bloat problem
> if autovac keeps getting canceled. You need to look at what sort of DDL
> you are repetitively executing on that table, and find a way to do it a
> lot less often.
>
> regards, tom lane
>