From: Josh Berkus <josh@agliodbs.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Rick <richard.branton@ca.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: autovacuum strategy / parameters
Date: Fri, 30 Apr 2010 15:50:54 -0700
Message-ID: <4BDB5ECE.4010607@agliodbs.com> (raw)
In-Reply-To: <h2o603c8f071004271755v3e56a408hbd23b5c355058aa@mail.gmail.com>
References: <c2d2b6cf-895f-4a4b-aa8b-3adaac33c794@z3g2000yqz.googlegroups.com>
<y2g603c8f071004221155h3874a3c0ybbe2c790e3c1bf80@mail.gmail.com>
<65d9d15d-41d2-4bfa-8cc3-6148b4fb9022@z3g2000yqz.googlegroups.com>
<h2o603c8f071004271755v3e56a408hbd23b5c355058aa@mail.gmail.com>
> My guess is that the reason we run ANALYZE more frequently than vacuum
> (with the default settings) is that ANALYZE is pretty cheap. In many
> cases, if the statistical distribution of the data hasn't changed
> much, then it's not really necessary, but it doesn't cost much either.
> And for certain types of usage patterns, like time series (where the
> maximum value keeps increasing) it's REALLY important to analyze
> frequently.
>
> But having said that, on the systems I've worked with, I've only
> rarely seen a problem caused by not analyzing frequently enough. On
> the other hand, I've seen MANY problems caused by not vacuuming
> enough.
Which is the opposite of my experience; currently we have several
clients who have issues which required more-frequent analyzes on
specific tables. Before 8.4, vacuuming more frequently, especially on
large tables, was very costly; vacuum takes a lot of I/O and CPU. Even
with 8.4 it's not something you want to increase without thinking about
the tradeoffs.
Since I'm responsible for the current defaults, I though I'd explain the
reasoning behind them. I developed and tested them while at Greenplum,
so they are *not* designed for small databases.
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
These two are set to the minimum threshold to avoid having small tables
get vacuum/analyzed continuously, but to make sure that small tables do
get vacuumed & analyzed sometimes.
#autovacuum_vacuum_scale_factor = 0.2
This is set because in my experience, 20% bloat is about the level at
which bloat starts affecting performance; thus, we want to vacuum at
that level but not sooner. This does mean that very large tables which
never have more than 10% updates/deletes don't get vacuumed at all until
freeze_age; this is a *good thing*. VACUUM on large tables is expensive;
you don't *want* to vacuum a billion-row table which has only 100,000
updates.
#autovacuum_analyze_scale_factor = 0.1
The 10% threshold for analyze is there because (a) analyze is cheap, and
(b) 10% changes to a table can result in very bad plans if the changes
are highly skewed towards a specific range, such as additions onto the
end of a time-based table.
The current postgres defaults were tested on DBT2 as well as pgbench,
and in my last 2 years of consulting I've seldom found reason to touch
them except on *specific* tables. So I still feel that they are good
defaults.
It would be worth doing a DBT2/DBT5 test run with different autovac
settings post-8.4 so see if we should specifically change the vacuum
threshold. Pending that, though, I think the current defaults are good
enough.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, robertmhaas@gmail.com, richard.branton@ca.com
Subject: Re: autovacuum strategy / parameters
In-Reply-To: <4BDB5ECE.4010607@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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