Monitor Percona XtraDB Cluster on OpenBSD 7.5

Essential wsrep status variables

SHOW STATUS LIKE 'wsrep_%';

Key variables:

Variable	Healthy value	Description
`wsrep_cluster_size`	= number of nodes	Nodes in the cluster
`wsrep_cluster_status`	`Primary`	Partition status
`wsrep_connected`	`ON`	Connected to cluster
`wsrep_ready`	`ON`	Ready to accept queries
`wsrep_local_state_comment`	`Synced`	Node sync state
`wsrep_flow_control_paused`	< 0.1	Flow control ratio
`wsrep_cert_deps_distance`		Parallelism potential

Check replication lag

SHOW STATUS LIKE 'wsrep_local_recv_queue_avg';
-- > 0 means the node is falling behind

Prometheus + PMM (Percona Monitoring and Management)

Install the PMM client:

# RHEL / AlmaLinux:
dnf install -y pmm2-client
pmm-admin config --server-insecure-tls --server-url=https://admin:[email protected]

# Add MySQL service:
pmm-admin add mysql --username=pmm --password=PMMpass! --service-name=pxc-node1

Alert on cluster partition

Create a shell script /usr/local/bin/check_pxc.sh:

#!/bin/bash
STATUS=$(mysql -u monitor -pMonitorPass -e "SHOW STATUS LIKE 'wsrep_cluster_status';" \
    2>/dev/null | awk '/wsrep_cluster_status/{print $2}')
[ "$STATUS" != "Primary" ] && echo "ALERT: PXC cluster is NOT Primary: $STATUS" | \
    mail -s "PXC Alert" [email protected]

Run every 5 minutes via cron:

*/5 * * * * root /usr/local/bin/check_pxc.sh