Re: Default autovacuum settings too conservative

Jim C. Nasby wrote:
> On Tue, Feb 07, 2006 at 01:39:34PM +0100, Markus Schaber wrote:
>
>>Hi, Jim,
>>
>>Jim C. Nasby wrote:
>>
>>
>>>What we really need is a replacement for vacuum_delay that takes
>>>PostgreSQL generated IO activity into account...
>>
>>There are also other ideas which can make vacuum less painfull:
>>
>>- Use a "delete"-map (like the free space map) so vacuum can quickly
>>find the pages to look at.
>
>
> Already on TODO.
>
>
>>- Have vacuum end its transaction after a certain amount of work, and
>>restart at the same page later.
>
>
> AFAIK this isn't possible with the current way vacuum works.

There was a patch posted for this in the 8.0 cycle, but it was said to
be not useful. I think it's possibly useful for large tables and with
autovac only.

>
>
>>- Have vacuum full search good candidates with non-stopping lock (and
>>usage of delete-map and fsm), then doing {lock, recheck, move, unlock}
>>in small amounts of data with delay between.
>
>
> This isn't an issue of locks, it's an issue of long-running
> transactions. It *might* be possible for vacuum to break work into
> smaller transactions, but I'm pretty sure that would be a non-trivial
> amount of hacking.

When tables are tracked individually for wraparound, the longest
transaction required for vacuuming will be one to vacuum one table.
With delete-map and other functions, the time for that transaction may
be reduced. Partial vacuum of large tables is an option, but again
requires some real smarts in the autovac code to track wraparound issues.

>
>
>>- Introducing some load measurement, and a pressure measurement (number
>>of deleted rows, TID wraparound etc.). Then start vacuum when load is
>>low or pressure is very high. Tune other parameters (like "certain
>>amount of work" depending on those measures.
>
>
> Which is essentially what I was suggesting...
>
>
>>All of them are a lot of code to hack, but although I'm not a postgresql
>>core developer, I am keen enough to invite you to send patches. :-)
>
>
> Well, if you know C then you're already 1 step closer to being able to
> change these kinds of things than I am.

Regards

Russell Smith