From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
Cc: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, Amit Kapila <amit(dot)kapila(at)huawei(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Performance bug in prepared statement binding in 9.2? |
Date: | 2013-09-10 01:04:20 |
Message-ID: | 20130910010420.GA32160@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Mon, Sep 9, 2013 at 08:38:09PM -0400, Andrew Dunstan wrote:
>
> On 08/01/2013 03:20 PM, Jeff Janes wrote:
> >On Thu, Aug 1, 2013 at 10:58 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> >>Amit, All:
> >>
> >>So we just retested this on 9.3b2. The performance is the same as 9.1
> >>and 9.2; that is, progressively worse as the test cycles go on, and
> >>unacceptably slow compared to 8.4.
> >>
> >>Some issue introduced in 9.1 is causing BINDs to get progressively
> >>slower as the PARSEs BINDs get run repeatedly. Per earlier on this
> >>thread, that can bloat to 200X time required for a BIND, and it's
> >>definitely PostgreSQL-side.
> >>
> >>I'm trying to produce a test case which doesn't involve the user's
> >>application. However, hints on other things to analyze would be keen.
> >Does it seem to be all CPU time (it is hard to imagine what else it
> >would be, but...)
> >
> >Could you use oprofile or perf or gprof to get a profile of the
> >backend during a run? That should quickly narrow it down to which C
> >function has the problem.
> >
> >Did you test 9.0 as well?
>
>
> This has been tested back to 9.0. What we have found is that the
> problem disappears if the database has come in via dump/restore, but
> is present if it is the result of pg_upgrade. There are some
> long-running transactions also running alongside this - we are
> currently planning a test where those are not present. We're also
> looking at constructing a self-contained test case.
>
> Here is some perf output from the bad case:
>
> + 14.67% postgres [.] heap_hot_search_buffer
OK, certainly looks like a HOT chain issue. I think there are two
possibilities:
1) the heap or index file is different from a dump/restore vs.
pg_upgrade
2) some other files is missing or changed between the two
My guess is that the dump/restore removes all the HOT chains as it just
dumps the most recent value of the chain. Could it be HOT chain
overhead that you are seeing, rather than a pg_upgrade issue/bug?
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
From | Date | Subject | |
---|---|---|---|
Next Message | Mel Llaguno | 2013-09-10 01:36:27 | Re: Performance bug in prepared statement binding in 9.2? |
Previous Message | Andrew Dunstan | 2013-09-10 00:38:09 | Re: Performance bug in prepared statement binding in 9.2? |