X-Original-To: pgsql-bugs@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id ECC59475458 for ; Fri, 4 Apr 2003 05:49:25 -0500 (EST) Received: from mail.reviewer.co.uk (frodo.reviewer.co.uk [213.232.121.13]) by postgresql.org (Postfix) with SMTP id B8B1F474E42 for ; Fri, 4 Apr 2003 05:49:24 -0500 (EST) Received: (qmail 8315 invoked from network); 4 Apr 2003 10:49:25 -0000 Received: from unknown (HELO LAIKA) (62.49.176.243) by mail.reviewer.co.uk with SMTP; 4 Apr 2003 10:49:25 -0000 From: "Robert John Shepherd" To: Subject: Rapid deteriation of performance (might be caused by tsearch?) in 7.3.2 Date: Fri, 4 Apr 2003 11:49:25 +0100 Message-ID: <000201c2fa97$dc2197f0$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 Importance: Normal X-Spam-Status: No, hits=-1.6 required=5.0 tests=BAYES_30 version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/25 X-Sequence-Number: 5798 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. The only cure is a backup and restore of the database, vacuuming and analysing does nothing. I even tried rebuilding all indexes once which didn't seem to help. This was an annoying but intermittent thing, which happened the last time this Wednesday. Since I was doing a backup and restore anyway, I decided to upgrade to 7.3.2 in the hope this might fix the annoying problem, however it has made it WAY worse. Rather than going a few weeks (and sometimes months) in between having to use this fix, I am now having to do it almost every single day. I'm now lucky if it lasts 24 hours before it brings my website to a total crawl. There is nothing special about my database other than the fact that I use the Tsearch addon. Now if I go and do a bit update to the Tsearch indexes on a table, with for example: UPDATE tblmessages SET strmessageidx=txt2txtidx(strheading || ' ' || strmessage); Then that instantly brings the whole database to a crawl, which no amount of index rebuilding, vacuuming and analysing helps. Help! (And sorry if this is the wrong list) 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