Re: pg_autovacuum vacuum cost variables patch

Hello, as per some discussion on the lists a few days ago, I am
submitting this patch against the CVS version of pg_autovacuum.

This patch adds 5 new command line options to pg_autovacuum that
directly correspond to the 5 new vacuum cost GUC variables in 8.0. I
have done some simple testing and this patch works Ok on my FC3 box.

Two questions:
1) It is my understanding that these new GUC vars only effect vacuum.
That is they do NOT have any effect on an analyze command right? (I ask
since I'm only setting the vars before I issue a vacuum command)
2) Does anyone have any better suggestions for the letters used for the
new pg_autovacuum command line args? Nothing obvious came to mind, so I
just basically picked at random resulting in this:
-c vacuum_cost_delay
-C vacuum_cost_page_hit
-m vacuum_cost_page_miss
-n vacuum_cost_page_dirty
-N vacuum_cost_limit
Any better ideas?

Please review and if deemed accecptable, please apply to CVS HEAD.

Thanks,

Matthew O'Connor

Matthew T. O'Connor wrote:

> Two questions:
> 1) It is my understanding that these new GUC vars only effect vacuum.
> That is they do NOT have any effect on an analyze command right? (I ask
> since I'm only setting the vars before I issue a vacuum command)

No, vacuum also affects analyze alone (cvs tip here):
(2.5 seconds -> 50 seconds)

test=# SET vacuum_cost_delay TO 0;
SET
Time: 0.308 ms
test=# analyze;
ANALYZE
Time: 2591.259 ms
test=# SET vacuum_cost_delay TO 10;
SET
Time: 0.309 ms
test=# analyze;
ANALYZE
Time: 51737.896 ms

And it seems it affects analyze much more than vacuum, at least if there is
*nothing* to vacuum... (2 seconds -> 8 seconds)

test=# SET vacuum_cost_delay TO 0;
SET
Time: 0.261 ms
test=# VACUUM;
VACUUM
Time: 1973.137 ms
test=# SET vacuum_cost_delay TO 10;
SET
Time: 0.236 ms
test=# vacuum;
VACUUM
Time: 7966.085 ms

I suggest you also issue the SET commands for analyze also. ISTM that there
is also no distinction between VACUUM and VACUUM FULL, but I think
pg_autovacuum never does a vacuum full, so there is no problem with that.

Best Regards,
Michael Paesold

Michael Paesold wrote:

> Matthew T. O'Connor wrote:
>
>> Two questions:
>> 1) It is my understanding that these new GUC vars only effect vacuum.
>> That is they do NOT have any effect on an analyze command right? (I ask
>> since I'm only setting the vars before I issue a vacuum command)
>
>
> No, vacuum also affects analyze alone (cvs tip here):
> (2.5 seconds -> 50 seconds)
>
> [snip examples...]

> I suggest you also issue the SET commands for analyze also. ISTM that
> there is also no distinction between VACUUM and VACUUM FULL, but I
> think pg_autovacuum never does a vacuum full, so there is no problem
> with that.

Ok, I'll do that too.

"Michael Paesold" <mpaesold(at)gmx(dot)at> writes:
> And it seems it affects analyze much more than vacuum, at least if there is
> *nothing* to vacuum... (2 seconds -> 8 seconds)

Fixed. The original coding was charging a page fetch cost for each row
on each page that analyze looked at :-(

regards, tom lane