[pgpool-general: 7631] Re: Pgpool replication delay

[Tatsuo Ishii]

>> Hi,
>> 
>> While load testing with concurrent users, we noticed that some read
>> operations reported the record not existing.
>> The load test creates and reads records.
>> When we manually query after load test, the record is in both master and
>> standby DBs.
>> Is it because of the replication delay and the read from standby fails?
> 
> Yes.
> 
>> Any recommendations for this kind of scenario?
> 
> synchronous_commit = remote_apply will help at the expense of
> performance.

Another option is, setting delay_threshold of pgpool.conf to lower
value. Default is 10000000 which allows large replication delay. So
you might want to set smaller value for it.

Note that observation of replication delay is done periodically,
defined by sr_check_period. This means that if replication delay grows
rapidly, the replication delay observed by pgpool could be outdated
and pgpool may send SELECT to delayed standby, and you might see the
error.

In summary delay_threshold is not perfect but can be useful if you
want to keep on using asynchronous replication.

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