On Wed, Aug 31, 2016 at 3:19 AM, Alvaro Herrera <alvhe...@2ndquadrant.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
pg_stat_sql_1.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
