Re: 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(at)postgresql(dot)org

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)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 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(at)Yahoo(dot)com #

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-----

-----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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)Yahoo(dot)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