SHOW_POOL_HEALTH_CHECK_STATS - show health check statistics data
SHOW POOL_HEALTH_CHECK_STATS
SHOW POOL_HEALTH_CHECK_STATS displays health check (see Section 5.8,
“Health Check”, in the documentation) statistic data mostly
collected by health check process. This command helps Pgpool-II admin to study
events related to health check. For example, admin can easily locate the
failover event in the log file by looking at
"last_failed_health_check" column. Another example is finding
unstable connection to backend by evaluating "average_retry_count"
column. If particular node shows higher retry count than other node, there may
be problem to the connection to the backend.
Table 25, “Statistics data shown by pool_health_check_stats
command” shows each column name and its description.
Table 25. Statistics data shown by pool_health_check_stats
command
| Column Name |
Description |
| node_id |
Backend node id. |
| hostname |
Backend hostname or UNIX domain socket path. |
| port |
Backend port number. |
| status |
Backend status. One of up, down, waiting, unused or quarantine. |
| role |
Role of the node. Either primary or standby in streaming replication
mode. Either main or replica in other mode. |
| last_status_change |
Timestamp of last backend status changed. |
| total_count |
Number of health check count in total. |
| success_count |
Number of successful health check count in total. |
| fail_count |
Number of failed health check count in total. |
| skip_count |
Number of skipped health check count in total. If the node is already
down, health check skips the node. |
| retry_count |
Number of retried health check count in total. |
| average_retry_count |
Number of average retried health check count in a health check
session. |
| max_retry_count |
Number of maximum retried health check count in a health check
session. |
| max_duration |
Maximum health check duration in Millie seconds. If a health check
session retries, the health check duration is sum of each retried health
check. |
| min_duration |
Minimum health check duration in Millie seconds. If a health check
session retries, the health check duration is sum of each retried health
check. |
| average_duration |
Average health check duration in Millie seconds. If a health check
session retries, the health check duration is sum of each retried health
check. |
| last_health_check |
Timestamp of last health check. If heath check does not performed yet,
empty string. |
| last_successful_health_check |
Timestamp of last successful health check. If heath check does not
succeeds yet, empty string. |
| last_skip_health_check |
Timestamp of last skipped health check. If heath check is not skipped
yet, empty string. Note that it is possible that this field is an empty
string even if the status is down. In this case failover was triggered by
other than health check process. |
| last_failed_health_check |
Timestamp of last failed health check. If heath check does not fail yet,
empty string. Note that it is possible that this field is an empty string
even if the status is down. In this case failover was triggered by other
than health check process. |
Here is an example session:
test=# show pool_health_check_stats;
-[ RECORD 1 ]----------------+--------------------
node_id | 0
hostname | /tmp
port | 11002
status | up
role | primary
last_status_change | 2020-01-26 19:08:45
total_count | 27
success_count | 27
fail_count | 0
skip_count | 0
retry_count | 0
average_retry_count | 0.000000
max_retry_count | 0
max_duration | 9
min_duration | 2
average_duration | 6.296296
last_health_check | 2020-01-26 19:12:45
last_successful_health_check | 2020-01-26 19:12:45
last_skip_health_check |
last_failed_health_check |
-[ RECORD 2 ]----------------+--------------------
node_id | 1
hostname | /tmp
port | 11003
status | down
role | standby
last_status_change | 2020-01-26 19:11:48
total_count | 19
success_count | 12
fail_count | 1
skip_count | 6
retry_count | 3
average_retry_count | 0.230769
max_retry_count | 3
max_duration | 83003
min_duration | 0
average_duration | 6390.307692
last_health_check | 2020-01-26 19:12:48
last_successful_health_check | 2020-01-26 19:10:15
last_skip_health_check | 2020-01-26 19:12:48
last_failed_health_check | 2020-01-26 19:11:48