Excerpts from Gregg Jaskiewicz's message of vie mar 02 07:44:07 -0300 2012:
> Folks,
>
> I got a system here (8.3.7), that is locked up. Few queries waiting
> for autovacuum aquired locks on a table or two.
> But it looks like autovacuum is also waiting for some semaphore:
>
>
> #0 0x00f07410 in __kernel_vsyscall ()
> #1 0x00252d2b in semop () from /lib/libc.so.6
> #2 0x081ca1ce in PGSemaphoreLock ()
> #3 0x081f590e in ProcWaitForSignal ()
> #4 0x081e8dea in LockBufferForCleanup ()
> #5 0x08160e68 in ?? ()
> #6 0x08161482 in lazy_vacuum_rel ()
> #7 0x081603b5 in ?? ()
> #8 0x081605c4 in vacuum ()
> #9 0x081cd154 in ?? ()
> #10 0x081cd7f1 in ?? ()
> #11 0x081cd911 in StartAutoVacWorker ()
> #12 0x081d6d47 in ?? ()
> #13 <signal handler called>
> #14 0x00f07410 in __kernel_vsyscall ()
> #15 0x00249fcd in ___newselect_nocancel () from /lib/libc.so.6
> #16 0x081d3964 in ?? ()
> #17 0x081d5105 in PostmasterMain ()
> #18 0x0818be60 in main ()
>
> There's still other transcations going on , and working fine - but two
> tables seem to be cut out by autovacuum, and other queries stuck
> waiting for autovacuum to finish.
This vacuum is waiting for some other process to release the buffer
lock. These locks are usually very short-lived, but maybe you have some
other backend doing something weird that won't release it. Do you have
idle processes with transactions open, and if you have, what are they
doing? Do you have prepared transactions open? (see the
pg_prepared_xacts view)
> Any ideas ?
> Is there a possible fix up the 8.3 stream, that would target that ?
I don't think so, though I am not certain.
> (I need leverage to upgrade to 8.3.18, and if that's the case - happy
> days).
Sorry, can't help you there :-)
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
