[pgpool-hackers: 3764] Re: More SQL stats?

[Tatsuo Ishii]

> Currently we only collect SELECT counts for each backend node and show
> them in "show pool_nodes". I think it would be nice if we could
> collect other counts, for example INSERT/UPDATE/DELETE and show
> them. I know that PostgreSQL already has such a feature but still I
> think having that statistics in Pgpool-II would be convenient for
> admins because he/she can grab statistics on all node at once, rather
> than querying against each backend node one by one.
> 
> Moreover we already have such that statistics are on shared memory:
> 
> /*
>  * Per backend node stat area in shared memory
>  */
> typedef struct
> {
> 	uint64		select_cnt;		/* number of read SELECT queries issued */
> 	uint64		insert_cnt;		/* number of INSERT queries issued */
> 	uint64		update_cnt;		/* number of UPDATE queries issued */
> 	uint64		delete_cnt;		/* number of DELETE queries issued */
> 	uint64		ddl_cnt;		/* number of DDL queries issued */
> 	uint64		other_cnt;		/* number of any other queries issued */
> }			PER_NODE_STAT;
> [src/utils/statistics.c]
> 
> Actually we haven't used them except select_cnt. The only thing we
> have to do is, update the stats above when SQL is executed.
> 
> It is debatable we'd better to add another show command for this
> purpose however. (show pool_nodes has already many columns).
> 
> Opinions?

Attached patch is for this. I added new column insert_cnt, update_cnt,
delete_cnt, ddl_cnt, other_cnt. Here is a sample output from show
pool_nodes;

test=# show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role   | select_cnt | insert_cnt | update_cnt | delete_cnt | ddl_cnt | other_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+-------+--------+-----------+---------+------------+------------+------------+------------+---------+-----------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | /tmp     | 11002 | up     | 0.500000  | primary | 0          | 11         | 1          | 1          | 10      | 10        | false             | 0                 |                   |                        | 2020-08-02 19:27:46
 1       | /tmp     | 11003 | up     | 0.500000  | standby | 1          | 0          | 0          | 0          | 0       | 4         | true              | 0                 | streaming         | async                  | 2020-08-02 19:27:46
(2 rows)

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
-------------- next part --------------
A non-text attachment was scrubbed...
Name: more_stats.diff
Type: text/x-patch
Size: 9213 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200802/778a8ff7/attachment.bin>