X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 29FE79DCAF0 for ; Mon, 5 Dec 2005 16:31:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40561-09 for ; Mon, 5 Dec 2005 16:31:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 816359DCAAC for ; Mon, 5 Dec 2005 16:31:41 -0400 (AST) Received: (qmail 20429 invoked by uid 500); 5 Dec 2005 20:36:04 -0000 Date: Mon, 5 Dec 2005 14:36:04 -0600 From: Bruno Wolff III To: Assaf Yaari Cc: pgsql-performance@postgresql.org Subject: Re: Performance degradation after successive UPDATE's Message-ID: <20051205203604.GA19356@wolff.to> Mail-Followup-To: Bruno Wolff III , Assaf Yaari , pgsql-performance@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.035 required=5 tests=[AWL=0.035] X-Spam-Score: 0.035 X-Spam-Level: X-Archive-Number: 200512/133 X-Sequence-Number: 15954 On Mon, Dec 05, 2005 at 19:05:01 +0200, Assaf Yaari wrote: > Hi, > > I'm using PostgreSQL 8.0.3 on Linux RedHat WS 3.0. > > My application updates counters in DB. I left a test over the night that > increased counter of specific record. After night running (several > hundreds of thousands updates), I found out that the time spent on > UPDATE increased to be more than 1.5 second (at the beginning it was > less than 10ms)! Issuing VACUUM ANALYZE and even reboot didn't seemed to > solve the problem. You need to be running vacuum more often to get rid of the deleted rows (update is essentially insert + delete). Once you get too many, plain vacuum won't be able to clean them up without raising the value you use for FSM. By now the table is really bloated and you probably want to use vacuum full on it.