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, whole thread or download thread mbox)
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
HJBug Founder and President

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-2017 The PostgreSQL Global Development Group