Skip site navigation (1) Skip section navigation (2)

Re: BUG #4730: Vacuum full verbose analyze "deadlock"

From: Wayne Conrad <wconrad(at)yagni(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4730: Vacuum full verbose analyze "deadlock"
Date: 2009-03-25 17:54:50
Message-ID: 20090325175449.GK1185@yagni.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Wed, Mar 25, 2009 at 09:15:48AM -0500, Kevin Grittner wrote:
> >>> "Wayne Conrad" <wconrad(at)yagni(dot)com> wrote: 
>  
> > "VACUUM FULL ANALYZE VERBOSE" on a "deadlocks"
>  
> > "VACUUM VERBOSE ANALYZE" (without the "FULL") does not
>  
> You do realize that FULL should not be part of normal maintenance,
> right?  It is sometimes useful to recover from table bloat when normal
> maintenance fails.  Although it is almost always much slower than
> CLUSTER, it has the advantage of not requiring disk space for a second
> copy of the table, but it requires a REINDEX afterward to correct the
> index bloat it causes.  If you are doing a good job of normal
> maintenance, you never, ever should be running VACUUM FULL.
>  
> None of the above means you haven't found a problem worth looking at
> -- I'm not trying to comment on that; but unless you are in the middle
> of recovery from abnormal bloat, you may be able to dodge the problem
> by correcting your maintenance practices.
>  
> -Kevin

Kevin,

We started doing a routine VACUUM FULL ANALYZE on the advice of a
friend after the database started getting slow over time.  We did not
realize that the FULL should never be done.  We will change that to a
VACUUM ANALYZE, without the full, and see how it goes.

We discovered that an occasionally REINDEX was also required to keep
things fast.  We did not realize it was a direct result of the VACUUM
FULL that the REINDEX was required.

Thank you for your advice.  We are, obviously, not DB guys--just
programmers shoved into the role.  We push the buttons and pull the
levers until things appear to work, which is a poor substitute for
actually understanding what we are doing.

Best Regards,
  Wayne Conrad

In response to

Responses

pgsql-bugs by date

Next:From: Thompson, EricDate: 2009-03-25 18:00:39
Subject: Re: BUG #4721: All sub-tables incorrectly included in search plan for partitioned table
Previous:From: Daniel MillerDate: 2009-03-25 16:37:23
Subject: BUG #4733: Feature request: add plpy.query_plan(...) to plpythonu

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group