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

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

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?

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 #

"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

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.