Re: lazy vacuum sleeps with exclusive lock on table - Mailing list pgsql-hackers

This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Simon Riggs wrote:
> On Fri, 2007-06-29 at 09:29 +0900, ITAGAKI Takahiro wrote:
> > Alvaro Herrera <alvherre@commandprompt.com> wrote:
> > 
> > > What I'm requesting here is that the sleep in count_nondeletable_pages()
> > > be removed and that change backpatched to 8.2 and 8.1.
> > 
> > Agreed. We'd better to shorten the exclusive locking as far as possible.
> 
> That is just one possibility, but I'd like to consider other
> possibilities before we go for that, especially backpatched.
> 
> ISTM holding the lock across many I/Os is the thing that is causing long
> lock times. Removing the sleep may not substantially reduce the time on
> a busy system. Alvaro's example also shows that the number of blocks
> removed could be a substantial number - reminding us that the time the
> lock is held would still be O(N), whereas we would like it to be O(1).
> This is important since we don't even attempt truncation until the
> number of blocks is large enough to be worth bothering with.
> 
> Would it be better to keep the sleep in there, but release and
> re-acquire the lock either side of the sleep? That would allow other
> transactions to progress without long lock waits.
> 
> Currently, releasing the lock is a problem because the new FSM entries
> are added after truncation, so any updates and inserts would probably
> try to extend the relation, thus preventing further truncation. If we
> did things differently, we would have no reason to fail when we attempt
> to re-acquire the lock:
> - analyze where the truncation point would be on the vacuum pass
> - add FSM entries for all blocks below the truncation point. If that is
> below a minimum of 5% of the entries/16 blocks then we can move the
> truncation point higher so that the FSM entry is large enough to allow
> us time to truncate.
> - truncate the file, one bite at a time as we sleep (or max 16 blocks at
> a time if no sleep requested), possibly scanning forwards not back
> 
> I would still like to see VACUUM spin a few times trying to acquire the
> lock before it gives up attempting to truncate. Re-running the whole
> VACUUM just to get another split-second chance to truncate is not very
> useful behaviour either.
> 
> -- 
>   Simon Riggs             
>   EnterpriseDB   http://www.enterprisedb.com
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

--  Bruce Momjian  <bruce@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +