Re: 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).

I have just pushed the patch with slight modifications (change
copyright year).

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