Skip site navigation (1) Skip section navigation (2)

Re: [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
Date: 2008-03-28 05:51:54
Message-ID: 200803280151.54874.xzilla@users.sourceforge.net (view raw or flat)
Thread:
Lists: pgsql-committerspgsql-hackerspgsql-performance
On Thursday 27 March 2008 17:11, Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2008-03-28 05:55:56
Subject: Re: [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
Previous:From: Tom LaneDate: 2008-03-27 21:11:37
Subject: Re: [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-03-28 05:55:56
Subject: Re: [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
Previous:From: Greg SmithDate: 2008-03-28 05:22:42
Subject: Re: pg_standby for 8.2 (with last restart point)

pgsql-committers by date

Next:From: Tom LaneDate: 2008-03-28 05:55:56
Subject: Re: [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
Previous:From: Bruce MomjianDate: 2008-03-28 03:29:49
Subject: pgsql: Add to TODO: > > o Add CASE capability to language (already in

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group