From: Bill Moran <wmoran@collaborativefusion.com>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performace Optimization for Dummies
Date: Fri, 29 Sep 2006 08:58:02 -0400
Message-ID: <20060929085802.05241905.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <efi9bo$2aja$1@news.hub.org>
References: <efgu4v$276m$1@news.hub.org>
<b42b73150609281017m7f8f9d86xfa32d38cec954af7@mail.gmail.com>
<efh26m$2fd$1@news.hub.org>
<5EE4B6D1-8ECE-4033-A912-DFD9146A5A93@blighty.com>
<efh6nt$11k4$1@news.hub.org>
<6B702C4D-C102-44E3-841C-BF176EAF1CAB@blighty.com>
<20060928235243.GH34238@nasby.net>
<efi9bo$2aja$1@news.hub.org>
In response to "Carlo Stonebanks" <stonec.register@sympatico.ca>:
> >> indexes. I don't know whether autovacuum will also analyze tables
> >> for you automagically, but it would be a good idea to analyze the table
> >
> > It does.
>
> So, I have checked my log and I see an autovacuum running once every minute
> on our various databases being hosted on the server - once every minute!
>
> From what I can see, autovacuum is hitting the db's in question about once
> every five minutes. Does this imply an ANALYZE is being done automatically
> that would meet the requirements we are talking about here? Is there any
> benefit ot explicitly performing an ANALYZE?
>
> (Or does this go hand-in-and with turning off autovacuum...?)
It's only checking to see if vacuum/analyze needs done every 5 minutes.
It may or may not do any actual work at that time, based on how much
the tables have changed. See:
http://www.postgresql.org/docs/8.1/interactive/maintenance.html#AUTOVACUUM
This is a case, during your bulk loads, where autovacuum might actually
hurt you. How many records are you inserting/updating in 5 minutes?
You may be exceeding autovacuum's ability to keep things clean.
I can't say for sure, but I would suspect that you'd be better off not
using autovacuum until after the initial data loads are done. My
guess is that you'll get better performance if you disable autovac and
write manual vacuum/analyze into your load scripts. Exactly how often
to have your script do it is something that will require testing to
figure out, but probably starting with every 100 or so, then adjust
it up and down and see what works best.
Explicitly performing a vacuum or analyze can be very beneficial,
especially if you know what kind of changes your creating in the data.
(Now that I think of it, there's no reason to disable autovac, as it
will notice if you've just manually vacuumed a table and not do it
again.) If you know that you're radically changing the kind of data
in a table, manually running analyze is a good idea. If you know that
you're creating a lot of dead tuples, manually vacuuming is a good
idea. Especially during a big data load where these changes might be
taking place faster than autovac notices.
--
Bill Moran
Collaborative Fusion Inc.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: wmoran@collaborativefusion.com, stonec.register@sympatico.ca
Subject: Re: Performace Optimization for Dummies
In-Reply-To: <20060929085802.05241905.wmoran@collaborativefusion.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox