[pgpool-hackers: 676] Logging load balancing result

[Tatsuo Ishii]

Hi,

We are frequently asked a way to know how SELECTs are load balanced
among database nodes. For now there are two ways:

1) enable log_per_node_statement and grep pgpool.log

2) enable log_statement and grep PostgreSQL log

Both increase log volume and are needed to create scripts or set up
external monitoring tools such as zabbix. Also I feel #1 and #2 are
overkill for those who just want to know number of SELECTs load
balanced and are not interested in individual SELECT statement.

So I would like to propose to add new functionality to allow to gather
load balancing statistics. The result will be shown using "show
pool_nodes" command in a new column "lbs_stat".

=> show pool_nodes;
 node_id | hostname | port  | status | lb_weight |  role   | lbs_stat
---------+----------+-------+--------+-----------+---------+---------
 0       | /tmp     | 11000 | 2      | 0.333333  | primary | 12345
 1       | /tmp     | 11001 | 2      | 0.333333  | standby | 45678
 2       | /tmp     | 11002 | 2      | 0.333333  | standby | 56789

Here lbs_stat indicates how many SELECTs are load balanced to each node.

The load balancing statistics are accumulated in a shared memory area
and will have quite little overhead. So I feel there's no need to the
functionality turned off.

Comments and suggestions are welcome.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp