Re: New SQL counter statistics view (pg_stat_sql)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
59 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Haribabu Kommi-2
On Sun, Aug 21, 2016 at 1:17 AM, Tom Lane <[hidden email]> wrote:

> Haribabu Kommi <[hidden email]> writes:
>> This is a new statistics view that is used to provide the number of
>> SQL operations that are
>> happened on a particular interval of time. This view is useful for the
>> system to find out the
>> pattern of the operations that are happening in the instance during
>> particular interval of
>> time.
>
> 1. This set of counters seems remarkably random.  Why, for instance,
> count reindexes but not original index creations?

I thought of adding the columns to the view that are mostly used(excluding
all DDL commands), otherwise the view columns fill with all the commands
that are supported by PostgreSQL.

> 2. What will you do with cases such as SELECTs containing modifying CTEs?
> Or EXPLAIN ANALYZE?  Does CLUSTER count as a REINDEX?  Does REFRESH
> MATERIALIZED VIEW count as a SELECT?  (Or maybe it's an INSERT?)

Cluster commands will be calculated as clusters, explain needs some
logic to identify what SQL operation exactly. This is because of using
nodeTag data from the parseTree structure to identify what type of SQL
statement it is.

The counters will not updated for any SQL type that is not as part of the view.

> If you're going to be selective about what you count, you're forever
> going to be fielding complaints from users who are unhappy that you
> didn't count some statement type they care about, or feel that you
> misclassified some complex case.

Yes, I agree that users may complain regarding with limited columns that are not
sufficient for their use. But with the mostly used columns, this view
may be useful for
some of the users to find out the pattern of the SQL operations that
are happening
on the instance.

> I'm inclined to suggest you forget this approach and propose a single
> counter for "SQL commands executed", which avoids all of the above
> definitional problems.  People who need more detail than that are
> probably best advised to look to contrib/pg_stat_statements, anyway.

I will add a new column to the pg_stat_database, to track the number of SQL
operations that are happened on this particular database.

Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Robert Haas
On Sat, Aug 20, 2016 at 11:17 AM, Tom Lane <[hidden email]> wrote:
> I'm inclined to suggest you forget this approach and propose a single
> counter for "SQL commands executed", which avoids all of the above
> definitional problems.  People who need more detail than that are
> probably best advised to look to contrib/pg_stat_statements, anyway.

I disagree.  I think SQL commands executed, lumping absolutely
everything together, really isn't much use.  Haribabu's categorization
scheme seems to need some work, but the idea of categorizing
statements by type and counting executions per type seems very
reasonable.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Andres Freund
On 2016-08-22 13:54:43 -0400, Robert Haas wrote:
> On Sat, Aug 20, 2016 at 11:17 AM, Tom Lane <[hidden email]> wrote:
> > I'm inclined to suggest you forget this approach and propose a single
> > counter for "SQL commands executed", which avoids all of the above
> > definitional problems.  People who need more detail than that are
> > probably best advised to look to contrib/pg_stat_statements, anyway.
>
> I disagree.  I think SQL commands executed, lumping absolutely
> everything together, really isn't much use.

I'm inclined to agree. I think that's a quite useful stat when looking
at an installation one previously didn't have a lot of interaction with.


> Haribabu's categorization
> scheme seems to need some work, but the idea of categorizing
> statements by type and counting executions per type seems very
> reasonable.

I'd consider instead using something like COALESCE(commandType,
nodeTag(Query->utilityStmt)) as the categories. Not sure if I'd even
pivot that.

Andres


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Tom Lane-2
Andres Freund <[hidden email]> writes:
> On 2016-08-22 13:54:43 -0400, Robert Haas wrote:
>> On Sat, Aug 20, 2016 at 11:17 AM, Tom Lane <[hidden email]> wrote:
>>> I'm inclined to suggest you forget this approach and propose a single
>>> counter for "SQL commands executed", which avoids all of the above
>>> definitional problems.  People who need more detail than that are
>>> probably best advised to look to contrib/pg_stat_statements, anyway.

>> I disagree.  I think SQL commands executed, lumping absolutely
>> everything together, really isn't much use.

> I'm inclined to agree. I think that's a quite useful stat when looking
> at an installation one previously didn't have a lot of interaction with.

Well, let's at least have an "other" category so you can add up the
counters and get a meaningful total.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Gavin Flower-2
On 23/08/16 08:27, Tom Lane wrote:

> Andres Freund <[hidden email]> writes:
>> On 2016-08-22 13:54:43 -0400, Robert Haas wrote:
>>> On Sat, Aug 20, 2016 at 11:17 AM, Tom Lane <[hidden email]> wrote:
>>>> I'm inclined to suggest you forget this approach and propose a single
>>>> counter for "SQL commands executed", which avoids all of the above
>>>> definitional problems.  People who need more detail than that are
>>>> probably best advised to look to contrib/pg_stat_statements, anyway.
>>> I disagree.  I think SQL commands executed, lumping absolutely
>>> everything together, really isn't much use.
>> I'm inclined to agree. I think that's a quite useful stat when looking
>> at an installation one previously didn't have a lot of interaction with.
> Well, let's at least have an "other" category so you can add up the
> counters and get a meaningful total.
>
> regards, tom lane
>
>
Initially I thought of something I thought was factious, but then
realized it might actually be both practicable & useful...

How about 2 extra categories (if appropriate!!!):

 1. Things that actually might be sort of as fitting in 2 or more of the
    existing categories, or there is an ambiguity as to which category
    is appropriate.

 2. Things that don't fit into any existing category

This was inspired by a real use case, in a totally unrelated area - but
where I attempted to ensure counts were in the most useful categories I
was able to provide.  The user had listed categories, but I found that
the data didn't always fit neatly into them as specified.


Cheers,

Gavin




--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

neha khatri
In reply to this post by Tom Lane-2
>Andres Freund <[hidden email]> writes:
>> On 2016-08-22 13:54:43 -0400, Robert Haas wrote:
>> On Sat, Aug 20, 2016 at 11:17 AM, Tom Lane <[hidden email]> wrote:
>>>> I'm inclined to suggest you forget this approach and propose a single
>>>> counter for "SQL commands executed", which avoids all of the above
>>>> definitional problems.  People who need more detail than that are
>>>> probably best advised to look to contrib/pg_stat_statements, anyway.

>>> I disagree.  I think SQL commands executed, lumping absolutely
>>> everything together, really isn't much use.

>> I'm inclined to agree. I think that's a quite useful stat when looking
>> at an installation one previously didn't have a lot of interaction with.

>Well, let's at least have an "other" category so you can add up the
>counters and get a meaningful total.

How would that meaningful total might help a user. What can a user might analyse with the counter in 'other' category.


Neha

Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Gavin Flower-2
On 24/08/16 12:02, neha khatri wrote:

> >Andres Freund <[hidden email] <mailto:[hidden email]>> writes:
> >> On 2016-08-22 13:54:43 -0400, Robert Haas wrote:
> >> On Sat, Aug 20, 2016 at 11:17 AM, Tom Lane <[hidden email]
> <mailto:[hidden email]>> wrote:
> >>>> I'm inclined to suggest you forget this approach and propose a single
> >>>> counter for "SQL commands executed", which avoids all of the above
> >>>> definitional problems.  People who need more detail than that are
> >>>> probably best advised to look to contrib/pg_stat_statements, anyway.
>
> >>> I disagree.  I think SQL commands executed, lumping absolutely
> >>> everything together, really isn't much use.
>
> >> I'm inclined to agree. I think that's a quite useful stat when looking
> >> at an installation one previously didn't have a lot of interaction
> with.
>
> >Well, let's at least have an "other" category so you can add up the
> >counters and get a meaningful total.
>
> How would that meaningful total might help a user. What can a user
> might analyse with the counter in 'other' category.
>
>
> Neha
>
The user could then judge if there were a significant number of examples
not covered in the other categories - this may, or may not, be a
problem; depending on the use case.


Cheers,
Gavin



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

neha khatri

On Wed, Aug 24, 2016 at 10:07 AM, Gavin Flower <[hidden email]> wrote:
On 24/08/16 12:02, neha khatri wrote:
>Andres Freund <[hidden email] <mailto:[hidden email]>> writes:
>> On 2016-08-22 13:54:43 -0400, Robert Haas wrote:
>> On Sat, Aug 20, 2016 at 11:17 AM, Tom Lane <[hidden email] <mailto:[hidden email]>> wrote:
>>>> I'm inclined to suggest you forget this approach and propose a single
>>>> counter for "SQL commands executed", which avoids all of the above
>>>> definitional problems.  People who need more detail than that are
>>>> probably best advised to look to contrib/pg_stat_statements, anyway.

>>> I disagree.  I think SQL commands executed, lumping absolutely
>>> everything together, really isn't much use.

>> I'm inclined to agree. I think that's a quite useful stat when looking
>> at an installation one previously didn't have a lot of interaction with.

>Well, let's at least have an "other" category so you can add up the
>counters and get a meaningful total.

How would that meaningful total might help a user. What can a user might analyse with the counter in 'other' category.



The user could then judge if there were a significant number of examples not covered in the other categories - this may, or may not, be a problem; depending on the use case.


Cheers,
Gavin

For the user to be able to judge that whether the number in the 'other' category is a problem or not, the user is also required to know what all might fall under the 'other' category. It may not be good to say that _anything_ that is not part of the already defined category is part of 'other'. Probably, 'other' should also be a set of predefined operations.

Neha
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Haribabu Kommi-2
In reply to this post by Andres Freund
On Tue, Aug 23, 2016 at 3:59 AM, Andres Freund <[hidden email]> wrote:
>> Haribabu's categorization
>> scheme seems to need some work, but the idea of categorizing
>> statements by type and counting executions per type seems very
>> reasonable.
>
> I'd consider instead using something like COALESCE(commandType,
> nodeTag(Query->utilityStmt)) as the categories. Not sure if I'd even
> pivot that.

CommandType means to use select, insert, update, delete and utility
as the categorization to display the counters? I think I am not getting
your point correctly.

Apart from the above, here are the following list of command tags that
are generated in the code, I took only the first word of the command tag
just to see how many categories present. The number indicates the
subset of operations or number of types it is used. Like create table,
create function and etc.

insert
delete
update
select (6)
transaction (10)
declare
close (2)
move
fetch
create (37)
drop (36)
Alter (56)
import
truncate
comment
security
copy
grant
revoke
notify
listen
unlisten
load
cluster
vacuum
analyze
explain
refresh
set (2)
reset
show
discard (4)
reassign
lock
checkpoint
reindex
prepare
execute
deallocate

we can try to pick something from the above list also for main
categories and rest will fall under
other.


Regards,
Hari Babu
Fujitsu Australia


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Alvaro Herrera-9
Haribabu Kommi wrote:

> Apart from the above, here are the following list of command tags that
> are generated in the code, I took only the first word of the command tag
> just to see how many categories present. The number indicates the
> subset of operations or number of types it is used. Like create table,
> create function and etc.

Sounds about right.  I suppose all those cases that you aggregated here
would expand to full tags in the actual code.  I furthermore suppose
that some of these could be ignored, such as the transaction ones and
things like load, lock, move, fetch, discard, deallocate (maybe lump
them all together under "other", or some other rough categorization, as
Tom suggests).  Also, for many of these commands it's probably relevant
whether they are acting on a temporary object or not; we should either
count these separately, or not count the temp ones at all.

> insert
> delete
> update
> select (6)
> transaction (10)
> declare
> close (2)
> move
> fetch
> create (37)
> drop (36)
> Alter (56)
> import
> truncate
> comment
> security
> copy
> grant
> revoke
> notify
> listen
> unlisten
> load
> cluster
> vacuum
> analyze
> explain
> refresh
> set (2)
> reset
> show
> discard (4)
> reassign
> lock
> checkpoint
> reindex
> prepare
> execute
> deallocate

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Haribabu Kommi-2


On Wed, Aug 31, 2016 at 3:19 AM, Alvaro Herrera <[hidden email]> wrote:

> Haribabu Kommi wrote:
>
>> Apart from the above, here are the following list of command tags that
>> are generated in the code, I took only the first word of the command tag
>> just to see how many categories present. The number indicates the
>> subset of operations or number of types it is used. Like create table,
>> create function and etc.
>
> Sounds about right.  I suppose all those cases that you aggregated here
> would expand to full tags in the actual code.  I furthermore suppose
> that some of these could be ignored, such as the transaction ones and
> things like load, lock, move, fetch, discard, deallocate (maybe lump
> them all together under "other", or some other rough categorization, as
> Tom suggests).
Following is the pg_stat_sql view with the SQL categories that I considered
that are important. Rest of the them will be shown under others category.

postgres=# \d pg_stat_sql
             View "pg_catalog.pg_stat_sql"
     Column      |           Type           | Modifiers 
-----------------+--------------------------+-----------
 inserts         | bigint                   | 
 deletes         | bigint                   | 
 updates         | bigint                   | 
 selects         | bigint                   | 
 declare_cursors | bigint                   | 
 closes          | bigint                   | 
 creates         | bigint                   | 
 drops           | bigint                   | 
 alters          | bigint                   | 
 imports         | bigint                   | 
 truncates       | bigint                   | 
 copies          | bigint                   | 
 grants          | bigint                   | 
 revokes         | bigint                   | 
 clusters        | bigint                   | 
 vacuums         | bigint                   | 
 analyzes        | bigint                   | 
 refreshs        | bigint                   | 
 locks           | bigint                   | 
 checkpoints     | bigint                   | 
 reindexes       | bigint                   | 
 deallocates     | bigint                   | 
 others          | bigint                   | 
 stats_reset     | timestamp with time zone | 


If any additions/deletions, I can accommodate them.

The stats data gets updated in exec_simple_query and exec_execute_message
functions and the collected stats will be sent to stats collector similar
like function usage stats in pgstat_report_stat function.

These SQL statistics data is stored in the stats file similar like global
statistics. The STAT file format is updated to accommodate the new stats.

A new GUC "track_sql" is added to track the SQL statement
statistics, by default this is off. Only superuser can change this
parameter.

Attached a patch for the same.

>  Also, for many of these commands it's probably relevant
> whether they are acting on a temporary object or not; we should either
> count these separately, or not count the temp ones at all.

Currently the SQL stats are not checking any object level that is a temp
one or not? The temp objects are specific to a backend only. But what
I feel, any way that is an SQL query that was executed on a temp object,
so we need to count that operation.

I feel this SQL stats should not worry about the object type. May be I am
wrong.

Regards,
Hari Babu
Fujitsu Australia


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

pg_stat_sql_1.patch (50K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Robert Haas
On Fri, Sep 2, 2016 at 2:33 AM, Haribabu Kommi <[hidden email]> wrote:

> On Wed, Aug 31, 2016 at 3:19 AM, Alvaro Herrera <[hidden email]>
> wrote:
>> Haribabu Kommi wrote:
>>
>>> Apart from the above, here are the following list of command tags that
>>> are generated in the code, I took only the first word of the command tag
>>> just to see how many categories present. The number indicates the
>>> subset of operations or number of types it is used. Like create table,
>>> create function and etc.
>>
>> Sounds about right.  I suppose all those cases that you aggregated here
>> would expand to full tags in the actual code.  I furthermore suppose
>> that some of these could be ignored, such as the transaction ones and
>> things like load, lock, move, fetch, discard, deallocate (maybe lump
>> them all together under "other", or some other rough categorization, as
>> Tom suggests).
>
> Following is the pg_stat_sql view with the SQL categories that I considered
> that are important. Rest of the them will be shown under others category.
>
> postgres=# \d pg_stat_sql
>              View "pg_catalog.pg_stat_sql"
>      Column      |           Type           | Modifiers
> -----------------+--------------------------+-----------
>  inserts         | bigint                   |
>  deletes         | bigint                   |
>  updates         | bigint                   |
>  selects         | bigint                   |
>  declare_cursors | bigint                   |
>  closes          | bigint                   |
>  creates         | bigint                   |
>  drops           | bigint                   |
>  alters          | bigint                   |
>  imports         | bigint                   |
>  truncates       | bigint                   |
>  copies          | bigint                   |
>  grants          | bigint                   |
>  revokes         | bigint                   |
>  clusters        | bigint                   |
>  vacuums         | bigint                   |
>  analyzes        | bigint                   |
>  refreshs        | bigint                   |
>  locks           | bigint                   |
>  checkpoints     | bigint                   |
>  reindexes       | bigint                   |
>  deallocates     | bigint                   |
>  others          | bigint                   |
>  stats_reset     | timestamp with time zone |
>
>
> If any additions/deletions, I can accommodate them.

I think it is not a good idea to make the command names used here the
plural forms of the command tags.  Instead of "inserts", "updates",
"imports", etc. just use "INSERT", "UPDATE", "IMPORT".  That's simpler
and less error prone - e.g. you won't end up with things like
"refreshs", which is not a word.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Haribabu Kommi-2


On Thu, Sep 15, 2016 at 6:01 AM, Robert Haas <[hidden email]> wrote:
On Fri, Sep 2, 2016 at 2:33 AM, Haribabu Kommi <[hidden email]> wrote:
> On Wed, Aug 31, 2016 at 3:19 AM, Alvaro Herrera <[hidden email]>
> wrote:
>> Haribabu Kommi wrote:
>>
>>> Apart from the above, here are the following list of command tags that
>>> are generated in the code, I took only the first word of the command tag
>>> just to see how many categories present. The number indicates the
>>> subset of operations or number of types it is used. Like create table,
>>> create function and etc.
>>
>> Sounds about right.  I suppose all those cases that you aggregated here
>> would expand to full tags in the actual code.  I furthermore suppose
>> that some of these could be ignored, such as the transaction ones and
>> things like load, lock, move, fetch, discard, deallocate (maybe lump
>> them all together under "other", or some other rough categorization, as
>> Tom suggests).
>
> Following is the pg_stat_sql view with the SQL categories that I considered
> that are important. Rest of the them will be shown under others category.
>
> postgres=# \d pg_stat_sql
>              View "pg_catalog.pg_stat_sql"
>      Column      |           Type           | Modifiers
> -----------------+--------------------------+-----------
>  inserts         | bigint                   |
>  deletes         | bigint                   |
>  updates         | bigint                   |
>  selects         | bigint                   |
>  declare_cursors | bigint                   |
>  closes          | bigint                   |
>  creates         | bigint                   |
>  drops           | bigint                   |
>  alters          | bigint                   |
>  imports         | bigint                   |
>  truncates       | bigint                   |
>  copies          | bigint                   |
>  grants          | bigint                   |
>  revokes         | bigint                   |
>  clusters        | bigint                   |
>  vacuums         | bigint                   |
>  analyzes        | bigint                   |
>  refreshs        | bigint                   |
>  locks           | bigint                   |
>  checkpoints     | bigint                   |
>  reindexes       | bigint                   |
>  deallocates     | bigint                   |
>  others          | bigint                   |
>  stats_reset     | timestamp with time zone |
>
>
> If any additions/deletions, I can accommodate them.

I think it is not a good idea to make the command names used here the
plural forms of the command tags.  Instead of "inserts", "updates",
"imports", etc. just use "INSERT", "UPDATE", "IMPORT".  That's simpler
and less error prone - e.g. you won't end up with things like
"refreshs", which is not a word.

Thanks for your suggestion. I also thought of changing the name while writing
"refreshs" as a column name of the view originally. A small restriction with the
change of names to command names is that, user needs to execute the query
as follows.

select pg_stat_sql.select from pg_stat_sql;

Updated patch is attached with the corrections. Apart from name change, 
added documentation and tests.
 
Regards,
Hari Babu
Fujitsu Australia


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

pg_stat_sql_2.patch (65K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Peter Eisentraut-6
In reply to this post by Robert Haas
On 9/14/16 4:01 PM, Robert Haas wrote:
> I think it is not a good idea to make the command names used here the
> plural forms of the command tags.  Instead of "inserts", "updates",
> "imports", etc. just use "INSERT", "UPDATE", "IMPORT".  That's simpler
> and less error prone - e.g. you won't end up with things like
> "refreshs", which is not a word.

How about having the tag not be a column name but a row entry.  So you'd
do something like

SELECT * FROM pg_stat_sql WHERE tag = 'ALTER VIEW';

That way, we don't have to keep updating (and re-debating) this when new
command types or subtypes are added.  And queries written for future
versions will not fail when run against old servers.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

David Fetter
On Wed, Sep 21, 2016 at 11:25:14AM -0400, Peter Eisentraut wrote:

> On 9/14/16 4:01 PM, Robert Haas wrote:
> > I think it is not a good idea to make the command names used here the
> > plural forms of the command tags.  Instead of "inserts", "updates",
> > "imports", etc. just use "INSERT", "UPDATE", "IMPORT".  That's simpler
> > and less error prone - e.g. you won't end up with things like
> > "refreshs", which is not a word.
>
> How about having the tag not be a column name but a row entry.  So you'd
> do something like
>
> SELECT * FROM pg_stat_sql WHERE tag = 'ALTER VIEW';

+1 for this.  It's MUCH easier to deal with changes in row counts than
changes in row type.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Alvaro Herrera-9
In reply to this post by Peter Eisentraut-6
Peter Eisentraut wrote:

> How about having the tag not be a column name but a row entry.  So you'd
> do something like
>
> SELECT * FROM pg_stat_sql WHERE tag = 'ALTER VIEW';
>
> That way, we don't have to keep updating (and re-debating) this when new
> command types or subtypes are added.  And queries written for future
> versions will not fail when run against old servers.

Yeah, good idea.

Let's also discuss the interface from the stats collector.  Currently we
have some 20 new SQL functions, all alike, each loading the whole data
and returning a single counter, and then the view invokes each function
separately.  That doesn't seem great to me.  How about having a single C
function that returns the whole thing as a SRF instead, and the view is
just a single function invocation -- something like pg_lock_status
filling pg_locks in one go.

Another consideration is that the present patch lumps together all ALTER
cases in a single counter.  This isn't great, but at the same time we
don't want to bloat the stat files by having hundreds of counters per
database, do we?

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

David Fetter
On Wed, Sep 21, 2016 at 02:05:24PM -0300, Alvaro Herrera wrote:
> Another consideration is that the present patch lumps together all
> ALTER cases in a single counter.  This isn't great, but at the same
> time we don't want to bloat the stat files by having hundreds of
> counters per database, do we?

I count 37 documented versions of ALTER as of git master.  Is there
some multiplier I'm missing?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Haribabu Kommi-2
In reply to this post by Alvaro Herrera-9


On Thu, Sep 22, 2016 at 3:05 AM, Alvaro Herrera <[hidden email]> wrote:
Peter Eisentraut wrote:

> How about having the tag not be a column name but a row entry.  So you'd
> do something like
>
> SELECT * FROM pg_stat_sql WHERE tag = 'ALTER VIEW';
>
> That way, we don't have to keep updating (and re-debating) this when new
> command types or subtypes are added.  And queries written for future
> versions will not fail when run against old servers.

Yeah, good idea.

Yes, Having it as a row entry is good.
 
 
Let's also discuss the interface from the stats collector.  Currently we
have some 20 new SQL functions, all alike, each loading the whole data
and returning a single counter, and then the view invokes each function
separately.  That doesn't seem great to me.  How about having a single C
function that returns the whole thing as a SRF instead, and the view is
just a single function invocation -- something like pg_lock_status
filling pg_locks in one go.

Another consideration is that the present patch lumps together all ALTER
cases in a single counter.  This isn't great, but at the same time we
don't want to bloat the stat files by having hundreds of counters per
database, do we?

Currently, The SQL stats is a fixed size counter to track the all the ALTER 
cases as single counter. So while sending the stats from the backend to
stats collector at the end of the transaction, the cost is same, because of
it's fixed size. This approach adds overhead to send and read the stats
is minimal.

With the following approach, I feel it is possible to support the counter at
command tag level.

Add a Global and local Hash to keep track of the counters by using the
command tag as the key, this hash table increases dynamically whenever
a new type of SQL command gets executed. The Local Hash data is passed
to stats collector whenever the transaction gets committed.

The problem I am thinking is that, Sending data from Hash and populating
the Hash from stats file for all the command tags adds some overhead.


Regards,
Hari Babu
Fujitsu Australia
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

Haribabu Kommi-2


On Thu, Sep 29, 2016 at 3:45 PM, Haribabu Kommi <[hidden email]> wrote:


On Thu, Sep 22, 2016 at 3:05 AM, Alvaro Herrera <[hidden email]> wrote:
Peter Eisentraut wrote:

> How about having the tag not be a column name but a row entry.  So you'd
> do something like
>
> SELECT * FROM pg_stat_sql WHERE tag = 'ALTER VIEW';
>
> That way, we don't have to keep updating (and re-debating) this when new
> command types or subtypes are added.  And queries written for future
> versions will not fail when run against old servers.

Yeah, good idea.

Yes, Having it as a row entry is good.
 
 
Let's also discuss the interface from the stats collector.  Currently we
have some 20 new SQL functions, all alike, each loading the whole data
and returning a single counter, and then the view invokes each function
separately.  That doesn't seem great to me.  How about having a single C
function that returns the whole thing as a SRF instead, and the view is
just a single function invocation -- something like pg_lock_status
filling pg_locks in one go.

Another consideration is that the present patch lumps together all ALTER
cases in a single counter.  This isn't great, but at the same time we
don't want to bloat the stat files by having hundreds of counters per
database, do we?

Currently, The SQL stats is a fixed size counter to track the all the ALTER 
cases as single counter. So while sending the stats from the backend to
stats collector at the end of the transaction, the cost is same, because of
it's fixed size. This approach adds overhead to send and read the stats
is minimal.

With the following approach, I feel it is possible to support the counter at
command tag level.

Add a Global and local Hash to keep track of the counters by using the
command tag as the key, this hash table increases dynamically whenever
a new type of SQL command gets executed. The Local Hash data is passed
to stats collector whenever the transaction gets committed.

The problem I am thinking is that, Sending data from Hash and populating
the Hash from stats file for all the command tags adds some overhead.


I tried changing the pg_stat_sql into row mode and ran the regress suite to 
add different type of SQL commands to the view and ran the pgbench test
on my laptop to find out any performance impact with this patch.

                                HEAD      PATCH
pgbench - select      828          816

Here I attached the pg_stat_sql patch to keep track of all SQL commands
based on the commandTag and their counts. I attached the result of this
view that is run on the database after "make installcheck" just for reference.

Regards,
Hari Babu
Fujitsu Australia


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

pg_stat_sql_output.txt (8K) Download Attachment
pg_stat_sql_row_mode_1.patch (34K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: New SQL counter statistics view (pg_stat_sql)

vinayak



On 2016/10/12 12:21, Haribabu Kommi wrote:


On Thu, Sep 29, 2016 at 3:45 PM, Haribabu Kommi <[hidden email]> wrote:


On Thu, Sep 22, 2016 at 3:05 AM, Alvaro Herrera <[hidden email]> wrote:
Peter Eisentraut wrote:

> How about having the tag not be a column name but a row entry.  So you'd
> do something like
>
> SELECT * FROM pg_stat_sql WHERE tag = 'ALTER VIEW';
>
> That way, we don't have to keep updating (and re-debating) this when new
> command types or subtypes are added.  And queries written for future
> versions will not fail when run against old servers.

Yeah, good idea.

Yes, Having it as a row entry is good.
 
 
Let's also discuss the interface from the stats collector.  Currently we
have some 20 new SQL functions, all alike, each loading the whole data
and returning a single counter, and then the view invokes each function
separately.  That doesn't seem great to me.  How about having a single C
function that returns the whole thing as a SRF instead, and the view is
just a single function invocation -- something like pg_lock_status
filling pg_locks in one go.

Another consideration is that the present patch lumps together all ALTER
cases in a single counter.  This isn't great, but at the same time we
don't want to bloat the stat files by having hundreds of counters per
database, do we?

Currently, The SQL stats is a fixed size counter to track the all the ALTER 
cases as single counter. So while sending the stats from the backend to
stats collector at the end of the transaction, the cost is same, because of
it's fixed size. This approach adds overhead to send and read the stats
is minimal.

With the following approach, I feel it is possible to support the counter at
command tag level.

Add a Global and local Hash to keep track of the counters by using the
command tag as the key, this hash table increases dynamically whenever
a new type of SQL command gets executed. The Local Hash data is passed
to stats collector whenever the transaction gets committed.

The problem I am thinking is that, Sending data from Hash and populating
the Hash from stats file for all the command tags adds some overhead.


I tried changing the pg_stat_sql into row mode and ran the regress suite to 
add different type of SQL commands to the view and ran the pgbench test
on my laptop to find out any performance impact with this patch.

                                HEAD      PATCH
pgbench - select      828          816

Here I attached the pg_stat_sql patch to keep track of all SQL commands
based on the commandTag and their counts. I attached the result of this
view that is run on the database after "make installcheck" just for reference.

Thank you for the patch.

Test: Commands with uppercase and lowercase
====
If the tag='select' then it returns the 0 rows but count is actually increment by 1.

tag='select' vs tag='SELECT'

postgres=# SET track_sql TO ON;
SET
postgres=# SELECT * FROM pg_stat_sql where tag='SELECT';
  tag   | count
--------+-------
 SELECT |    12
(1 row)

postgres=# SELECT * FROM pg_stat_sql where tag='SELECT';
  tag   | count
--------+-------
 SELECT |    13
(1 row)

postgres=# SELECT * FROM pg_stat_sql where tag='select';
 tag | count
-----+-------
(0 rows)

postgres=# SELECT * FROM pg_stat_sql where tag='SELECT';
  tag   | count
--------+-------
 SELECT |    15
(1 row)

I think all command works same as above.

Regards,
Vinayak Pokale
NTT Open Source Software Center
123