X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 72E5B475C60 for ; Mon, 24 Feb 2003 13:57:16 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 35938-08 for ; Mon, 24 Feb 2003 13:57:05 -0500 (EST) Received: from localhost.localdomain (unknown [65.217.53.66]) by postgresql.org (Postfix) with ESMTP id 2CA8A475E30 for ; Mon, 24 Feb 2003 13:30:42 -0500 (EST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.5/8.12.5) with ESMTP id h1OIwi6P022019; Mon, 24 Feb 2003 13:58:46 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h1OIUYp27505; Mon, 24 Feb 2003 13:30:36 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id F3HTHDGG; Mon, 24 Feb 2003 13:30:33 -0500 Subject: Re: slow query From: Robert Treat To: Clarence Gardner Cc: Oleg Lebedev , Josh Berkus , "pgsql-performance@postgresql.org" In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 24 Feb 2003 13:30:34 -0500 Message-Id: <1046111434.1014.165.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/180 X-Sequence-Number: 1236 On Mon, 2003-02-24 at 12:27, Clarence Gardner wrote: > On 24 Feb 2003, Robert Treat wrote: > > > > > If your seeing wildly dramatic improvments from vacuum full, you might > > want to look into running regular vacuums more often (especially for > > high turnover tables), increase your max_fsm_relations to 1000, and > > increasing your max_fsm_pages. > > 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. > Actually if you are vacuuming frequently enough, it can (and should*) obviate a vacuum full. Be aware that frequently enough might mean really frequent, for instance I have several tables in my database that update every row within a 15 minute timeframe, so I run a "lazy" vacuum on these tables every 10 minutes. This allows postgresql to reuse the space for these tables almost continuously so I never have to vacuum full them. (* this assumes your max_fsm_pages/relations settings are set correctly) Robert Treat