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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: vacuum analyze slows sql query

On Wed, Nov 03, 2004 at 10:31:33AM -0800, patrick ~ wrote:

> 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.

Then I suspect you have two problems, not one. The locking in 7.1
will certainly cause the timeouts during vacuum, but won't explain
the other items. Someone else suggested REINDEX, also, which is
likely needed. But again, you'll get more useful remarks from the
-performance list.

> 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.

You can learn a bit about this in the _current_ version of the docs
under regular maintenance.

> 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.

That's even worse. I certainly can't explain your results, then.
You really want to move to the -performance list for that.

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

Foreign keys are one well-known area of locking surprises.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org