[pgpool-hackers: 713] Re: Logging load balancing result

Tatsuo Ishii ishii at postgresql.org
Thu Dec 11 09:59:24 JST 2014


I have implemented this. Here is an example session:

psql -c "show pool_nodes" -p 11000 test
 node_id | hostname | port  | status | lb_weight |  role   | select_cnt 
---------+----------+-------+--------+-----------+---------+------------
 0       | /tmp     | 11002 | 2      | 0.500000  | primary | 18
 1       | /tmp     | 11003 | 2      | 0.500000  | standby | 15
(2 rows)

The reason why the new column is named "select_cnt" rather than
"lbs_stat" is, we could potentially log queries other than SELECT. We
could add "update_cnt", "insert_cnt" etc. if we like. However at this
moment I only take care of SELECTs. BTW, "SELECT" does not necessary
means "read only SELECT". For now, every SELECTs including "SELECT FOR
UPDATE" for example, are took account of the counter.

The counters are in shared memory area and resets upon pgpool-II
starting up. I hope upcoming new pcp command could reset the counter
without restarting.

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

> Hi
> 
> I think this is a very good idea, and a much needed feature. As load
> balancing is one of the prime feature of the pgpool-II and getting
> these stats will be very helpful and informative.
> 
> Although I think having the ability to reset stats should also be
> included as the part of this feature and it would be nice to add a
> pool_ command to check the stats reset time.
> 
> 
> Kind regards,
> Muhammad Usama
> 
> On Fri, Nov 21, 2014 at 5:07 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:
>> 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
>> _______________________________________________
>> pgpool-hackers mailing list
>> pgpool-hackers at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-hackers


More information about the pgpool-hackers mailing list