Re: vacuumdb hanging database cluster

On Monday 26 July 2004 2:18 pm, Tom Lane wrote:
> Steve Crawford <scrawford(at)pinpointresearch(dot)com> writes:
> > 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.
>
> I suspect what must have happened is that the vacuum process was
> trying to vacuum one of the shared catalogs (pg_database or
> pg_shadow), and was blocked trying to get exclusive lock because
> someone else (the "idle in transaction" guy) was holding some lock
> on that table. At this point all incoming connections, to any
> database, will block behind the VACUUM until the idle guy closes
> his transaction and thereby releases his lock.

I agree that it has the "smell" of a system-level lock - I just
couldn't get any info from pg_locks till it was cleared.

> > 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.
>
> What was it waiting on? Since it was idle instead of waiting, the
> problem must have been on the client side. I've not heard of
> pg_dump just going to sleep for no reason...

Beats the heck out of me. We periodically dump some selected small
tables via a script using:
pg_dump -i -h $dbhost -U $dbuser -t $dumptable > dumpfile

It's very vanilla and generally works fine but sometimes (perhaps 1
per 1000+ runs) ends up idle in transaction. I'm going to take a much
closer look at pg_locks next time it happens.

The -i is because pg_dump on the client machine is 7.4.2 and the
server is 7.4.1 but that doesn't seem to be a problem.

> > 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.
>
> My recommendation would be to lose the --full. If you're doing
> sufficiently frequent vacuuming you have no need for that, and
> getting rid of it means vacuum doesn't take exclusive table locks.
> That means it will neither block nor be blocked by ordinary readers
> and writers.

Yes, that's where I've headed. I'll save the occasional full vacuum
for manual running when I can watch it.

Thanks,
Steve