From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Smith <greg@2ndquadrant.com>
Cc: 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:25:40 -0400
Message-ID: <5266.1272734740@sss.pgh.pa.us> (raw)
In-Reply-To: <4BDC60A9.60108@2ndquadrant.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>
<4BDC60A9.60108@2ndquadrant.com>
Greg Smith <greg@2ndquadrant.com> writes:
> 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.
With a little luck, HOT will alleviate that case, since HOT updates can
be reclaimed without running vacuum per se. I agree there's a risk
there though.
Now that partial vacuum is available, it'd be a real good thing to
revisit these numbers.
regards, tom lane
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: tgl@sss.pgh.pa.us, greg@2ndquadrant.com, robertmhaas@gmail.com, josh@agliodbs.com, richard.branton@ca.com
Subject: Re: autovacuum strategy / parameters
In-Reply-To: <5266.1272734740@sss.pgh.pa.us>
* 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