Skip site navigation (1) Skip section navigation (2)

Re: vacuumdb hanging database cluster

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: vacuumdb hanging database cluster
Date: 2004-07-26 21:54:31
Message-ID: 200407261454.31635.scrawford@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-general
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


In response to

Responses

pgsql-general by date

Next:From: David ParkerDate: 2004-07-26 22:14:06
Subject: estimating table size
Previous:From: Tom LaneDate: 2004-07-26 21:18:01
Subject: Re: vacuumdb hanging database cluster

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group