X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D2D6A47633A for ; Fri, 4 Apr 2003 10:19:42 -0500 (EST) Received: from mail.reviewer.co.uk (frodo.reviewer.co.uk [213.232.121.13]) by postgresql.org (Postfix) with SMTP id A0819476334 for ; Fri, 4 Apr 2003 10:19:41 -0500 (EST) Received: (qmail 11466 invoked from network); 4 Apr 2003 15:19:39 -0000 Received: from unknown (HELO LAIKA) (62.49.176.243) by mail.reviewer.co.uk with SMTP; 4 Apr 2003 15:19:39 -0000 From: "Robert John Shepherd" To: Subject: Re: [BUGS] Rapid deteriation of performance (might be caused by tsearch?) in 7.3.2 Date: Fri, 4 Apr 2003 16:19:34 +0100 Message-ID: <001e01c2fabd$9edfb860$f3b0313e@LAIKA> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 In-Reply-To: <20030404064515.Q92769-100000@megazone23.bigpanda.com> Importance: Normal X-Spam-Status: No, hits=-10.1 required=5.0 tests=BAYES_20,IN_REP_TO,QUOTED_EMAIL_TEXT,QUOTE_TWICE_1 autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/55 X-Sequence-Number: 1561 > > Up until a few days ago I have been running Postgresl 7.2.3 with Tsearch > > from the contrib dir, but at various times the performance of the > > database would suddenly and rapidly deteriate so that queries which > > previously took 500ms then took 8 or 9 seconds. > Hmm, what are the before and after explain analyze results? Also, what > are your conf settings for shared buffers, sort memory and the fsm > parameters? shared_buffers = 40960 sort_mem = 20480 #max_fsm_relations = 1000 #max_fsm_pages = 10000 As you can see I've not uncommented or touched the fsm parameters, I have no idea what they do. Optimisation wise I have only played with shared_buffers, sort_mem and max_connections. > > The only cure is a backup and restore of the database > Did you do a regular vacuum or vacuum full? If only the former, it's > possible that you need to either vacuum more frequently and/or raise the > free space map settings in your configuration file. I've been running this daily: vacuumdb -h localhost -a -z Should I be using the full switch then? I'll get back to you on the other questions if you think they are still needed. > pgsql-performance is a better list, so I've replied to there. You'll > probably need to join in order to reply to list. Thanks, especially for not shouting at me heh, this is stressful enough as it is. Yours Unwhettedly, Robert John Shepherd. Editor DVD REVIEWER The UK's BIGGEST Online DVD Magazine http://www.dvd.reviewer.co.uk For a copy of my Public PGP key, email: pgp@robertsworld.org.uk