Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

[samthakur74]

>This patch needs documentation. At a minimum, the new calls_underest
>field needs to be listed in the description of the pg_stat_statements.
I have attached a version which includes documentation.
pg_stat_statements-identification-v4.patch.gz

  
>Pardon for not following the discussion: What exactly does the
>calls_underest field mean? I couldn't decipher it from the patch. What
>can an admin do with the value? How does it compare with just bumping up
>pg_stat_statements.max? 
Paraphrasing the documentation. 
There is a possibility that,for queries which are tracked intermittently,
could have their statistics silently reset.
The calls_underest field gives an indication that a query has been tracked
intermittently and consequently 
 have a higher probability of erroneous statistics. Queries tracked
constantly will have a zero value for 
calls_underest indicating zero probability of erroneous statistics.
A greater value of calls_underest indicates greater degree of inconsistent
tracking which in
turn means greater possibility of erroneous statistics due to statistics
being reset when 
query was not being tracked.

Increasing pg_stat_statements.max reduces the possibility of a query being
tracked intermittently but does not address the problem of indicating the
probability of erroneous statistics if the query is indeed being tracked
intermittently. I do not believe the admin can influence the value of 
calls_underest as it is not a GUC parameter.

We have a need to see this patch committed hence the revived interest in
this thread

regards
Sameer



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5770844.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


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

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

[samthakur74]

> >You have added this email to the commit fest, but it contains no patch.
>
>Please add the email with the actual patch.
>
 I hope its attached now!

>  Maybe the author should be
> >given a chance to update the patches, though, because they are quite
> >old.
>
 I did connect with Daniel and he did have some improvement ideas. I am not
sure when they could be implemented. Since we have a interest in the
current version of the patch, which needed documentation, i tried to
complete that.
Thank you,
Sameer


pg_stat_statements-identification-v4.patch.gz (8K) 





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5770937.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

[samthakur74]

>You seem to have forgotten to include the pg_stat_statements--1.2.sql
>and pg_stat_statements--1.1--1.2.sql in the patch.

>
> Sorry again. Please find updated patch attached.
>


> <http://www.postgresql.org/mailpref/pgsql-hackers>
>
> NAML<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>



On Tue, Sep 17, 2013 at 5:47 PM, Fujii Masao-2 [via PostgreSQL] <
ml-node+s1045698n5771213...@n5.nabble.com> wrote:

> On Sun, Sep 15, 2013 at 3:54 PM, samthakur74 <[hidden 
> email]<http://user/SendEmail.jtp?type=node&node=5771213&i=0>>
> wrote:
> >
> >
> >> >You have added this email to the commit fest, but it contains no
> patch.
> >>
> >> >Please add the email with the actual patch.
> >
> >  I hope its attached now!
>
> You seem to have forgotten to include the pg_stat_statements--1.2.sql
> and pg_stat_statements--1.1--1.2.sql in the patch.
>
> Regards,
>
> --
> Fujii Masao
>
>
> --
> Sent via pgsql-hackers mailing list ([hidden 
> email]<http://user/SendEmail.jtp?type=node&node=5771213&i=1>)
>
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
>
> http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5771213.html
>  To unsubscribe from pg_stat_statements: calls under-estimation
> propagation, click 
> here<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5738128&code=c2FtdGhha3VyNzRAZ21haWwuY29tfDU3MzgxMjh8ODM4MzYxMTcy>
> .
> NAML<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>


pg_stat_statements-identification-v4.patch.gz (9K) 
<http://postgresql.1045698.n5.nabble.com/attachment/5771248/0/pg_stat_statements-identification-v4.patch.gz>




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5771248.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

[samthakur74]

>I got the segmentation fault when I tested the case where the
least-executed
>query statistics is discarded, i.e., when I executed different queries
more than
>pg_stat_statements.max times. I guess that the patch might have a bug.
Thanks, will try to fix it.

>pg_stat_statements--1.1.sql should be removed.
> Yes will do that
>


> >+  queryid
> >+  bigint
> >+  
> >+  Unique value of each representative statement for the
> >current statistics session.
> >+   This value will change for each new statistics session.
>
> >What does "statistics session" mean?
> The time period when statistics are gathered by statistics collector
> without being reset. So the statistics session continues across normal
> shutdowns, but in case of abnormal situations like crashes, format upgrades
> or statistics being reset for any other reason, a new time period of
> statistics collection starts i.e. a new statistics session. The queryid
> value generation is linked to statistics session so emphasize the fact that
> in case of crashes,format upgrades or any situation of statistics reset,
> the queryid for the same queries will also change. Will update
> documentation clearly explain the term statistics session in this context
>
> regards
Sameer

>
>




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5771562.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

[samthakur74]

On Thu, Sep 19, 2013 at 11:32 AM, Fujii Masao-2 [via PostgreSQL] <
ml-node+s1045698n5771565...@n5.nabble.com> wrote:

> On Thu, Sep 19, 2013 at 2:25 PM, samthakur74 <[hidden 
> email]<http://user/SendEmail.jtp?type=node&node=5771565&i=0>>
> wrote:
>
> >>I got the segmentation fault when I tested the case where the
> >> least-executed
> >>query statistics is discarded, i.e., when I executed different queries
> more
> >> than
> >>pg_stat_statements.max times. I guess that the patch might have a bug.
> > Thanks, will try to fix it.
> >
> >> >pg_stat_statements--1.1.sql should be removed.
> >> Yes will do that
> >
> >
> >>
> >> >+  queryid
> >> >+  bigint
> >> >+  
> >> >+  Unique value of each representative statement for the
> >> >current statistics session.
> >> >+   This value will change for each new statistics
> session.
> >>
> >> >What does "statistics session" mean?
> >> The time period when statistics are gathered by statistics collector
> >> without being reset. So the statistics session continues across normal
> >> shutdowns, but in case of abnormal situations like crashes, format
> upgrades
> >> or statistics being reset for any other reason, a new time period of
> >> statistics collection starts i.e. a new statistics session. The queryid
> >> value generation is linked to statistics session so emphasize the fact
> that
> >> in case of crashes,format upgrades or any situation of statistics
> reset, the
> >> queryid for the same queries will also change.
>
> >I'm afraid that this behavior narrows down the use case of queryid very
> much.
> >For example, since the queryid of the same query would not be the same in
> >the master and the standby servers, we cannot associate those two
> statistics
> >by using the queryid. The queryid changes through the crash recovery, so
> >we cannot associate the query statistics generated before the crash with
> that
> >generated after the crash recovery even if the query is the same.
>
>Yes, these are limitations in this approach. The other approaches
suggested included
1. Expose query id hash value as is, in the view, but document the fact
that it will be unstable between releases
2. Expose query id hash value via an undocumented function and let more
expert users decided if they want to use it.

The approach of using statistics session id to generate queryid is a
compromise between not exposing it at all and exposing it without warning
the users of unstable hash value from query tree between releases.


> >This is not directly related to the patch itself, but why does the
> queryid
> >need to be calculated based on also the "statistics session"?
>
  If we expose hash value of query tree, without using statistics session,
it is possible that users might make wrong assumption that this value
remains stable across version upgrades, when in reality it depends on
whether the version has make changes to query tree internals. So to
explicitly ensure that users do not make this wrong assumption, hash value
generation use statistics session id, which is newly created under
situations described above.

>
> >> Will update documentation
> >> clearly explain the term statistics session in this context
>
> >Yep, that's helpful!
>
> Regards,
> Sameer
>
>




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5771701.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Re: [HACKERS] pg_stat_statements: calls under-estimation propagation

[samthakur74]

>
> I forgot about removal of the relevant SGML, amended here in v6.

Thank you for this!
i did a quick test with following steps:
1. Applied v6 patch
2. make and make install on pg_stat_statements;
3. Restarted Postgres with pg_stat_statements loaded with
pg_stat_statements.max = 4
4. Dropped and created extension pg_stat_statements.

Executed following:
select * from pg_stat_statements_reset();
select * from pgbench_branches ;
select * from pgbench_history ;
 select * from pgbench_tellers ;
 select * from pgbench_accounts;

I expected 4 rows in pg_stat_statements view for each of 4 queries
above. But i saw just 2 rows.

select * from pg_stat_statements;

userid | dbid  | stat_session_id |  query  |   quer
y_id   | calls | total_time |  rows  | shared_blks_hit | shared_blks_read |
shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | l
ocal_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | bl
k_read_time | blk_write_time
+---+-+-+---
---+---+++-+--+-
+-++-+--
--+++---+---
+
 10 | 12900 |21595345 | select * from pgbench_accounts; | -803800319
3522943111 | 1 |108.176 | 10 |1640 |0 |
  0 |   0 |  0 |   0 |
0 |  0 |  0 | 0 |
  0 |  0
 10 | 12900 |21595345 | select * from pgbench_tellers ; | -149722997
7134331757 | 1 |  0.227 | 10 |   1 |0 |
  0 |   0 |  0 |   0 |
0 |  0 |  0 | 0 |
  0 |  0
(2 rows)


Am i doing something wrong?

regards
Sameer




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-stat-statements-calls-under-estimation-propagation-tp5738128p5771960.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.