pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSetting autovacuum_vacuum_scale_factor to 0 a good idea ?
3+ messages / 2 participants
[nested] [flat]
* Setting autovacuum_vacuum_scale_factor to 0 a good idea ?
@ 2012-09-14 20:11 Sébastien Lorion <sl@thestrangefactory.com>
2012-09-14 21:49 ` Re: Setting autovacuum_vacuum_scale_factor to 0 a good idea ? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Sébastien Lorion @ 2012-09-14 20:11 UTC (permalink / raw)
To: pgsql-performance
I am pondering about this... My thinking is that since *_scale_factor need
to be set manually for largish tables (>1M), why not
set autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor, and
increase the value of autovacuum_vacuum_threshold to, say, 10000, and
autovacuum_analyze_threshold
to 2500 ? What do you think ?
Also, with systems handling 8k-10k tps and dedicated to a single database,
would there be any cons to decreasing autovacuum_naptime to say 15s, so
that the system perf is less spiky ?
Sébastien
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Setting autovacuum_vacuum_scale_factor to 0 a good idea ?
2012-09-14 20:11 Setting autovacuum_vacuum_scale_factor to 0 a good idea ? Sébastien Lorion <sl@thestrangefactory.com>
@ 2012-09-14 21:49 ` Josh Berkus <josh@agliodbs.com>
2012-09-15 02:35 ` Re: Setting autovacuum_vacuum_scale_factor to 0 a good idea ? Sébastien Lorion <sl@thestrangefactory.com>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2012-09-14 21:49 UTC (permalink / raw)
To: pgsql-performance
> I am pondering about this... My thinking is that since *_scale_factor need
> to be set manually for largish tables (>1M), why not
> set autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor, and
> increase the value of autovacuum_vacuum_threshold to, say, 10000, and
> autovacuum_analyze_threshold
> to 2500 ? What do you think ?
I really doubt you want to be vacuuming a large table every 10,000 rows.
Or analyzing every 2500 rows, for that matter. These things aren't
free, or we'd just do them constantly.
Manipulating the analyze thresholds for a large table make sense; on
tables of over 10m rows, I often lower autovacuum_analyze_scale_factor
to 0.02 or 0.01, to get them analyzed a bit more often. But vacuuming
them more often makes no sense.
> Also, with systems handling 8k-10k tps and dedicated to a single database,
> would there be any cons to decreasing autovacuum_naptime to say 15s, so
> that the system perf is less spiky ?
You might also want to consider more autovacuum workers. Although if
you've set the thresholds as above, that's the reason autovacuum is
always busy and not keeping up ...
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Setting autovacuum_vacuum_scale_factor to 0 a good idea ?
2012-09-14 20:11 Setting autovacuum_vacuum_scale_factor to 0 a good idea ? Sébastien Lorion <sl@thestrangefactory.com>
2012-09-14 21:49 ` Re: Setting autovacuum_vacuum_scale_factor to 0 a good idea ? Josh Berkus <josh@agliodbs.com>
@ 2012-09-15 02:35 ` Sébastien Lorion <sl@thestrangefactory.com>
0 siblings, 0 replies; 3+ messages in thread
From: Sébastien Lorion @ 2012-09-15 02:35 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
Ah I see... I thought that by running the vacuum more often, its cost would
be divided in a more or less linear fashion, with a base constant cost.
While I read about the vacuum process, I did not check the source code or
even read about the actual algorithm, so I am sorry for having asked a
nonsensical question :)
It was theoretical, my current database does what you suggest, but I might
increase workers as about 10 tables see a heavy update rate and are quite
large compared to the others.
Sébastien
On Fri, Sep 14, 2012 at 5:49 PM, Josh Berkus <josh@agliodbs.com> wrote:
>
> > I am pondering about this... My thinking is that since *_scale_factor
> need
> > to be set manually for largish tables (>1M), why not
> > set autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor,
> and
> > increase the value of autovacuum_vacuum_threshold to, say, 10000, and
> > autovacuum_analyze_threshold
> > to 2500 ? What do you think ?
>
> I really doubt you want to be vacuuming a large table every 10,000 rows.
> Or analyzing every 2500 rows, for that matter. These things aren't
> free, or we'd just do them constantly.
>
> Manipulating the analyze thresholds for a large table make sense; on
> tables of over 10m rows, I often lower autovacuum_analyze_scale_factor
> to 0.02 or 0.01, to get them analyzed a bit more often. But vacuuming
> them more often makes no sense.
>
> > Also, with systems handling 8k-10k tps and dedicated to a single
> database,
> > would there be any cons to decreasing autovacuum_naptime to say 15s, so
> > that the system perf is less spiky ?
>
> You might also want to consider more autovacuum workers. Although if
> you've set the thresholds as above, that's the reason autovacuum is
> always busy and not keeping up ...
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-09-15 02:35 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-14 20:11 Setting autovacuum_vacuum_scale_factor to 0 a good idea ? Sébastien Lorion <sl@thestrangefactory.com>
2012-09-14 21:49 ` Josh Berkus <josh@agliodbs.com>
2012-09-15 02:35 ` Sébastien Lorion <sl@thestrangefactory.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox