Re: New SQL counter statistics view (pg_stat_sql)

On Wed, Aug 31, 2016 at 3:19 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
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