From: Jim C. Nasby <jnasby@pervasive.com>
To: Markus Schaber <schabi@logix-tt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Default autovacuum settings too conservative
Date: Tue, 7 Feb 2006 19:03:39 -0600
Message-ID: <20060208010339.GT38134@pervasive.com> (raw)
In-Reply-To: <43E89506.40903@logix-tt.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>
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.
> - 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.
> - 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.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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: jnasby@pervasive.com, schabi@logix-tt.com
Subject: Re: Default autovacuum settings too conservative
In-Reply-To: <20060208010339.GT38134@pervasive.com>
* 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