From: Greg Smith <greg@2ndquadrant.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Rick <richard.branton@ca.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: autovacuum strategy / parameters
Date: Sat, 01 May 2010 13:11:05 -0400
Message-ID: <4BDC60A9.60108@2ndquadrant.com> (raw)
In-Reply-To: <AANLkTik9xSarBYBapF1CVrPoRuqimyGAFAgOaGgvHpLR@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>
<4BDB5ECE.4010607@agliodbs.com>
<AANLkTik9xSarBYBapF1CVrPoRuqimyGAFAgOaGgvHpLR@mail.gmail.com>
Robert Haas wrote:
> I don't have a stake in the ground on what the right settings are, but
> I think it's fair to say that if you vacuum OR analyze much less
> frequently than what we recommend my default, it might break.
>
I think the default settings are essentially minimum recommended
frequencies. They aren't too terrible for the giant data warehouse case
Josh was suggesting they came from--waiting until there's 20% worth of
dead stuff before kicking off an intensive vacuum is OK when vacuum is
expensive and you're mostly running big queries anyway. And for smaller
tables, the threshold helps it kick in a little earlier. It's unlikely
anyone wants to *increase* those, so that autovacuum runs even less; out
of the box it's not tuned to run very often at all.
If anything, I'd expect people to want to increase how often it runs,
for tables where much less than 20% dead is a problem. The most common
situation I've seen where that's the case is when you have a hotspot of
heavily updated rows in a large table, and this may match some of the
situations that Robert was alluding to seeing. Let's say you have a big
table where 0.5% of the users each update their respective records
heavily, averaging 30 times each. That's only going to result in 15%
dead rows, so no autovacuum. But latency for those users will suffer
greatly, because they might have to do lots of seeking around to get
their little slice of the data.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
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: greg@2ndquadrant.com, robertmhaas@gmail.com, josh@agliodbs.com, richard.branton@ca.com
Subject: Re: autovacuum strategy / parameters
In-Reply-To: <4BDC60A9.60108@2ndquadrant.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