Обсуждение: Allowing VACUUM to time out when waiting for locks?

We have a frequently updated (peak > 5/sec) table with about 1000 rows.

We run VACCUM FULL on this table every 5 minutes.

The regular updates are not long in duration, and the vacuum is fast, so they
do not produce noticeable delays.

When we run a pg_dump on the database:

- the dump takes a long standing AccessShareLock lock on this table
(the database is large, and the table is locked for the duration).

- the regular updates run quite happily

- the VACUUM FULL comes along and asks for a AccessExclusiveLock  (which is not granted due to PG_DUMP).

- the regular updates hang until the dump completes

Is it possible to set up a vacuum to fail if a lock is not granted in
a limited period of time (eg. 1 minute)?

We could use lock files to synchronize our dumps with our vacuums, but
were hoping for a way of managing this within the DB so that ad-hoc dumps
will not cause a problem.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp.mit.edu:11371       |/ 

On Sun, Jan 30, 2005 at 01:23:11 +1100, Philip Warner <pjw@rhyme.com.au> wrote:
> 
> We have a frequently updated (peak > 5/sec) table with about 1000 rows.
> 
> We run VACCUM FULL on this table every 5 minutes.

Why not just use plain VACUUM? The table will reach a steady state size.
You should only need to use VACUUM FULL if an event causes the table
to bloat to significantly more than steady state size and you want to reduce
the size again.

Philip Warner <pjw@rhyme.com.au> writes:
> We have a frequently updated (peak > 5/sec) table with about 1000 rows.
> We run VACCUM FULL on this table every 5 minutes.

I agree with Bruno's comment that you shouldn't be doing that in the
first place.  Plain vacuum (perhaps executed even more often, like
once a minute) will cause fewer locking headaches.

> Is it possible to set up a vacuum to fail if a lock is not granted in
> a limited period of time (eg. 1 minute)?

I think you could do that by setting a statement timeout.
        regards, tom lane

At 02:53 AM 30/01/2005, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
> > We have a frequently updated (peak > 5/sec) table with about 1000 rows.
> > We run VACCUM FULL on this table every 5 minutes.
>
>Plain vacuum (perhaps executed even more often, like
>once a minute) will cause fewer locking headaches.

We have done both in the past, but found some tables still just grew 
(perhaps just because of infrequent locks that prevented the plain VACUUM). 
I'll go back to the plain VACUUM and monitor the table growth.

Am I correct in saying that the FSM now tracks the entire table, and that 
the FSM parameters just determine how much is stored in memory?


>I think you could do that by setting a statement timeout.

This would be a good solution if we still see growth with plain VACUUM.


Is any type of opportunistic locking likely/planned for a future version 
(ie. a has lock, b asks for conflicting lock, c asks for lock that is OK 
with a but denied by b; so c's lock is allowed and b stays waiting).




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp.mit.edu:11371       |/ 

Philip Warner <pjw@rhyme.com.au> writes:
> Am I correct in saying that the FSM now tracks the entire table, and that 
> the FSM parameters just determine how much is stored in memory?

No.  Any free space that can't be remembered in FSM is lost to use.
(Not completely --- an update of a row on the same page can reuse it ---
but for the most part you want to make FSM large enough to remember all
the useful free space.)

> Is any type of opportunistic locking likely/planned for a future version 
> (ie. a has lock, b asks for conflicting lock, c asks for lock that is OK 
> with a but denied by b; so c's lock is allowed and b stays waiting).

That's deliberately disallowed by the current logic because of the risk
of starving b indefinitely.  IIRC it would be a trivial code change to
do the other, but I doubt it's a good idea.  The typical situation is
exactly a VACUUM that wants an exclusive lock, versus a fairly
continuous stream of shared lock requests for select/insert/update/delete.
        regards, tom lane