Feature: reduce sync messages

Currently pgpool forwards sync messages to all configure backend nodes
regardless the backend_weight or load_balance_mode. This is not only
a waste of CPU cycle, but degrades performance since it takes more
message round trip time as the number of backend nodes increases. This
is conspicuous if backend nodes are in a distant location. We should
send sync messages only to necessary backend nodes.

I have created a patch to enhance this. Send sync message only to
necessary backends.

Suppose there is a pgpool cluster with 3 backend nodes. Before patch:

test=# select 1 \bind \g
NOTICE: DB node id: 0 statement: Parse: select 1
NOTICE: DB node id: 0 statement: Bind: select 1
NOTICE: DB node id: 0 statement: D message
NOTICE: DB node id: 0 statement: Execute: select 1
NOTICE: DB node id: 0 statement: Sync
NOTICE: DB node id: 1 statement: Sync
NOTICE: DB node id: 2 statement: Sync
?column?
----------
1
(1 row)

As you can see, the sync messages are sent to backend node 0, 1 and 2,
although only node 0 is involved in the query "select 1". So sending
sync messages to node 1 and 2 are just waste of time.

After patch:

test=# select 1 \bind \g
NOTICE: DB node id: 2 statement: Parse: select 1
NOTICE: DB node id: 2 statement: Bind: select 1
NOTICE: DB node id: 2 statement: D message
NOTICE: DB node id: 2 statement: Execute: select 1
NOTICE: DB node id: 2 statement: Sync
?column?
----------
1
(1 row)

The sync message is only sent to node 2.

Now the implementation details.

The idea is, if pgpool does not send any query message to a backend
until now (more precisely since the last ReadyForQuery message), we
don't need to send a sync message to the backend node.

To decide which node we need to send the sync message, add a new
struct member "sync_map" to the session context. A sync_map is an
array of bool. Each array member corresponds to each backend
node. When pgpool forwards a message to backend, corresponding
sync_map member is set to true.

When a sync message is received from frontend, a sync pending message
is added as we already do. The difference is, previously no query
context is added to the sync pending message. Now we add a query
context with the sync map translated into where_to_send map in the
query context. Then we send a sync message to backend, consulting the
where_to_send map. This way, we don't need to send a sync message to
backend node which pgpool has never sent a query since the last
ReadyForQuery message.

When a Ready for query message arrives, we decide which backend to
read according to the where_to_send map, which is different from what
we do today: read from all available backend nodes. After receiving
the ReadyForQuery message, sync_map members are all set to false. Also
the sync message query context is destroyed at this timing.

There are a few other edge cases:

(1) When an ErrorResponse is received,
pool_discard_except_sync_and_ready_for_query() is called to remove any
pending messages (and backend buffer data) except sync pending message
and ReadyForQuery message. If the sync message is not found in the
queue and receives a sync message from frontend, add a new sync
pending message, consulting the sync_map as described above and
forward the sync message to backends.

(2) If no query is sent and a sync message is received, the sync_map
is all false. This doesn't make sense since there's no point to send a
sync message, but the protocol does not prohibit this. In this case we
set sync_map all true, which means we send sync messages to all
backend. This may not be the best way in terms of performance, but
this makes things simpler (and compatible what we are doing today).

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