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

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Re: [HACKERS] [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
Date: 2008-04-25 21:24:47
Message-ID: 200804251724.48705.xzilla@users.sourceforge.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers pgsql-hackers pgsql-performance

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

In response to

Responses

Browse pgsql-committers by date

  From Date Subject
Next Message Tom Lane 2008-04-25 21:32:59 Re: Re: [HACKERS] [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
Previous Message Tom Lane 2008-04-25 21:21:25 Re: Re: [COMMITTERS] pgsql: Update: < * Allow adding enumerated values to an existing

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-04-25 21:32:59 Re: Re: [HACKERS] [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
Previous Message Tom Lane 2008-04-25 21:21:25 Re: Re: [COMMITTERS] pgsql: Update: < * Allow adding enumerated values to an existing

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2008-04-25 21:32:59 Re: Re: [HACKERS] [COMMITTERS] pgsql: Fix TransactionIdIsCurrentTransactionId() to use binary search
Previous Message Andrew Sullivan 2008-04-25 15:20:53 Re: Sun Talks about MySQL