Re: Vacuum, Freeze and Analyze: the big picture

On 06/07/2013 04:38 AM, Jeff Janes wrote:
> On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
>> "My database is slow"
>> ->
>> "This autovacuum thing is using up lots of I/O and CPU, I'll increase
>> this delay setting here"
>
> Do you think this was the correct diagnosis but with the wrong action
> taken, or was the diagnosis incorrect in the first place (i.e. it may be
> using some IO and CPU, but that isn't what was causing the initial
> problem)? And if the diagnosis was correct, was it causing problems under
> default settings, or only because they already turned off the cost delay?

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*.

>> ->
>> "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 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. I'd really, really love to see some feedback-based
auto-tuning of vacuum.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services