Monitoring PostgreSQL Cluster via Pgpool-II with Prometheus

-

Database monitoring is important in production. Effective database monitoring helps you to visualize database activities and detect errors. Pgpool-II Exporter can expose Pgpool-II and PostgreSQL cluster's metrics which can be collected by Prometheus. In this post, I will describe how to use Pgpool-II Exporter to monitor Pgpool-II and PostgreSQL cluster's statistics and visualize them with Prometheus and Grafana.

What is Prometheus?

Prometheus is a leading cloud-native monitoring solution. Prometheus collects application metrics from monitored targets which expose Prometheus-formatted metrics. Prometheus metrics exporter provides the endpoint to scrape. The most popular one is node_exporter which collects system metrics such as CPU, memory and disk space usage for Linux servers.

Pgpool-II Exporter

Pgpool-II Exporter uses SHOW command to collect Pgpool-II and PostgreSQL Cluster's metrics, including:

name Description
pgpool2_frontend_total Number of total child processes
pgpool2_frontend_used Number of used child processes
pgpool2_pool_nodes_status Backend node Status (1 for up or waiting, 0 for down or unused)
pgpool2_pool_nodes_replication_delay Replication delay
pgpool2_pool_backend_stats_select/insert/update/delete/ddl/other_cnt SELECT/INSERT/UPDATE/DELETE/DDL/Other statement counts issued to each backend
pgpool2_pool_backend_stats_panic/fatal/error_cnt Panic/Fatal/Error message counts returned from backend
pgpool2_pool_cache_cache_hit_ratio Query cache hit ratio 
... ...

Building and running Pgpool-II Exporter

First, let's download the repository and build it.

$ git clone https://github.com/pgpool/pgpool2_exporter.git
$ cd pgpool2_exporter
$ make

Run Pgpool-II Exporter. Then you should see that Pgpool-II Exporter is now running and exposing metrics on port 9719:

$ export DATA_SOURCE_NAME="postgresql://user:password@hostname:port/dbname?sslmode=disable"
$ ./pgpool2_exporter
INFO[0000] Starting pgpool2_exporter (version=0.0.0-dev, branch=master, revision=6419beee395419c5cb15fa12910681b229ff515d) for po
stgresql://pengbo:postgres@127.0.0.1:11000/postgres?sslmode=disable  source="pgpool2_exporter.go:610"
INFO[0000] Listening on :9719                            source="pgpool2_exporter.go:611"

Metrics

Once Pgpool-II Exporter is running, you can verify the metrics that are being exported using curl command.

$ curl -s localhost:9719/metrics

You should see output like below:

# HELP go_gc_duration_seconds A summary of the pause duration of garbage collection cycles.
# TYPE go_gc_duration_seconds summary
go_gc_duration_seconds{quantile="0"} 6.121e-06
go_gc_duration_seconds{quantile="0.25"} 9.33e-06
go_gc_duration_seconds{quantile="0.5"} 1.2386e-05
go_gc_duration_seconds{quantile="0.75"} 1.4918e-05
go_gc_duration_seconds{quantile="1"} 4.5425e-05
go_gc_duration_seconds_sum 0.000265276
go_gc_duration_seconds_count 19
# HELP go_goroutines Number of goroutines that currently exist.
# TYPE go_goroutines gauge
go_goroutines 10
# HELP go_info Information about the Go environment.
# TYPE go_info gauge

Pgpool-II Exporter is now exposing metrics that Prometheus can scrape. Pgpool-II metrics are prefixed with pgpool2_. To view those metrics: 

$ curl -s localhost:9719/metrics | grep pgpool2
# HELP pgpool2_frontend_total Number of total child processed
# TYPE pgpool2_frontend_total gauge
pgpool2_frontend_total 32
# HELP pgpool2_frontend_used Number of used child processes
# TYPE pgpool2_frontend_used gauge
pgpool2_frontend_used 1
# HELP pgpool2_last_scrape_duration_seconds Duration of the last scrape of metrics from Pgpool-II.
# TYPE pgpool2_last_scrape_duration_seconds gauge
pgpool2_last_scrape_duration_seconds 0.00194464
# HELP pgpool2_last_scrape_error Whether the last scrape of metrics from Pgpool-II resulted in an error (1 for error, 0 for success).
# TYPE pgpool2_last_scrape_error gauge
pgpool2_last_scrape_error 0
# HELP pgpool2_pool_backend_stats_ddl_cnt DDL statement counts issued to each backend
# TYPE pgpool2_pool_backend_stats_ddl_cnt gauge
pgpool2_pool_backend_stats_ddl_cnt{hostname="/tmp",port="11002",role="primary"} 24
pgpool2_pool_backend_stats_ddl_cnt{hostname="/tmp",port="11003",role="standby"} 0
# HELP pgpool2_pool_backend_stats_delete_cnt DELETE statement counts issued to each backend
# TYPE pgpool2_pool_backend_stats_delete_cnt gauge
pgpool2_pool_backend_stats_delete_cnt{hostname="/tmp",port="11002",role="primary"} 5
pgpool2_pool_backend_stats_delete_cnt{hostname="/tmp",port="11003",role="standby"} 0

Configuring  Prometheus and Grafana

This section describes how to configure Prometheus and Grafana. If you have not installed Prometheus Grafana and Prometheus, please visit their documentation for installation.

Edit configuration file prometheus.yml to add the target to scrape.

scrape_configs:
  - job_name: 'pgpool'
    static_configs:
    - targets: ['localhost:9719'] 

Start Prometheus and Grafana.

# ./prometheus --config.file=./prometheus.yml
# systemctl start grafana-server

After creating a Prometheus data source to Grafana you can add the following graphs.

Conclusion

In the post I described how to use Pgpool-II Exporter in a local environment. I hope it helps to monitor your PostgreSQL cluster and Pgpool-II's statistics. In future blogs, I will describe how to install and use Pgpool-II Exporter in Docker and Kubernetes.

Comments

Post a Comment

Popular posts from this blog

Installing Pgpool-II on Debian/Ubuntu

-
First of all, many thanks to the PostgreSQL Global Development Group (PGDG) for creating Pgpool-II packages for Debian and Ubuntu. This post shows you step by step how to install Pgpool-II using official APT repository provided by PGDG. Prerequisites This blog assumes you have already installed two PostgreSQL 14 servers and setup streaming replication between the PostgreSQL servers.   For testing purpose,we set trust authentication in pg_hba.conf . In a production environment, Please follow the PostgreSQL and Pgpool-II documentation to configure proper authentication settings.  Install Pgpool-II To use the PostgreSQL apt repository, follow the steps below. Create the repository configuration file: # echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list Import the repository signing key: # wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Update the package lists: #
Read more

Authentication in Pgpool-II

-
Image
    Pgpool-II is a proxy software for PostgreSQL cluster and it supports several authentication methods. How does Pgpool-II perform user authentication? In this post, I will introduce the authentication methods which Pgpool-II supports and how Pgpool-II authentication mechanism works. Authentication methods in Pgpool-II Pgpool-II supports several authentication methods: Trust Authentication MD5 Password Authentication   scram-sha-256 Authentication (Pgpool-II 4.0 or later) Certificate Authentication   PAM Authentication LDAP Authentication (from the next major release 4.2)  Starting with Pgpool-II 4.0, Pgpool-II supports scram-sha-256 authentication. scram-sha-256 authentication method is strongly recommended because it is the most secure password-based authentication method. How does Pgpool-II authentication mechanism work? Since Pgpool-II is a PostgreSQL proxy that works between clients and PostgreSQL servers, the authentication comprises two steps: Authentication between client
Read more

Query Load Balancing in Pgpool-II

-
Image
Pgpool-II is a PostgreSQL cluster management tool. The major features of Pgpool-II are: Connection pooling Query load balancing Automated failover Watchdog (High availability of Pgpool-II) Replication In memory query cache In this post, I will describe the query load balancing mechanism in Pgpool-II and the relevant configuration parameters. Why use query load balancing? Query load balancing can distribute database server workloads across multiple PostgreSQL servers. Nowadays, most database systems use multiple replicated database servers to achieve high availability for PostgreSQL. Pgpool-II takes the advantage of the replication feature in order to distribute the workloads across multiple PostgreSQL servers. If there is a PostgreSQL cluster with multiple PostgreSQL servers, Pgpool-II is able to distribute READ queries across those PostgreSQL servers. The benefit of READ queries load balancing is, improve the system's throughput reduce the load on each PostgreSQL server optimize
Read more