Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 4E9679FA26D for ; Thu, 18 Jan 2007 22:09:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 12607-07 for ; Thu, 18 Jan 2007 22:09:15 -0400 (AST) X-Greylist: delayed 00:31:41.907875 by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 8846C9FB39A for ; Thu, 18 Jan 2007 22:09:20 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from www.roaringpenguin.com (www.roaringpenguin.com [206.191.13.82]) by svr4.postgresql.org (Postfix) with ESMTP id C1F585AF87E for ; Fri, 19 Jan 2007 01:37:37 +0000 (GMT) Received: from ottawa-hs-209-217-122-117.s-ip.magma.ca (ottawa-hs-209-217-122-117.s-ip.magma.ca [209.217.122.117]) by www.roaringpenguin.com (8.13.8/8.13.7) with ESMTP id l0J1bZ21003021 for ; Thu, 18 Jan 2007 20:37:35 -0500 Received: from [192.168.2.3] (shishi.roaringpenguin.com [192.168.2.3]) by shevy.roaringpenguin.com (8.12.10/8.12.10) with ESMTP id l0J1bXPr022366 for ; Thu, 18 Jan 2007 20:37:34 -0500 Message-ID: <45B020DD.2030603@roaringpenguin.com> Date: Thu, 18 Jan 2007 20:37:33 -0500 From: "David F. Skoll" User-Agent: Thunderbird 1.5.0.7 (X11/20060909) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Strange performance hit upgrading from 8.0.9 to 8.2.1 X-Enigmail-Version: 0.94.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-CanItPRO-Stream: outgoing (inherits from default) X-Scanned-By: CanIt (www . roaringpenguin . com) on 206.191.13.82 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.143 tagged_above=0 required=5 tests=BAYES_50, SPF_FAIL X-Spam-Level: * X-Archive-Number: 200701/167 X-Sequence-Number: 24323 Hello, We have a customer running a rather large installation. There are about 15 machines talking to a PostgreSQL database server. At any given time, each machine has between around 20 to 120 connections to the PG server, and the aggregate query rate probably hovers at over 1000 per second. Our database has a table that looks like this: database=> \d low_contention_stats Table "public.low_contention_stats" Column | Type | Modifiers -----------+---------+----------------------------- date | date | default ('now'::text)::date key | text | increment | integer | default 1 There are no indexes on that table. Anywhere from 40 to 100 times per second, we insert something like this into the table: INSERT INTO low_contention_stats(key, increment) VALUES('ACCEPTED', 1); The INSERTs run outside of any transaction (ie, in their own self-contained transaction.) The "key" data being inserted is short -- under 25 characters. We periodically do something like this: BEGIN; -- Aggregate the low_contention data into another table -- and then... DELETE FROM low_contention_stats; COMMIT; and it used to work fine. However, on the upgrade from 8.0.9 to 8.2.1, we suddenly started seeing terrible performance on the INSERTs. (The aggregation process was disabled -- we were only doing the INSERTs at the time.) What would happen is that hundreds of back-ends would acquire locks on the table and it would take many seconds for all the INSERTs to finish. Things would settle down for a few seconds to a couple of minutes, and then again - WHAM! Huge contention. Disabling the INSERTs completely made everything happy, even though they formed only a small portion of the overall queries. While this is OK as a stopgap, it's not really a long-term solution because it disables a fairly useful feature of our software. Unfortunately, I have no idea how to duplicate the problem in our lab with the hardware we have access to. :-( Any PostgreSQL gurus have any insights? Regards, David.