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. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
