Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 893FE2E005D
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Fri, 25 Apr 2008 18:25:26 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 11934-06
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Fri, 25 Apr 2008 18:25:23 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from vms173001pub.verizon.net (vms173001pub.verizon.net
	[206.46.173.1])
	by developer.postgresql.org (Postfix) with ESMTP id 793902E0033
	for <pgsql-performance@postgresql.org>;
	Fri, 25 Apr 2008 18:25:23 -0300 (ADT)
Received: from new-host.home ([72.81.136.83]) by vms173001.mailsrvcs.net
	(Sun Java System Messaging Server 6.2-6.01 (built Apr  3 2006))
	with ESMTPA id <0JZW00DDHGU4XCB0@vms173001.mailsrvcs.net> for
	pgsql-performance@postgresql.org; Fri, 25 Apr 2008 16:25:16 -0500 (CDT)
Date: Fri, 25 Apr 2008 17:24:47 -0400
From: Robert Treat <xzilla@users.sourceforge.net>
Subject: Re: Re: [HACKERS] [COMMITTERS] pgsql: Fix
	TransactionIdIsCurrentTransactionId() to use binary search
In-reply-to: <20080421165424.GC14464@alvh.no-ip.org>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: pgsql-performance@postgresql.org
Message-id: <200804251724.48705.xzilla@users.sourceforge.net>
MIME-version: 1.0
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7bit
Content-disposition: inline
References: <20080317021856.0D2D37558DC@cvs.postgresql.org>
	<200804211229.00871.xzilla@users.sourceforge.net>
	<20080421165424.GC14464@alvh.no-ip.org>
User-Agent: KMail/1.9.1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200804/367
X-Sequence-Number: 29860

On Monday 21 April 2008 12:54, Alvaro Herrera wrote:
> Robert Treat wrote:
> > Unfortunatly I don't have the 8.1 system to bang on anymore for this,
> > (though anecdotaly speaking, I never saw this behavior in 8.1) however I
> > do now have a parallel 8.3 system crunching the data, and it is showing
> > the same symptom (yes, 2 8.3 servers, crunching the same data, both
> > bogged down now), so I do feel this is something specific to 8.3.
> >
> > I am mostly wondering if anyone else has encountered behavior like this
> > on 8.3 (large sets of insert....update exception block in plpgsql bogging
> > down), or if anyone has any thoughts on which direction I should poke at
> > it from here. TIA.
>
> Perhaps what you could do is backpatch the change and see if the problem
> goes away.

So, after some more digging, we ended up backpatching the change. Results as 
follows:

= hanging job before patch

     elapsed     |                         status
-----------------+--------------------------------------------------------
 00:00:00.024075 | OK/starting with 2008-04-25 08:20:02
 00:00:00.611411 | OK/processing 624529 hits up until 2008-04-25 10:20:02
 03:48:02.748319 | ??/Processed 65000 aggregated rows so far
(3 rows)

= successful job after patch

     elapsed     |                         status
-----------------+---------------------------------------------------------
 00:00:00.026809 | OK/starting with 2008-04-25 08:20:02
 00:00:03.921532 | OK/processing 2150115 hits up until 2008-04-25 15:00:02
 00:24:45.439081 | OK/Processed 334139 aggregated rows
 00:00:00.019433 | OK/
(4 rows)

Note the second run had to do all the rows from the first run, plus additional 
rows that accumulated while the first job was running. 

Oddly some dtrace profiling gave me this, which is pretty different, but 
certainly doesn't have concerns about TransactionIdIsCurrentTransactionId

<snip>
postgres`hash_search_with_hash_value                      536   2.3%
postgres`SearchCatCache                                   538   2.3%
postgres`hash_seq_search                                  577   2.4%
postgres`MemoryContextAllocZeroAligned                    610   2.6%
postgres`_bt_compare                                      671   2.8%
libc.so.1`memcpy                                          671   2.8%
postgres`XLogInsert                                       755   3.2%
postgres`LockReassignCurrentOwner                         757   3.2%
postgres`base_yyparse                                    1174   5.0%
postgres`AllocSetAlloc                                   1244   5.3%

We still have one of our 8.3 servers running stock 8.3.1, so we'll see how 
long before this bites us again.  Would certainly be nice to get this fixed 
in the mainline code. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL