[pgpool-hackers: 464] Avoid sending queries to unrelated nodes in streaming replication mode

Tatsuo Ishii ishii at postgresql.org
Sun Feb 23 21:44:42 JST 2014


Hi,

Pgpool-II sends certain queries, such as BEGIN, END and SET commands to
all of DB nodes. However in streaming replication mode, only primary
node and at most one standby node are only concerned (if primacy node
is selected as the load balance node, only 1 node is concerned). 

Think about 3 nodes cluster. Node 0 is primary, node 1 and 2 are
standby servers. Node 1 is selected as the load balance node. In this
case no query is needed to send to node 2, because DMLs are sent to
node 0 and SELECTs are sent to node 1.

Attached patch tries to reduce the wast of cycle by not sending queries
to node 2 in the case above, for example.

I have created a 3 node streaming replication cluster by using
pgpool_setup -n 3, ran pgbench and see how the number of BEGIN/END
reduces.

Master branch head:
[t-ishii at localhost aaa]$ pgbench -C -n -t 10 -c 10 -p 11003 test
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 100/100
tps = 507.673485 (including connections establishing)
tps = 1010.223461 (excluding connections establishing)
[t-ishii at localhost aaa]$ egrep 'BEGIN|END' log/pgpool.log |wc
    579    8127   51096

Number of BEGINs, ENDs are 579.

With attched patch:

[t-ishii at localhost aaa]$ pgbench -C -n -t 10 -c 10 -p 11003 test
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 100/100
tps = 532.529569 (including connections establishing)
tps = 1140.016872 (excluding connections establishing)
[t-ishii at localhost aaa]$ egrep 'BEGIN|END' log/pgpool.log |wc
    307    4321   26557

Number of BEGINs, ENDs are 307, that's 47% reduce.

If there's no objection, I will commit to all supported branches.

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: pool_quer_context.patch
Type: text/x-patch
Size: 1541 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20140223/0e3caae4/attachment.bin>


More information about the pgpool-hackers mailing list