[pgpool-hackers: 3765] 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)

Attached patch is for regression 003 failover test, which needs
modifications to sync with "show pool_nodes" output change.

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_test.diff
Type: text/x-patch
Size: 4990 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200803/3a02630a/attachment-0001.bin>