From: Amit Kapila <amit.kapila@huawei.com>
To: "'Josh Berkus'" <josh@agliodbs.com>, <pgsql-performance@postgresql.org>
References: <51A699E6.3000008@agliodbs.com>
In-Reply-To: <51A699E6.3000008@agliodbs.com>
Subject: Re: Performance bug in prepared statement binding in 9.2?
Date: Thu, 30 May 2013 19:25:57 +0530
Message-ID: <00b301ce5d3d$697449a0$3c5cdce0$@kapila@huawei.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: Ac5cysDpv1AyH2FcS7uAjeB8ieb6IAAcN02Q
Content-Language: en-us
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Thursday, May 30, 2013 5:45 AM Josh Berkus wrote:
> Folks,
>=20
> I'm seeing what may be a major performance bug in BIND in 9.2.4.
>=20
> We have a client who has an application which uses
> Tomcat+Hibernate+JDBC.  They are in the process of upgrading this
> application from 8.4.17 to 9.2.4.  As part of this, they have been
> doing
> performance testing, and 9.2 is coming out MUCH worse than 8.4.  The
> problem appears to be bind/plan time.
>=20
> Their application does not use prepared queries usefully, doing
> parse,bind,execute on every query cycle.
>=20
> Here's timings overall for 29 test cycles (cycle 1 has been omitted).
> As you can see, parse+execute times are pretty much constant, as are
> application think times, but bind times vary quite a lot.  In 8.4, the
> 29 cycles are constantly 4.5min to 5.75min long.  In 9.2, which is the
> chart below, they are all over the place.

I think it might be because of choosing custom plan option due to which it =
might be generating new plan during exec_bind_message().
exec_bind_message()->GetCachedPlan()->choose_custom_plan(). If it chooses c=
ustom plan, then it will regenerate the plan which can cause extra cost
observed in test.
Though there is calculation that it should not choose custom plan always, b=
ut still I guess the variation observed in the test can be due to this reas=
on.

To test if this is the cause, we might hack the code such that it always ch=
ooses generic plan, so that it doesn't need to generate plan again.

With Regards,
Amit Kapila.


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