From: Russell Smith <mr-russ@pws.com.au>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: Markus Schaber <schabi@logix-tt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Default autovacuum settings too conservative
Date: Wed, 08 Feb 2006 12:49:54 +1100
Message-ID: <43E94E42.3080605@pws.com.au> (raw)
In-Reply-To: <20060208010339.GT38134@pervasive.com>
References: <20060201211633.GM95850@pervasive.com>
<43E12A03.6070906@zeut.net>
<20060207050545.GZ1240@pervasive.com>
<m38xsn4rvm.fsf@mobile.int.cbbrowne.com>
<20060207072621.GI13592@pervasive.com>
<43E89506.40903@logix-tt.com>
<20060208010339.GT38134@pervasive.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mr-russ@pws.com.au, jnasby@pervasive.com, schabi@logix-tt.com
Subject: Re: Default autovacuum settings too conservative
In-Reply-To: <43E94E42.3080605@pws.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox