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