[pgpool-general: 8999] Re: Massive performance and latency issues when remote replica is attached to cluster.

[Tatsuo Ishii]

> Any recommendations would be greatly appreciated. So far, after making
> multiple tuning and settings changes there have been no meaningful changes.
> 
> What we are highly suspicious of is that the latency numbers seem to go
> from around .2 ms to around 8 ms. This is very close to the round-trip-time
> between our datacenters.

> statement latencies in milliseconds:
>          0.003  \set aid random(1, 100000 * :scale)
>          0.001  \set bid random(1, 1 * :scale)
>          0.001  \set tid random(1, 10 * :scale)
>          0.001  \set delta random(-5000, 5000)
>          8.844  BEGIN;
>          9.695  UPDATE pgbench_accounts SET abalance = abalance + :delta
> WHERE aid = :aid;
>          8.878  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
>        685.873  UPDATE pgbench_tellers SET tbalance = tbalance + :delta
> WHERE tid = :tid;
>         92.049  UPDATE pgbench_branches SET bbalance = bbalance + :delta
> WHERE bid = :bid;
>          8.826  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
> VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
>          8.847  END;

If BEGIN and END were sent to the remote standby node, then the
latency number makes sense. But theoretically it should not happen
because you turn off load balance mode. In this case everything should
be sent to primary except the connection establishing. But even if
load balance mode is off, pgpool establish connections to both primary
and standbys. However the default option of pgbench is establishing
connection only once at the beginning of session, meaning the
connection overhead should only happen only once in the session.  Thus
I can not think of plausible reason for the latency numbers.

Anyway, can you turn on log_per_node_statement and see everything is
sent to standby? I want to confirm that first.

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