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

[Tatsuo Ishii]

>> Hi Ishii-san,
>> 
>> On Thu, 06 Aug 2020 10:52:55 +0900 (JST)
>> Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> 
>>> Hi Pgpool-II hackers,
>>> 
>>> >> 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.
>>> 
>>> My coleague suggested that it would be useful for admins if Pgpool-II
>>> can provide error information like:
>>> 
>>> count of ERROR
>>> count of FATAL
>>> count of PANIC
>>> 
>>> This kind of statistics is not available in PostgreSQL, but I think it
>>> is crusial for users.
>> 
>> 
>> Yes. I think such statistics is useful for monitoring PostgreSQL.
>> 
>>> If we implement it, probably we should have separate show command like
>>> below because now the number of columns of "pgpool show" is too long.
>> 
>> I agree to use a separate show commad for statistics.
> 
> Ok, I have committed/pushed changes to implement as seprate show
> command.
> 
> test=# show pool_backend_stats;
>  node_id | hostname | port  | status |  role   | select_cnt | insert_cnt | update_cnt | delete_cnt | ddl_cnt | other_cnt 
> ---------+----------+-------+--------+---------+------------+------------+------------+------------+---------+-----------
>  0       | /tmp     | 11002 | up     | primary | 27773      | 27784      | 83273      | 0          | 11      | 83298
>  1       | /tmp     | 11003 | up     | standby | 1          | 0          | 0          | 0          | 0       | 41410
> (2 rows)
> 
> I am going to add error counts if there's no objection.

Done.

t-ishii$ psql -p 11000 -c "show pool_backend_stats" test
 node_id | hostname | port  | status |  role   | select_cnt | insert_cnt | update_cnt | delete_cnt | ddl_cnt | other_cnt | panic_cnt | fatal_cnt | error_cnt 
---------+----------+-------+--------+---------+------------+------------+------------+------------+---------+-----------+-----------+-----------+-----------
 0       | /tmp     | 11002 | up     | primary | 1889       | 1899       | 5651       | 1          | 11      | 3798      | 0         | 0         | 2
 1       | /tmp     | 11003 | up     | standby | 1          | 0          | 0          | 0          | 0       | 763       | 0         | 0         | 0
(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