Message-ID: <43E94E42.3080605@pws.com.au>
Date: Wed, 08 Feb 2006 12:49:54 +1100
From: Russell Smith <mr-russ@pws.com.au>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013)
MIME-Version: 1.0
To: "Jim C. Nasby" <jnasby@pervasive.com>
CC: Markus Schaber <schabi@logix-tt.com>,
 pgsql-performance@postgresql.org
Subject: Re: Default autovacuum settings too conservative
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>
In-Reply-To: <20060208010339.GT38134@pervasive.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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