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.

I could see it being useful in an autovac perspective. Work on a
table for a while, giving up after some period of time, but without
giving up on having done some work.

>>>- 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.

Right. And part of the trouble is that you lose certainty that you
have covered off transaction wraparound.

> 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.

Unfortunately, "delete-map" *doesn't* help you with the wraparound
problem. The point of the "delete map" or "vacuum space map" is to
allow the VACUUM to only touch the pages known to need vacuuming.

At some point, you still need to walk through the whole table (touched
parts and untouched) in order to make sure that the old tuples are
frozen.

Tracking tables individually does indeed help by making the longest
transaction be the one needed for the largest table. Unfortunately,
that one can't lean on the "delete map"/"vacuum space map" to ignore
parts of the table :-(.
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://linuxdatabases.info/info/slony.html
"Access to a COFF symbol table via ldtbread is even less abstract,
really sucks in general, and should be banned from earth."
-- SCSH 0.5.1 unix.c