Re: BUG #5946: Long exclusive lock taken by vacuum (not full)

On Mon, Mar 28, 2011 at 2:20 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Mar 28, 2011 at 12:29 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> I think we've had a number of pieces of evidence that suggest that
>>> extending 8kB at a time is too costly, but I agree with Greg that the
>>> idea of extending an arbitrarily large table by 10% at a time is
>>> pretty frightening - that could involve allocating a gigantic amount
>>> of space on a big table.  I would be inclined to do something like
>>> extend by 10% of table or 1MB, whichever is smaller.
>>
>> Sure, something like that sounds sane, though the precise numbers
>> need some validation.
>
> Yeah.
>
>>> ... And a 1MB extension is probably also small enough
>>> that we can do it in the foreground without too much of a hiccup.
>>
>> Less than convinced about this.
>
> Well, I guess we can always try it and see.

Another approach might be to do something "adaptive"...

The iterative process might be wrapped with something like the following:

- Grab timestamp
- Grab exclusive lock
- Process [Some number of pages]
- Check time.
- If [# of ms] have passed then check to see if anyone else has a lock
O/S on the table.
- Commit & give up the lock for a bit if they do
- Go back and process more pages if they don't

This offers 3 parameters that are amenable to management:
- How many pages to process at a time
- How long to process between checking for lock requests
- How long to give up processing

Robert's suggestion would be consistent with these being set to (128,?,?).

The adverse impact would be kept pretty small by something like (16,
10ms, 30ms).

And if the table *isn't* being avidly used, it can iterate
incessantly, not giving up the lock, because nobody else cares.

In the "busy with lots of other users of that table" case, it'll take
quite a long time to get the table's extra extensions truncated.
Indeed, it's pretty easy for other things to get *heavily* in the way.
--
http://linuxfinances.info/info/linuxdistributions.html