Date: Fri, 28 Mar 2008 01:51:54 -0400
From: Robert Treat <xzilla@users.sourceforge.net>
Subject: Re: [COMMITTERS] pgsql: Fix
	TransactionIdIsCurrentTransactionId() to use binary search
In-reply-to: <2092.1206652297@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Message-id: <200803280151.54874.xzilla@users.sourceforge.net>
MIME-version: 1.0
Content-type: text/plain; charset=iso-8859-15
Content-transfer-encoding: 7bit
Content-disposition: inline
References: <20080317021856.0D2D37558DC@cvs.postgresql.org>
	<200803271656.45574.xzilla@users.sourceforge.net>
	<2092.1206652297@sss.pgh.pa.us>
User-Agent: KMail/1.9.1

On Thursday 27 March 2008 17:11, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > On Sunday 16 March 2008 22:18, Tom Lane wrote:
> >> Fix TransactionIdIsCurrentTransactionId() to use binary search instead
> >> of linear search when checking child-transaction XIDs.
> >
> > Are there any plans to backpatch this into REL8_3_STABLE?
>
> No.
>
> > It looks like I am
> > hitting a pretty serious performance regression on 8.3 with a stored
> > procedure that grabs a pretty big recordset, and loops through doing
> > insert....update on unique failures.  The procedure get progressivly
> > slower the more records involved... and dbx shows me stuck in
> > TransactionIdIsCurrentTransactionId().
>
> If you can convince me it's a regression I might reconsider, but I
> rather doubt that 8.2 was better,
>

Well, I can't speak for 8.2, but I have a second system crunching the same 
data using the same function on 8.1 (on lesser hardware in fact), and it 
doesn't have these type of issues.  Looking over the past week, the 8.3 box 
averages about 19 minutes to complete each run, and the 8.1 box averages 15 
minutes (sample size is over 100 iterations of both).  Of course this is when 
it completes, the 8.3 box often does not complete, as it falls farther behind 
during the day and eventually cannot finish (it does periodic intra-day 
summing, so there's a limited time frame it has to run, and it's jobs end up 
taking hours to complete). 

I am open to the idea that there is some other issue going on here, but 
whenever I look at it, it seems stuck in 
TransactionIdIsCurrentTransactionId(), progress for the function does get 
increasingly slower as it progresses, and I can watch the process consuming 
more and more memory as it goes on...  I can probably get some dtrace output 
tommorrow if you want. 

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