From: patrick ~ <sidsrr@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: vacuum analyze slows sql query
Date: Wed, 3 Nov 2004 10:31:33 -0800 (PST)
Message-ID: <20041103183134.78048.qmail@web52105.mail.yahoo.com> (raw)
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
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-sql@postgresql.org
Cc: sidsrr@yahoo.com
Subject: Re: vacuum analyze slows sql query
In-Reply-To: <20041103183134.78048.qmail@web52105.mail.yahoo.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