X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E8E45475E8C for ; Mon, 24 Feb 2003 14:14:06 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 42106-02 for ; Mon, 24 Feb 2003 14:13:56 -0500 (EST) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by postgresql.org (Postfix) with ESMTP id DFD50475EE4 for ; Mon, 24 Feb 2003 13:51:25 -0500 (EST) Received: from andrew by mail.libertyrms.com with local (Exim 3.22 #3 (Debian)) id 18nNhF-0004xt-00 for ; Mon, 24 Feb 2003 13:51:25 -0500 Date: Mon, 24 Feb 2003 13:51:25 -0500 From: Andrew Sullivan To: "pgsql-performance@postgresql.org" Subject: Re: slow query Message-ID: <20030224135125.H2742@mail.libertyrms.com> Mail-Followup-To: Andrew Sullivan , "pgsql-performance@postgresql.org" References: <1046105913.1014.66.camel@camel> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: ; from clarence@silcom.com on Mon, Feb 24, 2003 at 09:27:56AM -0800 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/183 X-Sequence-Number: 1239 On Mon, Feb 24, 2003 at 09:27:56AM -0800, Clarence Gardner wrote: > > I don't know about the settings you mention, but a frequent vacuum > does not at all obviate a vacuum full. My database is vacuumed every > night, but a while ago I found that a vacuum full changed a simple > single-table query from well over 30 seconds to one or two. We now > do a vacuum full every night. This probably means that either some of your FSM settings should be different, or that you have long-running queries, or both. _Some_ advantage to vacuum full is expected, but 30 seconds to one or two is a pretty big deal. Except in cases where a large percentage of the table is a vacuum candidate, the standard vacuum should be more than adequate. But there are a couple of gotchas. You need to have room in your free space map to hold information about the bulk of the to-be-freed tables. So perhaps your FSM settings are not big enough, even though you tried to set them higher. (Of course, if you're replacing, say, more than half the table, setting the FSM high enough isn't practical.) Another possibility is that you have multiple long-running transactions that are keeping non-blocking vacuum from being very effective. Since those transactions are possibly referencing old versions of a row, when the non-blocking vacuum comes around, it just skips the "dead" tuples which are nevertheless alive to someone. (You can see the effect of this by using the contrib/pgstattuple function). Blocking vacuum doesn't have this problem, because it just waits on the table until everything ahead of it has committed or rolled back. So you pay in wait time for all transactions during the vacuum. (I have encountered this very problem on a table which gets a lot of update activity on just on just one row. Even vacuuming every minute, the table grew and grew, because of another misbehaving application which was keeping a transaction open when it shouldn't have.) A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada M2P 2A8 +1 416 646 3304 x110