Skip site navigation (1) Skip section navigation (2)

Re: Nice vacuums

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Michael Paesold <mpaesold(at)gmx(dot)at>
Cc: "D'Arcy J(dot)M(dot) Cain" <darcy(at)druid(dot)net>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: Nice vacuums
Date: 2004-10-22 18:32:21
Message-ID: 41795235.6030101@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On 10/22/2004 12:23 PM, Michael Paesold wrote:

> D'Arcy J.M. Cain wrote:
>> I have an idea for a change to the contributed module pg_autovacuum that
>> I would like to run by people.  What I want to do is make sure that when
>> vacuum (or analyze) runs that it takes no time from actual transactions.
>> To this end I want to add an option (-n?) which runs nice(2) on the
>> process ID of the backend.
>>
>> I realize that there will be a limitation that this can only work when
>> pg_autovacuum is running on the same host as the server.  I plan to
>> handle that by ignoring the new option if the -h option (or equivalent
>> environment variable) is also set.
>>
>> The big question I have is this.  Is this strategy likely to improve my
>> transaction processing?
> 
> There is a much better way available in PostgreSQL 8.0 to reduce the impact 
> of VACUUM: cost-based vacuum delay.
> See: http://developer.postgresql.org/docs/postgres/runtime-config.html

And looking at how my test systems perform under TPC-W load with 
PostgreSQL 8.0 and Slony-I 1.0.5 right now I can confirm that this is 
the right path. I have of course pg_autovacuum running and vacuum delay 
is enabled. The main database server rolls like a Sherman Tank through 
backyard fences ... everything just level without any bumps or dips ... 
and that even under a load of 20+.


Jan

> 
> There are five GUC-variables that control vacuum delay. The most important 
> is vacuum_cost_delay(int), because it actually enables (value >0) or 
> disables (value =0) the feature.
> 
> This can be set during runtime via SET. The default value for 
> vacuum_cost_delay is currently 0.
> 
> So what you could do, is make a new option in pg_autovacuum that will set 
> vacuum_cost_delay before executing vacuum. So one can leave 
> vacuum_cost_delay at zero in postgresql.conf, but enable it for background 
> vacuum in pg_autovacuum.
> 
> Best Regards,
> Michael Paesold 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck(at)Yahoo(dot)com #

In response to

pgsql-hackers by date

Next:From: Simon RiggsDate: 2004-10-22 18:50:59
Subject: ARC Memory Usage analysis
Previous:From: Matthew T. O'ConnorDate: 2004-10-22 18:29:02
Subject: Re: Nice vacuums

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group