Re: vacuum analyze slows sql query

On Wed, Nov 03, 2004 at 07:01:00AM -0500, Andrew Sullivan wrote:
> On Tue, Nov 02, 2004 at 06:50:31PM -0800, patrick ~ wrote:
> > We have a nightly "garbage collection" process that runs and
> > purges any old data.  After this process a 'vacuum analyze'
> > is kicked off (regardless of whether or not any data was
> > actually purged).
> > 
> > At this point I should mention that our customer sites are
> > running PostgreSQL 7.1.3; however, I am able to reproduce
> > the issue on 7.4.2.
> 
> A 7.1 system takes an exclusive lock on any VACUUM.  It's the
> same as VACUUM FULL in 7.4.  Nothing you can do to make that
> not be sluggish.

Just to clarify, the sliggishness isn't only during the vacuum
period.  There are more more serious issues during the vacuum,
but i am not touching on those. The sluggishness is persistant
throughout daily operations.


> You want to get those sites off 7.1 anyway.  At the very least,
> you should be aware of xid exhaustion which can be prevented in
> 7.1 only with an initdb and complete restore.  Failure to
> accommodate that will mean that one day your databases will just
> disappear.

Yes, the plan is to upgrade them with new release of our product.
I didn't know about the xid exhaustion problem.  I'll need to
search the mailing list archives.


> Current VACUUM certainly does impose a serious I/O load; this is
> the reason for the vacuum setting tweaks in 8.0.  See the -hackers
> archives (from more than a year ago now) for (for instance) Jan
> Wieck's discussion of his feature and the subsequent debates.

I'll look into this.  I don't think we are ready to touch 8.x
yet.


> > I noticed that a freshly created db with freshly inserted data
> > (from a previous pg_dump) would result in quite fast results.
> > However, after running 'vacuum analyze' the very same query
> > slowed down about 1250x (Time: 1080688.921 ms vs Time: 864.522
> > ms).
> 
> My best guess is that there's something going on inside your
> function.  I'd be looking for locks here, though.  That makes no
> sense, given that you've only 78 rows being returned.  BTW, this
> topic should probably be better pursued on -performance.

Again to clarify, the output I pasted was from my standalone
PostgreSQL box.  That is, it wasn't being used other than those
quries being executed.

I don't know if you looked at my stored function, but there are
no locks in it (no explicit ones anyway).


Thanks for your reply,
--patrick

    
__________________________________ 
Do you Yahoo!? 
Check out the new Yahoo! Front Page. 
www.yahoo.com