Date: Mon, 9 Sep 2013 21:04:20 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Jeff Janes <jeff.janes@gmail.com>, Josh Berkus <josh@agliodbs.com>,
 Amit Kapila <amit.kapila@huawei.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Performance bug in prepared statement binding in 9.2?
Message-ID: <20130910010420.GA32160@momjian.us>
References: <51A699E6.3000008@agliodbs.com>
 <WM!f3d412a8ff9fb2a52f4cfd0f48c225b6122ba5bf1e1523173b5dbc63db143b9cab09ac3f5518a12a19d2507538472d98!@asav-1.01.com>
 <51A79512.4020200@agliodbs.com>
 <WM!c84cb8479a8b2ec21d1061c951095da00bc52608e9822124f1438b9de432ec7ebedaf25085801b67d581eed6d368c344!@asav-2.01.com>
 <51FAA1B8.6040809@agliodbs.com>
 <CAMkU=1wn3npjTjEkC8rYMgmoKXYoAgfxosT_Q3v_=wTfriPKdA@mail.gmail.com>
 <522E69F1.8070203@dunslane.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <522E69F1.8070203@dunslane.net>
User-Agent: Mutt/1.5.20 (2009-06-14)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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@agliodbs.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance