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
Attachments:
pg_autovacuum_vacuum_cost.difftext/x-patch; name=pg_autovacuum_vacuum_cost.diffDownload+194-41
-----Original Message-----
From: pgsql-patches-owner@postgresql.org
[mailto:pgsql-patches-owner@postgresql.org] On Behalf Of
Matthew T. O'Connor
Sent: 26 October 2004 06:40
To: pgsql-patches
Subject: [PATCHES] pg_autovacuum vacuum cost variables patchPlease review and if deemed accecptable, please apply to CVS HEAD.
Hi Matthew,
It doesn't look like you modified the Win32 service installation code to
write these options to the registry when installing the service (see
szCommand in InstallService()).
Regards, Dave
Import Notes
Resolved by subject fallback
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
Dave Page wrote:
Hi Matthew,
It doesn't look like you modified the Win32 service installation code to
write these options to the registry when installing the service (see
szCommand in InstallService()).
Oops.... Can you tell I didn't write that part of the code ;-) I'll
take a look at this tonight after work and send in an updated patch.
Matthew
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@gmx.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