Re: Vacuum, Freeze and Analyze: the big picture

Craig Ringer <craig@2ndquadrant.com> wrote:
> On 06/07/2013 04:38 AM, Jeff Janes wrote:
>> Craig Ringer <craig@2ndquadrant.com>

> The problem is that vacuum running too slow tends to result in
> table and index bloat. Which results in less efficient cache use,
> slower scans, and generally worsening performance.
>
> I've repeatedly seen the user attribute the resulting high I/O to
> autovacuum (which is, after all, always working away trying to
> keep up) - and "solving" the problem by further slowing
> autovacuum.
>
> It is very counter-intuitive that to fix the problem the user
> needs to make the background process that's doing the I/O take up
> *more* resources, so that other queries take *even less*.

Exactly.  It can be very hard to convince someone to make
autovacuum more aggressive when they associate its default
configuration with slowness.

>>> -> "I'll whack in some manual VACUUM cron jobs during low load
>>> maintenance hours and hope that keeps the worst of the problem
>>> away, that's what random forum posts on the Internet say to
>>> do".
>>> -> "oh my, why did my DB just do an emergency shutdown?"
>>
>> This one doesn't make much sense to me, unless they mucked
>> around with autovacuum_freeze_max_age as well as turning
>> autovacuum itself off (common practice?).
>
> Unfortunately, yes, as an extension of the above reasoning people
> seem to apply around autovacuum. The now horrifyingly bloated DB
> is being kept vaguely functional by regular cron'd vacuum runs,
> but then autovacuum kicks back in and starts thrashing the
> system. It's already performing really badly because of all the
> bloat so this is more than it can take and performance tanks
> critically. Particularly since it probably has 1000 or more
> backends thrashing away if it's anything like many of the systems
> I've been seeing in the wild.
>
> The operator's response: Panic and find out how to make it stop.
> Once autovacuum quits doing its thing the system returns to
> staggering along and they go back to planning a hardware upgrade
> someday, then suddenly it's emergency wraparound prevention time.

I have seen exactly this pattern multiple times.  They sometimes
completely ignore all advice about turning on and tuning autovacuum
and instead want to know the exact formula for when the the
wraparound prevention autovacuum will trigger, so they can run a
vacuum "just in time" to prevent it -- since they believe this will
minimize disk access and thus give them best performance.  They
often take this opportunity to run VACUUM FULL on the table and
don't see the point of following that with any other form of
VACUUM, so they wipe out their visibility map in the process.

> I suspect vacuum, autovacuum, autovacuum tuning, table and index
> bloat, etc is just too complicated for a lot of people running Pg
> installs to really understand.

The ones who suffer most are those who learn just enough to think
they know how to tune better than the defaults, but not enough to
really understand the full impact of the changes they are making.
I have no particular ideas on what to do about that observation,
unfortunately.

> I'd really, really love to see some feedback-based auto-tuning of
> vacuum.

+1

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company