X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id B749D9DC987
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue,  7 Feb 2006 08:39:31 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 31355-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue,  7 Feb 2006 08:39:34 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.logix-tt.com (serval.logix-tt.com [213.239.221.42])
	by postgresql.org (Postfix) with ESMTP id ECC469DC9DA
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Feb 2006 08:39:28 -0400 (AST)
Received: from kingfisher.intern.logix-tt.com (Mc0c4.m.pppool.de
	[89.49.192.196])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.logix-tt.com (Postfix) with ESMTP id 2209124400F;
	Tue,  7 Feb 2006 13:39:34 +0100 (CET)
Received: from [127.0.0.1] (localhost [127.0.0.1])
	by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id AAA9218151EB5;
	Tue,  7 Feb 2006 13:39:34 +0100 (CET)
Message-ID: <43E89506.40903@logix-tt.com>
Date: Tue, 07 Feb 2006 13:39:34 +0100
From: Markus Schaber <schabi@logix-tt.com>
Organization: Logical Tracking and Tracing International AG, Switzerland
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: 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>
In-Reply-To: <20060207072621.GI13592@pervasive.com>
X-Enigmail-Version: 0.93.0.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.125 required=5 tests=[AWL=0.125]
X-Spam-Score: 0.125
X-Spam-Level: 
X-Archive-Number: 200602/55
X-Sequence-Number: 17041

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.

- Have vacuum end its transaction after a certain amount of work, and
restart at the same page later.

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

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

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

Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org