Thread: Nice vacuums
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 runsnice(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.
"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.