[pgpool-general: 8520] Re: Best source for replication delay metrics?

[Tatsuo Ishii]

> Hi everyone,
> What is the best way to get the current replication delay to each backend?
> In particular I'd like to write a script that checks PGPool's understanding
> of the replication delay to each backend every ~5-10s and uploads that to
> Datadog, our infrastructure monitoring service; and I could use some advice
> on the best way to get that data.
> 
> The two places I've considered are:
> 
>    1. "replication_delay" shown from SHOW POOL_NODES
>       - but I often see 0 for the backends here and am not sure if that
>       means the delay is actually currently 0 or just unknown

I guess actually it's mostly 0 in your environment.

>    2. scraping the log lines produced with the log_standby_delay = 'always'
>    setting
>       - these are almost always greater than 0

If it's something like this:

2022-12-16 14:24:29.031: sr_check_worker pid 615821: LOG:  standby_delay: 54

it's not correct information because of Pgpool-II's bug. This is just
a debug info showing the number based on replay_lag column *
1000000 (the reason for multiplying 1000000 is, we want to show
replication delay with 6 decimal places precision in seconds if
delay_threshold_by_time > 0).

In short, you should ignore the log. I am going to downgrade the log
from LOG level to DEBUG1 level so that it does not show up unless
log_min_message is lower than DEBUG 1 or less.

What you should look at is something like this log lines:
2022-12-16 14:24:59.072: sr_check_worker pid 615821: LOG:  Replication of node: 1 is behind 456 bytes from the primary server (node: 0)

> , even when SHOW POOL_NODES says
>       0, so my guess is this is a more accurate source
>       - but I'd prefer something that I can query on-demand, rather than
>       having to scrape the logs

Yes. you'd better use show pool_nodes (or pcp_node_info).

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