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

Re: Nice vacuums

From: "Michael Paesold" <mpaesold(at)gmx(dot)at>
To: "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 16:23:46
Message-ID: 026601c4b853$81f749c0$ad01a8c0@zaphod (view raw or flat)
Thread:
Lists: pgsql-hackers
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

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 


In response to

Responses

pgsql-hackers by date

Next:From: Greg StarkDate: 2004-10-22 16:54:58
Subject: Re: code question: storing INTO relation
Previous:From: Tom LaneDate: 2004-10-22 16:06:20
Subject: Re: Nice vacuums

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