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

Re: Full Vacuum/Reindex vs autovacuum

From: Jason Long <jason(at)octgsoftware(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Full Vacuum/Reindex vs autovacuum
Date: 2011-02-28 19:18:33
Message-ID: 1298920713.2655.3.camel@localhost.fx60 (view raw or flat)
Thread:
Lists: pgsql-general
On Mon, 2010-11-08 at 16:23 -0700, Scott Marlowe wrote:
> On Mon, Nov 8, 2010 at 3:42 PM, Jason Long <jason(at)octgsoftware(dot)com> wrote:
> > On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote:
> >> On Mon, Nov 8, 2010 at 11:50 AM, Jason Long <jason(at)octgsoftware(dot)com> wrote:
> >> > I currently have Postgres 9.0 install after an upgrade.  My database is
> >> > relatively small, but complex.  The dump is about 90MB.
> >> >
> >> > Every night when there is no activity I do a full vacuum, a reindex,
> >>
> >> One question, why?
> >>
> >> > and then dump a nightly backup.
> >>
> >> Good idea.
> >>
> >> > Is this optimal with regards to performance?  autovacuum is set to the
> >> > default.
> >>
> >> that depends very much on your answer to the question of why are you
> >> doing it and what you're trying to gain / work around with vacuum full
> >> / reindex every night.
> >>
> >
> > I have been doing this for several years.  Since my database is small
> > and it takes little time to do a full vacuum.  I am doing the reindex
> > because I thought that was recommended after a full vacuum.
> 
> Definitely reindex after a full vacuum on previous versions (i.e.
> before 9.0) I think with 9.0 vacuum full is like a cluster without any
> reordering, so it likely doesn't need reindexing, but I've not played
> with 9.0 much yet.
> 
> > As the data has grown the system is slowing down.  Right now I am
> > looking at ways to improve performance without getting into the queries
> > themselves because I am swamped with new development.
> 
> OK, so it's a standard maintenance procedure you've been doing for a
> while.  That doesn't really explain why you started doing it, but I
> can guess that you had some bloat issues way back when and vacuum full
> fixed them, so doing it got kind of enshrined in the nightly
> maintenance.
> 
> > Is doing the full vacuum and reindex hurting or helping anything?
> 
> It might help a small amount if you've got regular usage patterns.  If
> you routinely update whole tables over and over then it might be
> helping.
> 
> > Any other quick fixes that I can try?
> 
> Increasing work_mem, shared_buffers, changing random_page_cost and /
> or seq_page_cost.
> 
> Log long running queries and run explain analyze on any that show up very often.
> 
> But for real performance, you do often have to "get into the queries"
> because an inefficient query may be something you can cut down to
> 1/10000th the run time with a simple change, and often that change is
> impossible to make by tuning the db, only the query can be tuned.  It
> might be something simple like you need to cast a type to match some
> other type.  Hard to say without looking.
> 
> When a 90Meg database is slow, it's almost always poorly written /
> non-optimized queries at the heart of it.
> 

I stopped doing the nightly vacuum full and reindex.  After 3 months
some queries would not complete within 2 minutes.  Normally these take
less than 5 seconds.  I tried vacuum without full and reindex, but the
problem was still there.  Only vacuum full and reindex returned
performance to normal.  Now I am back to my previous nightly full vacuum
and reindex.

Any suggestions?
 
-- 
Thank you for your time,

Jason Long
CEO and Chief Software Engineer
BS Physics, MS Chemical Engineering
http://www.octgsoftware.com
HJBug Founder and President
http://www.hjbug.com


In response to

pgsql-general by date

Next:From: Martijn van OosterhoutDate: 2011-02-28 19:30:08
Subject: Re: Thoroughly confused about time zones
Previous:From: suresh ramasamyDate: 2011-02-28 19:10:35
Subject: dear friend

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