Обсуждение: Re: [ADMIN] autovacuum on a -mostly- r/o table

От:
Tobias Brox
Дата:

[Matthew T. O'Connor - Wed at 02:33:10PM -0400]
> In addition autovacuum respects the work of manual or cron based
> vacuums, so if you issue a vacuum right after a daily batch insert /
> update, autovacuum won't repeat the work of that manual vacuum.

I was experimenting a bit with autovacuum now.  To make the least effect
possible, I started with a too high cost_delay/cost_limit-ratio.  The
effect of this was that autovacuum "never" finished the transactions it
started with, and this was actually causing the nightly vacuum to not do
it's job good enough.

От:
"Matthew T. O'Connor"
Дата:

Tobias Brox wrote:
> [Matthew T. O'Connor - Wed at 02:33:10PM -0400]
>
>> In addition autovacuum respects the work of manual or cron based
>> vacuums, so if you issue a vacuum right after a daily batch insert /
>> update, autovacuum won't repeat the work of that manual vacuum.
>>
>
> I was experimenting a bit with autovacuum now.  To make the least effect
> possible, I started with a too high cost_delay/cost_limit-ratio.  The
> effect of this was that autovacuum "never" finished the transactions it
> started with, and this was actually causing the nightly vacuum to not do
> it's job good enough.

Yeah, I think if the delay settings are too high it can cause problems,
that's part of the reason we have yet to turn these on be default since
we won't have enough data to suggest good values.  Can you tell us what
settings you finally settled on?

BTW hopefully for 8.3 we are going to add the concept of maintenance
windows to autovacuum, during these periods you can lower the thresholds
and perhaps even change the delay settings to make autovacuum more
aggressive during the maintenance window.  This hopefully will just
about eliminate the need for nightly cron based vacuum runs.


От:
Tobias Brox
Дата:

[Matthew T. O'Connor - Sun at 10:42:34AM -0400]
> Yeah, I think if the delay settings are too high it can cause problems,
> that's part of the reason we have yet to turn these on be default since
> we won't have enough data to suggest good values.  Can you tell us what
> settings you finally settled on?

I'm still not yet settled, and the project manager is critical to
autovacuum (adds complexity, no obvious benefits from it, we see from
the CPU graphs that it's causing iowait, iowait is bad).  We're going to
run autovacuum biweekly now to see what effect it has on the server
load.

I've been using the cost/delay-setting of 200/200 for a week now, and
I'm going to continue with 100/150 for a while.

Are there any known disadvantages of lowering both values to the extreme
- say, 20/20 instead of 200/200?  That would efficiently mean "sleep as
often as possible, and sleep for 1 ms for each cost unit spent" if
I've understood the system right.

Are there any logs that can help me, and eventually, are there any
ready-made scripts for checking when autovacuum is running, and
eventually for how long it keeps its transactions?  I'll probably write
up something myself if not.


От:
"Jim C. Nasby"
Дата:

On Sun, Oct 15, 2006 at 04:52:12PM +0200, Tobias Brox wrote:
> Are there any logs that can help me, and eventually, are there any
> ready-made scripts for checking when autovacuum is running, and
> eventually for how long it keeps its transactions?  I'll probably write
> up something myself if not.

8.2 adds some stats on when autovac last ran, per-table. I don't
remember if it reports how long it took to vacuum the table, but that
would probably be useful info.
--
Jim Nasby                                            
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

От:
"Larry Rosenman"
Дата:

Jim C. Nasby wrote:
> On Sun, Oct 15, 2006 at 04:52:12PM +0200, Tobias Brox wrote:
>> Are there any logs that can help me, and eventually, are there any
>> ready-made scripts for checking when autovacuum is running, and
>> eventually for how long it keeps its transactions?  I'll probably
>> write up something myself if not.
>
> 8.2 adds some stats on when autovac last ran, per-table. I don't
> remember if it reports how long it took to vacuum the table, but that
> would probably be useful info.

It does NOT.  It's just the timestamp of the END of the vacuum / analyze.

(I'm the author of the patch).


--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 512-248-2683             E-Mail: 
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893