X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 385445E37D0 for ; Thu, 26 Aug 2004 15:20:49 -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 62328-08 for ; Thu, 26 Aug 2004 18:20:45 +0000 (GMT) Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) by svr1.postgresql.org (Postfix) with ESMTP id CA8755E3632 for ; Thu, 26 Aug 2004 15:20:44 -0300 (ADT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 6183323; Thu, 26 Aug 2004 11:22:01 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Neil Cooper , "pgsql-performance@postgresql.org" Subject: Re: Disabling transaction/outdated-tuple behaviour Date: Thu, 26 Aug 2004 11:20:20 -0700 User-Agent: KMail/1.4.3 References: <1093543374.16123.38.camel@NCooper> In-Reply-To: <1093543374.16123.38.camel@NCooper> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200408261120.20890.josh@agliodbs.com> 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: 200408/368 X-Sequence-Number: 8004 Neil, > I am using a simple PostgreSQL 7.3 database in a soft-realtime > application. Then you're not going to like the answer I have for you, see below. > I have a problem where an update on a record within a (fully indexed) > table containing less than ten records needs to occur as fast as > possible. Have you considered dropping the indexes? On such a small table, they won't be used, and they are detracting significantly from your update speed. > Immediately after performing a vaccum, updates take upto 50 milliseconds > to occur, however the update performance degrades over time, such that > after a few hours of continuous updates, each update takes about half a > second. Regular vacuuming improves the performance temporarily, but > during the vacuum operation (which takes upto 2 minutes), performance of > concurrent updates falls below an acceptable level (sometimes > 2 > seconds per update). This is "normal" depending on your platform and concurrent activity. More frequent vacuums would take less time each. What is your max_fsm_pages set to? Increasing this may decrease the necessity of vacuums as well as speeding them up. Also, are you vacuuming the whole DB or just that table? 2 mintues seems like a long time; I can vacuum a 100GB database in less than 4. > Is there a way to disable this behaviour such that an update operation > would overwrite the current record and does not generate an outdated > tuple each time? (My application does not need transactional support). No. Our ACID Transaction compliance depends on "that behaviour" (MVCC). We don't offer PostgreSQL in a "non-ACID mode". If your application truly does not need transactional support, you may want to consider an embedded database instead, such as BerkeleyDB or SQLite. PostgreSQL has a *lot* of "baggage" associated with having 99.99% incorruptable transactions. Alternately, you may also want to take a look at TelegraphCG, a derivative of PostgreSQL designed to handle "streaming data". They may have already conquered some of your difficulties for you. http://telegraph.cs.berkeley.edu/ Were I you, I would start with tuning the database first through PostgreSQL.conf and a careful look at my hardware usage and DB maintenance. Then I would consider testing 8.0, which has some specific improvements designed to address some of the problems you are having. Particularly, Jan's Background Writer and Lazy Vacuum. -- Josh Berkus Aglio Database Solutions San Francisco