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?

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Fri, 22 Oct 2004 11:53:27 -0400
"D'Arcy J.M. Cain" <darcy@druid.net>
Nice vacuums
"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> 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.

Please re-read all the previous threads on the uselessness of nice.

What *would* be useful is an option to turn on the 8.0 vacuum cost delay
features (ie, set non-default values for those GUC variables).

			regards, tom lane

Fri, 22 Oct 2004 12:06:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Nice vacuums 
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 


Fri, 22 Oct 2004 18:23:46 +0200
"Michael Paesold" <mpaesold@gmx.at>
Re: Nice vacuums
Tom Lane wrote:

> What *would* be useful is an option to turn on the 8.0 vacuum cost delay
>
>features (ie, set non-default values for those GUC variables).
>
Just curious, if I were to submit patches in the next week or so that 
added new command line options to pg_autovacuum that corresponded to the 
new vacuum cost delay features, would they still be accepted?  I know we 
are well into beta, but it's also a contrib module, and the changes 
would be pretty simple.

Thoughts?

Fri, 22 Oct 2004 14:29:02 -0400
"Matthew T. O'Connor" <matthew@zeut.net>
Re: Nice vacuums
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@Yahoo.com #

Fri, 22 Oct 2004 14:32:21 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Nice vacuums
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> Tom Lane wrote:
>> What *would* be useful is an option to turn on the 8.0 vacuum cost delay
>> features (ie, set non-default values for those GUC variables).
>> 
> Just curious, if I were to submit patches in the next week or so that 
> added new command line options to pg_autovacuum that corresponded to the 
> new vacuum cost delay features, would they still be accepted?  I know we 
> are well into beta, but it's also a contrib module, and the changes 
> would be pretty simple.

I would be inclined to accept it, since the cost-delay feature certainly
needs to play nicely with autovacuum.  I was actually surprised to
discover this hadn't been done already.

If you could get it done today or tomorrow so we can fit it into beta4,
that would be even better ...

			regards, tom lane

Fri, 22 Oct 2004 14:53:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Nice vacuums 
Tom Lane wrote:

>"Matthew T. O'Connor" <matthew@zeut.net> writes:
>  
>
>> Just curious, if I were to submit patches in the next week or so that
>>
>>added new command line options to pg_autovacuum that corresponded to the 
>>new vacuum cost delay features, would they still be accepted?  I know we 
>>are well into beta, but it's also a contrib module, and the changes 
>>would be pretty simple.
>>    
>>
>
>I would be inclined to accept it, since the cost-delay feature certainly
>needs to play nicely with autovacuum.  I was actually surprised to
>discover this hadn't been done already.
>
>If you could get it done today or tomorrow so we can fit it into beta4,
>that would be even better ...
>

Sorry no can do, my weekend is already shot.  I could possibly have 
something as soon as Sunday or Monday night, we will see. 

As to why this wasn't already done, it was something I was planning on 
doing for 8.0, however after my autovac integration work got dropped, I 
stopped working on it.

Fri, 22 Oct 2004 16:54:55 -0400
"Matthew T. O'Connor" <matthew@zeut.net>
Re: Nice vacuums