Re: New SQL counter statistics view (pg_stat_sql)

On Fri, Oct 14, 2016 at 7:48 PM, vinayak <Pokale_Vinayak_q3(at)lab(dot)ntt(dot)co(dot)jp>
wrote:

>
> On 2016/10/12 12:21, Haribabu Kommi wrote:
>
> 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.
>
> Some comments:
> I think we can use pgstat_* instead of pgstat* for code consistency.
>
> +static HTAB *pgStatBackendSql = NULL;
> How about *pgstat_backend_sql
>
> +HTAB *pgStatSql = NULL;
> How about *pgstat_sql
>

Changed.

> +static void pgstat_recv_sqlstmt(PgStat_MsgSqlstmt * msg, int len);
> How about PgStat_MsgSqlstmt *msg instead of PgStat_MsgSqlstmt * msg
>

Added the typdef into typdef.list file so this problem never occurs.

> +typedef struct PgStatSqlstmtEntry
> How about PgStat_SqlstmtEntry
>
> +typedef struct PgStatMsgSqlstmt
> How about PgStat_MsgSqlstmt
>

changed.

> I have observed below behavior.
> I have SET track_sql to ON and then execute the SELECT command and it
> return 0 rows.
> It start counting from the second command.
> postgres=# SET track_sql TO ON;
> SET
> 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 | 1
> (1 row)
> Is this a correct behavior?
>

Yes. This is because the stats of the SQL statements that are collected are
sent to
the stats collector at the end of the SQL statement execution. The current
SQL statement
is not counted in the result.

Updated patch is attached.

Regards,
Hari Babu
Fujitsu Australia