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@xxxxxxxxxxxxx> wrote:
Uwe Bartels <uwe.bartels@xxxxxxxxx> writes:What that sounds like is it was an anti-wraparound vacuum. Autovacuum
> 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.
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
