Re: lazy vacuum sleeps with exclusive lock on table

Simon Riggs wrote:
> On Thu, 2007-06-28 at 17:16 -0400, Alvaro Herrera wrote:
>
> > I noticed that lazy vacuum acquires an exclusive lock at the end, to be
> > able to truncate the table. This is not a surprise. If it cannot
> > acquire the lock, it simply skips truncating the table and goes on with
> > life.
> >
> > However, what's problematic is that if a non-zero cost delay has been
> > set, it will happily take naps while determining what to truncate :-(
> > This seems a bad idea. It also may explain why some people is seeing
> > autovacuum blocking other processes. It also readily explains why this
> > is so when there are no non-granted locks for autovacuum.
> >
> > Comments? I think we should remove the sleep in the truncate phase.
>
> Do we have any timings for that lock-out? Even with a largish sleep
> delay, I can't think it's locked out for that long.

I created a table like this:

create table foo (a int);
begin;
insert into foo select * from generate_series(1, 1000000);
rollback;

It took it 14 seconds to truncate with 50ms vacuum delay.

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.

> Seems like VACUUM shouldn't try just once to get the lock. It could be
> very frustrating to wait hours for a VACUUM to finish, only to find a
> small query prevents file truncation. That's just too random. It should
> retry as many times as there are blocks for it to truncate i.e. it tries
> harder to truncate the more it needs to do so.

We don't know how many pages we can truncate until after we have
acquired the exclusive lock and examined the pages in question, scanning
backwards from the end of the table.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support