Thread: Re: [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
Re: [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
From
Robert Treat
Date:
On Sunday 16 March 2008 22:18, Tom Lane wrote: > Log Message: > ----------- > Fix TransactionIdIsCurrentTransactionId() to use binary search instead of > linear search when checking child-transaction XIDs. This makes for an > important speedup in transactions that have large numbers of children, > as in a recent example from Craig Ringer. We can also get rid of an > ugly kluge that represented lists of TransactionIds as lists of OIDs. > Are there any plans to backpatch this into REL8_3_STABLE? 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(). I can provide provide more details if needed (lmk what your looking for) but it certainly looks like the issue discussed here: http://archives.postgresql.org/pgsql-performance/2008-03/msg00191.php -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Re: [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
From
Tom Lane
Date:
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, regards, tom lane
Re: [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
From
Robert Treat
Date:
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
Re: [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
From
Tom Lane
Date:
Robert Treat <xzilla@users.sourceforge.net> writes: >> 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. If you can condense it to a test case that is worse on 8.3 than 8.1, I'm willing to listen... regards, tom lane