Postgres Pro
Downloads
Home   >   mailing lists

Thread: What happens at BIND time?

What happens at BIND time?

From
Josh Berkus
Date:
All,

Per -performance, we're trying to isolate and reproduce a customer issue
where on 9.2 BINDs get progressively slower (as much as 2000% slower)
over the course of a long test run, in a way they *don't* in 8.4.

So I'm looking for a cause for this.  One thing would help is a clearer
understanding of what happens at BIND time for 3-phase queries.

Note that, in this workload, the user is re-parsing the queries
over-and-over rather than using prepared plans properly.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Re: What happens at BIND time?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> So I'm looking for a cause for this.  One thing would help is a clearer
> understanding of what happens at BIND time for 3-phase queries.

Planning.

> Note that, in this workload, the user is re-parsing the queries
> over-and-over rather than using prepared plans properly.

Does the backend's memory usage climb, or hold steady?  If the former,
I'd bet on client failure to release resources, eg not closing the
portals when done with them.  A memory map from MemoryContextStats
would help determine exactly what's leaking.
        regards, tom lane

Re: What happens at BIND time?

From
Josh Berkus
Date:
Tom,

> Does the backend's memory usage climb, or hold steady?  If the former,
> I'd bet on client failure to release resources, eg not closing the
> portals when done with them.  A memory map from MemoryContextStats
> would help determine exactly what's leaking.

FS cache usage increases through the test run, as you'd expect, but the
amount of pinned memory actually remains pretty much constant -- and has
the same usage in both 8.4 (where the BIND issue doesn't happen) and
9.3b2 (where it does).

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Re: What happens at BIND time? (pg_upgrade issue)

From
Josh Berkus
Date:
On 08/28/2013 11:44 AM, Josh Berkus wrote:
> Tom,
> 
>> Does the backend's memory usage climb, or hold steady?  If the former,
>> I'd bet on client failure to release resources, eg not closing the
>> portals when done with them.  A memory map from MemoryContextStats
>> would help determine exactly what's leaking.
> 
> FS cache usage increases through the test run, as you'd expect, but the
> amount of pinned memory actually remains pretty much constant -- and has
> the same usage in both 8.4 (where the BIND issue doesn't happen) and
> 9.3b2 (where it does).

So, this just got a lot stranger.

What we've been testing here is upgrading from 8.4 to 9.X via
pg_upgrade, because that's what they have to do in production for time
reasons.  We recently confirmed that this issue affects 9.0 also.

So I finally got results back from the test where we do dump/restore (to
9.3b2) instead of pg_upgrade ... and the BIND regression does not occur.So this now has something to do with
pg_upgrade,not just BIND and Java.
 

cc'd Bruce for that reason.

We'll be rerunning these tests next week, comparing a 9.3 with the issue
to a 9.3 without it under oprofile etc.  Suggestions on what we should
look for are welcome.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com