Re: Vacuum as "easily obtained" locks

In response to Michael Graham <mgraham(at)bloxx(dot)com>:

> On Wed, 2011-08-03 at 10:17 -0400, Tom Lane wrote:
> > Michael Graham <mgraham(at)bloxx(dot)com> writes:
> > > Would my applications
> > > constant polling of the queue mean that the lock could not be easily
> > > obtained?
> >
> > Very possible, depending on what duty cycle is involved there.
>
> Hmm. The clients aren't that aggressive, especially when they failed to
> find data on a previous select, there are 4 clients, they each poll
> every 10 seconds and the select runs in <1ms.

So, under optimal conditions, the table is queried about every 1s. What
about table inserts? Really, there are lots of situations that can cause
a 1ms query to occasionally take a few seconds, so it's possible that
table is locked more often than you realize.

> It might be worth noting that they don't ever disconnect from the
> server, but I assume that is not an issue for getting the
> AccessExclusiveLock on the table?

Unless those clients are starting transactions and leaving them running
for long periods. Some client software is known to do that unless you
specifically tell it not to.

The definitive way to determine this is to monitor the pg_locks table.

> My worry at the moment is that because the table is so large the vacuum
> takes a very long time to run (one has been running for 5hrs) and I
> assume it will continue to run until it is able to get the
> AccessExclusiveLock is so desperately wants.

If it's gotten very bad, you may have to explicitly VACUUM FULL it
manually to get things back under control.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/