pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedparse/bind/execute
4+ messages / 3 participants
[nested] [flat]
* parse/bind/execute
@ 2014-06-05 01:32 Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 1 reply; 4+ messages in thread
From: Huang, Suya @ 2014-06-05 01:32 UTC (permalink / raw)
To: pgsql-performance
Hello,
I am using Pgbadger to analyze the postgresql database log recently and noticed a section "Prepared queries ratio". For my report, it has:
1.03 as Ratio of bind vs prepare
0.12% Ratio between prepared and "usual" statements
I'm trying to understand what the above metrics mean and if it's a problem. I found people can clearly clarify the parse/bind/execute time of a query. To my limited knowledge of Postgres, using explain analyze, I can only get the total execution time.
Can someone shed me some light on this subject? How to interpret the ratios?
Thanks,
Suya
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: parse/bind/execute
@ 2014-06-05 01:57 David G Johnston <david.g.johnston@gmail.com>
parent: Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 1 reply; 4+ messages in thread
From: David G Johnston @ 2014-06-05 01:57 UTC (permalink / raw)
To: pgsql-performance
Huang, Suya wrote
> Hello,
>
> I am using Pgbadger to analyze the postgresql database log recently and
> noticed a section "Prepared queries ratio". For my report, it has:
>
> 1.03 as Ratio of bind vs prepare
> 0.12% Ratio between prepared and "usual" statements
>
> I'm trying to understand what the above metrics mean and if it's a
> problem. I found people can clearly clarify the parse/bind/execute time of
> a query. To my limited knowledge of Postgres, using explain analyze, I can
> only get the total execution time.
>
> Can someone shed me some light on this subject? How to interpret the
> ratios?
>
> Thanks,
> Suya
Both are related to using prepared statements (usually with parameters).
Each bind is a use of an already prepared query with parameters filled in.
The prepare is the initial preparation of the query. A ratio of 1 means
that each time you prepare a query you use it once then throw it away.
Likewise a value of 2 would mean you are executing each prepared statement
twice.
"Usual" statements are those that are not prepared. The ratio is simply the
counts of each as seen by the database - I do not know specifics as to what
exactly is counted (ddl?).
That low a ratio means that almost all statements you send to the database
are non-prepared. In those relatively few cases where you do prepare first
you almost always immediately execute a single set of inputs then discard
the prepared statement.
I do not know enough about the underlying data to draw a conclusion but
typically the higher the bind/prepare ratio the more efficient your use of
database resources. Same goes for the prepare ratio. The clients you use
and the general usage of the database heavily influence what would be
considered reasonable ratios.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/parse-bind-execute-tp5806132p5806133.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: parse/bind/execute
@ 2014-06-05 02:20 Huang, Suya <Suya.Huang@au.experian.com>
parent: David G Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Huang, Suya @ 2014-06-05 02:20 UTC (permalink / raw)
To: David G Johnston <david.g.johnston@gmail.com>; pgsql-performance
Thank you David, I copied the detailed activity from the report as below. As it shows, it has prepare and bind queries. One of the item has Bind/Prepare pretty high as 439.50. so that looks like a good value?
Another question is if bind only happens in a prepared statement?
Day Hour Prepare Bind Bind/Prepare Percentage of prepare
Jun 03 00 205 209 1.02 1.27%
01 19 19 1.00 0.17%
02 0 0 0.00 0.00%
03 0 0 0.00 0.00%
04 6 6 1.00 0.00%
05 2 879 439.50 0.02%
06 839 1,323 1.58 7.01%
07 0 0 0.00 0.00%
Thanks,
Suya
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of David G Johnston
Sent: Thursday, June 05, 2014 11:58 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] parse/bind/execute
Huang, Suya wrote
> Hello,
>
> I am using Pgbadger to analyze the postgresql database log recently
> and noticed a section "Prepared queries ratio". For my report, it has:
>
> 1.03 as Ratio of bind vs prepare
> 0.12% Ratio between prepared and "usual" statements
>
> I'm trying to understand what the above metrics mean and if it's a
> problem. I found people can clearly clarify the parse/bind/execute
> time of a query. To my limited knowledge of Postgres, using explain
> analyze, I can only get the total execution time.
>
> Can someone shed me some light on this subject? How to interpret the
> ratios?
>
> Thanks,
> Suya
Both are related to using prepared statements (usually with parameters).
Each bind is a use of an already prepared query with parameters filled in.
The prepare is the initial preparation of the query. A ratio of 1 means that each time you prepare a query you use it once then throw it away.
Likewise a value of 2 would mean you are executing each prepared statement twice.
"Usual" statements are those that are not prepared. The ratio is simply the counts of each as seen by the database - I do not know specifics as to what exactly is counted (ddl?).
That low a ratio means that almost all statements you send to the database are non-prepared. In those relatively few cases where you do prepare first you almost always immediately execute a single set of inputs then discard the prepared statement.
I do not know enough about the underlying data to draw a conclusion but typically the higher the bind/prepare ratio the more efficient your use of database resources. Same goes for the prepare ratio. The clients you use and the general usage of the database heavily influence what would be considered reasonable ratios.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/parse-bind-execute-tp5806132p5806133.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: parse/bind/execute
@ 2014-06-05 02:34 David Johnston <david.g.johnston@gmail.com>
parent: Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Johnston @ 2014-06-05 02:34 UTC (permalink / raw)
To: Huang, Suya <Suya.Huang@au.experian.com>; +Cc: pgsql-performance
Please do not top-posts on these lists.
On Wednesday, June 4, 2014, Huang, Suya <Suya.Huang@au.experian.com> wrote:
> Thank you David, I copied the detailed activity from the report as below.
> As it shows, it has prepare and bind queries. One of the item has
> Bind/Prepare pretty high as 439.50. so that looks like a good value?
>
> Another question is if bind only happens in a prepared statement?
>
> Day Hour Prepare Bind Bind/Prepare Percentage of
> prepare
> Jun 03 00 205 209 1.02 1.27%
> 01 19 19 1.00 0.17%
> 02 0 0 0.00 0.00%
> 03 0 0 0.00 0.00%
> 04 6 6 1.00 0.00%
> 05 2 879 439.50 0.02%
> 06 839 1,323 1.58 7.01%
> 07 0 0 0.00 0.00%
>
>
>
Yes. Something that high usual involves batch inserting into a table. To
be honest, a global picture is of limited value for this very reason.
Representing all of your usage as a single number is problematic.
Breaking it down by hour as done here increases the likelihood of seeing
something useful but typically that would be by chance. In this case
because batch processing is done in the early morning and few users are
probably on the system (a common source of one-off statements) the numbers
here are dominated by the special case of bulk inserts and are not typical
of normal activity and performance.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-06-05 02:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-06-05 01:32 parse/bind/execute Huang, Suya <Suya.Huang@au.experian.com>
2014-06-05 01:57 ` David G Johnston <david.g.johnston@gmail.com>
2014-06-05 02:20 ` Huang, Suya <Suya.Huang@au.experian.com>
2014-06-05 02:34 ` David Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox