Thread: VACUUM DELAY
Hi all, I have seen the big debat about to have the delay off or on by default. Why not enable it by default and introduce a new parameter to vacuum command itself ? Something like: VACUUM .... WITH DELAY 100; this will permit to change easilly the delay in the maintainance scripts. Regards Gaetano Mendola
On Mon, 2004-08-09 at 05:19, Gaetano Mendola wrote: > Hi all, > I have seen the big debat about to have the delay > off or on by default. > > Why not enable it by default and introduce a new > parameter to vacuum command itself ? Something like: > > > VACUUM .... WITH DELAY 100; > > > this will permit to change easilly the delay in the maintainance > scripts. The problem, I believe, is that any delay at all results in a VERY slow vacuum run (like 3 to 5 times slower) and for some people, this will be such unexpected behaviour they may believe postgresql is broken, or just want the older, faster vacuum, especially in a development environment. Imagine an increase from 1 to 5 minutes on an otherwise duplicate database from a 7.4 machine. I'll personally be running the delay and autovacuum on any machine I'll be running, and I think once the autovacuum is integrated, it might make sense to have a vacuum command just toss an entry in a que saying "vacuum this table next scheduled run" and return immediately with a NOTICE: vacuum (on tablex) scheduled.
On 8/9/2004 7:19 AM, Gaetano Mendola wrote: > Hi all, > I have seen the big debat about to have the delay > off or on by default. > > Why not enable it by default and introduce a new > parameter to vacuum command itself ? Something like: > > > VACUUM .... WITH DELAY 100; It's not just one parameter to tune here. It is a set of parameters that all together need to be viewed as a whole. The slowdown will be affected by the other parameters as well, so turning the millisecond knob only is not even half of the story. Setting the delay to zero simply disables the whole feature at runtime. That is why this discussion was using the delay parameter as a synonym for enabling/disabling the feature by default. Jan > > > this will permit to change easilly the delay in the maintainance > scripts. > > > > Regards > Gaetano Mendola > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > On 8/9/2004 7:19 AM, Gaetano Mendola wrote: > >> Hi all, >> I have seen the big debat about to have the delay >> off or on by default. >> >> Why not enable it by default and introduce a new >> parameter to vacuum command itself ? Something like: >> >> >> VACUUM .... WITH DELAY 100; > > > It's not just one parameter to tune here. It is a set of parameters that > all together need to be viewed as a whole. The slowdown will be affected > by the other parameters as well, so turning the millisecond knob only is > not even half of the story. So the other parameter will inserted in the new sintax too, I think is fundamental the ability of override this values during the vacuum call: VACUUM .... WITH DELAY 100 [ .... ]; Regards Gaetano Mendola
On Mon, Aug 09, 2004 at 07:19:44PM +0200, Gaetano Mendola wrote: > So the other parameter will inserted in the new sintax too, I think is > fundamental > the ability of override this values during the vacuum call: > > VACUUM .... WITH DELAY 100 [ .... ]; What's wrong with SET vacuum_delat 100; SET whatever_parameter 'value'; VACUUM ...; -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Licensee shall have no right to use the Licensed Software for productive or commercial use. (Licencia de StarOffice 6.0 beta)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Alvaro Herrera wrote: | On Mon, Aug 09, 2004 at 07:19:44PM +0200, Gaetano Mendola wrote: | | |>So the other parameter will inserted in the new sintax too, I think is |>fundamental |>the ability of override this values during the vacuum call: |> |>VACUUM .... WITH DELAY 100 [ .... ]; | | | What's wrong with | | SET vacuum_delat 100; | SET whatever_parameter 'value'; | VACUUM ...; Noting wrong but: 1) The parameters and new feature will be spotted out better to new users 2) My shell script will become less hugly :-) Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBF8wv7UpzwH2SGd4RAnSHAJ0QI0Uu9ZVJiMFn3NY5jFT6omdkYwCfZ8pU BaVnYczZ9pGGTBXMurNtj30= =hP7Q -----END PGP SIGNATURE-----
On 8/9/2004 1:19 PM, Gaetano Mendola wrote: > Jan Wieck wrote: > >> On 8/9/2004 7:19 AM, Gaetano Mendola wrote: >> >>> Hi all, >>> I have seen the big debat about to have the delay >>> off or on by default. >>> >>> Why not enable it by default and introduce a new >>> parameter to vacuum command itself ? Something like: >>> >>> >>> VACUUM .... WITH DELAY 100; >> >> >> It's not just one parameter to tune here. It is a set of parameters that >> all together need to be viewed as a whole. The slowdown will be affected >> by the other parameters as well, so turning the millisecond knob only is >> not even half of the story. > > So the other parameter will inserted in the new sintax too, I think is fundamental > the ability of override this values during the vacuum call: > > VACUUM .... WITH DELAY 100 [ .... ]; You can do it right now. set vacuum_cost_delay = 100; vacuum analyze; No need to panic. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Jan Wieck wrote: | On 8/9/2004 1:19 PM, Gaetano Mendola wrote: | |> Jan Wieck wrote: |> |>> On 8/9/2004 7:19 AM, Gaetano Mendola wrote: |>> |>>> Hi all, |>>> I have seen the big debat about to have the delay |>>> off or on by default. |>>> |>>> Why not enable it by default and introduce a new |>>> parameter to vacuum command itself ? Something like: |>>> |>>> |>>> VACUUM .... WITH DELAY 100; |>> |>> |>> |>> It's not just one parameter to tune here. It is a set of parameters |>> that all together need to be viewed as a whole. The slowdown will be |>> affected by the other parameters as well, so turning the millisecond |>> knob only is not even half of the story. |> |> |> So the other parameter will inserted in the new sintax too, I think is |> fundamental |> the ability of override this values during the vacuum call: |> |> VACUUM .... WITH DELAY 100 [ .... ]; | | | You can do it right now. | | set vacuum_cost_delay = 100; | vacuum analyze; | No need to panic. No need to be smarty pants too. I know that it can be possible, after all 4 years for a dummy like I'm, are enough to understand that is possible to change some GUC for a given connection. :-) However I think is annoying to write: set vacuum_cost_delay = 100; vacuum table <big_huge>; set vacuum_cost_delay = 0; set <whatelse>; vacuum table <night_table>; .... .... or even better: psql -c "set vacuum_cost_delay = 100; vacuum analyze;" and what about the utility vacuumdb ? If I remember well this is the first command that need to change GUC in order to change behaviour, I don't think we wrote: set vacuum_mode = full; set vacuum_verbosity = on; vacuum; Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBGAud7UpzwH2SGd4RAjR0AKDw8XLAI2Lo2uqRauwhWJWwGmwYtgCgmI7u WDZvqwUMzuwXN6Z1qqj91vs= =Wxpz -----END PGP SIGNATURE-----
Gaetano Mendola wrote: > However I think is annoying to write: > > set vacuum_cost_delay = 100; > vacuum table <big_huge>; > set vacuum_cost_delay = 0; > set <whatelse>; > vacuum table <night_table>; > .... > .... Well, you are already seting it to zero for night, so why not just set it to non-zero for day? Seems the same to me, or set it to non-zero in postgresql.conf and set it to zero at night. > or even better: > > psql -c "set vacuum_cost_delay = 100; vacuum analyze;" > > and what about the utility vacuumdb ? Anyone using the utility command can use PGOPT to set the GUC I think. Maybe we should mention that in the manual page. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On 8/9/2004 7:41 PM, Gaetano Mendola wrote:
> If I remember well this is the first command that need to change
> GUC in order to change behaviour, I don't think we wrote:
>
> set vacuum_mode = full;
> set vacuum_verbosity = on;
> vacuum;
You got a point here. However, we don't have
SELECT foo FROM bar WHERE baz = 'bumm' NOSEQSCAN;
either, and I hope you don't suggest doing that next :-)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > On 8/9/2004 7:41 PM, Gaetano Mendola wrote: > >> If I remember well this is the first command that need to change >> GUC in order to change behaviour, I don't think we wrote: >> >> set vacuum_mode = full; >> set vacuum_verbosity = on; >> vacuum; > > > You got a point here. However, we don't have > > SELECT foo FROM bar WHERE baz = 'bumm' NOSEQSCAN; > > either, and I hope you don't suggest doing that next :-) Good idea indeed :-) This could be the first step to give some hints to the planner, Informix have it for sure and if I remember well Oracle have it, Sybase have it... Regards Gaetano Mendola