X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6E1C2D1B1DA for ; Fri, 2 Jul 2004 10:06:20 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20939-06 for ; Fri, 2 Jul 2004 13:06:18 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 05EE8D1B18E for ; Fri, 2 Jul 2004 10:06:14 -0300 (ADT) Received: (qmail 25624 invoked by uid 500); 2 Jul 2004 13:12:58 -0000 Date: Fri, 2 Jul 2004 08:12:58 -0500 From: Bruno Wolff III To: "Gosen, Hitoshi" Cc: pgsql-bugs@postgresql.org, pgsql-performance@postgresql.org Subject: Re: BUG #1186: Broken Index? Message-ID: <20040702131258.GB25007@wolff.to> Mail-Followup-To: pgsql-performance@postgresql.org References: <20040702075007.B448FCF4D52@www.postgresql.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20040702075007.B448FCF4D52@www.postgresql.com> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/27 X-Sequence-Number: 8609 On Fri, Jul 02, 2004 at 04:50:07 -0300, PostgreSQL Bugs List wrote: > > The following bug has been logged online: This doesn't appear to be a bug at this point. It sounds like you have a self induced performance problem, so I am moving the discussion to pgsql-performance. > > Bug reference: 1186 > Logged by: Gosen, Hitoshi > > Email address: mic-gosen@ns.inter-mic.co.jp > > PostgreSQL version: 7.4 > > Operating system: linux 2.4.18 > > Description: Broken Index? > > Details: > > Hello All, > We are using PostgreSQL 7.4.2 for our website that handles over 200,000 > transactions a day. > About a month ago, the responses from the SELECT queries on the database > became terribly slow. > We tried to anaylze the cause of the problem, searching throught the system > logs and all, but nothing appeared to be out of the ordinary. > > What we did to resolve this was to dump the database, delete the database, > recreate the database, and finally restore it. After that, things were back > to normal. > > From the above experience, we were able to hypothesize that the fault of the > slow responses was not from a broken data or hardware failures, but from a > broken index, since we were able to recover 100% of the data on the same > machine. > > Today, the same problem occured, and the same actions are going to be taken > to temporary resolve it. > > Final note: we will also experiment with the 'vacuum full' command to see > if it counters this problem. It sounds like you aren't properly vacuuming your database. It is possible that you need a higher FSM setting or to vacuum more frequently.