Re: vacuumdb hanging database cluster

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> Steve Crawford
> Sent: Monday, July 26, 2004 1:23 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] vacuumdb hanging database cluster
>
>
> When I run:
> vacuumdb --full --all --analyze --quiet
> on my database cluster it will complete in < 2 minutes (this cluster
> is a few million total rows and ~2GB).
>
> After testing, I set this up as an off-hours cron job and it worked
> fine for several days then hung the whole database. After my pager
> pulled me from bed I found the vacuumdb process still running but the
> vacuum process on the first database (alphabetically) was showing it
> was waiting:
> postgres: vacuumdb --full --all --analyze --quiet
> postgres: postgres firstdb [local] VACUUM waiting
>
> A couple hundred processes were showing as "startup waiting" and one
> was "idle in transaction". The process in the "VACUUM waiting" state
> was the only one connected to that database - all other connections
> were to other databases.
>
> CPU and disk utilization were essentially zero. Suspecting a lock
> problem I attempted to use a pre-existing connection to view pg_locks
> but it would not respond.
>
> I killed the vacuum process and all the processes in the "waiting"
> states cleared within a second or two and system returned to normal.
> The pg_locks query also returned but showed no useful info.
>
> I tracked down the process that was "idle in transaction" and
> it was a
> pg_dump process running on another machine. This process does a
> periodic dump of one very small table and should complete in a
> fraction of a second but was still waiting since the previous day -
> apparently without deleterious effects.
>
> There was no useful info in the log.
>
> I've stopped running the vacuum full job via cron till I can
> trust it.
> Any ideas on how to track/prevent this behavior? Server is version
> 7.4.1 and my web searches have proved futile.

I have seen problematic behavior when one vacuum starts after another is
already running.
It might be a good idea to semaphore vacuum operations.
But my experience is with an older version of PostgreSQL, so the
problems you are seeing might be totally unrelated.