From: Robert John Shepherd <robert@reviewer.co.uk>
To: pgsql-bugs@postgresql.org
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> (raw)
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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: robert@reviewer.co.uk, pgsql-bugs@postgresql.org
Subject: Re: Rapid deteriation of performance (might be caused by tsearch?) in 7.3.2
In-Reply-To: <000201c2fa97$dc2197f0$f3b0313e@LAIKA>
References: <000201c2fa97$dc2197f0$f3b0313e@LAIKA>
* 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