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

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