From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: vacuum analyze slows sql query
Date: Wed, 3 Nov 2004 13:45:47 -0500
Message-ID: <20041103184547.GA16528@phlogiston.dyndns.org> (raw)
In-Reply-To: <20041103183134.78048.qmail@web52105.mail.yahoo.com>
References: <20041103183134.78048.qmail@web52105.mail.yahoo.com>
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
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: ajs@crankycanuck.ca
Subject: Re: vacuum analyze slows sql query
In-Reply-To: <20041103184547.GA16528@phlogiston.dyndns.org>
* 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