Re: parse/bind/execute

Поиск
Список
Период
Сортировка
От Huang, Suya
Тема Re: parse/bind/execute
Дата
Msg-id D83E55F5F4D99B4A9B4C4E259E6227CD0149D1B5@AUX1EXC01.apac.experian.local
обсуждение исходный текст
Ответ на Re: parse/bind/execute  (David G Johnston <david.g.johnston@gmail.com>)
Ответы Re: parse/bind/execute  (David 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
oncethen 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
donot 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
caseswhere 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
moreefficient your use of database resources.  Same goes for the prepare ratio.  The clients you use and the general
usageof 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


В списке pgsql-performance по дате отправления:

Предыдущее
От: David G Johnston
Дата:
Сообщение: Re: parse/bind/execute
Следующее
От: David Johnston
Дата:
Сообщение: Re: parse/bind/execute