In this article, we will introduce a tool called "Percona Monitoring and Management" which is an open-source database monitoring, management, and observability solution for popular databases available in the market such as MySQL, PostgreSQL, and MongoDB. The official website below shows the significant functionalities of PMM (Percona Monitoring and Management).

• Allows users to observe the health of their database systems
• Explore patterns in their behavior, troubleshoot, and perform database management tasks anytime.
• PMM collects performance metrics from databases and other supported services, then show visualized data in the web UI dashboard

We have to do database monitoring to find out database issues in time, which can help the application/system remain healthy and accessible. Database administrators can choose to monitor databases manually, but this choice is a time-consuming and inefficient approach. By choosing a tool such as Percona we believe it will help the productivity and efficiency of your monitoring task.

In our experiment, we have set up Percona Monitoring and Management Server using docker and monitor 3 services PostgreSQL, PgBouncer, and HAProxy as shown in the below sample database environment architecture.

Prerequisite

• Docker installed on database cluster (Master, and 2 Standby) which is used to expose statistics or matrics of Pgbouncer, the pooling of each database cluster.
• Docker installed on monitoring host act as PMM server.

1. INSTALL PERCONA MONITORING AND MANAGEMENT SERVER (PMM SERVER)

Use this link for the easy install script method provided by the Percona team or you can try another setup method based on your environment. For us, we are using the following command to get it done.

#get percona images from docker hub
docker pull registry.hub.docker.com/percona/pmm-server:2
wget https://www.percona.com/get/pmm

. pmm -r registry.hub.docker.com/percona/pmm-server -p 8443

Gathering/downloading required components, this may take a moment

Checking docker installation - installed.

Starting PMM server...
Created PMM Data Volume: pmm-data
Created PMM Server: pmm-server
        Use the following command if you ever need to update your container by hand:
        docker run -d -p 8443:443 --volumes-from pmm-data --name pmm-server --restart always registry.hub.docker.com/percona/pmm-server:2

PMM Server has been successfully setup on this system!

You can access your new server using one of the following web addresses:
        https://172.17.0.1:8443/
        https://server_ip_address:8443/
        https://127.0.0.1:8443/

The default username is 'admin' and the password is 'admin' :)
Note: Some browsers may not trust the default SSL certificate when you first open one of the urls above.
If this is the case, Chrome users may want to type 'thisisunsafe' to bypass the warning.

Enjoy Percona Monitoring and Management!

Once done, you can use the provided link and the custom port to log in and change the default password. ENJOY!

2. ADD POSTGRESQL SERVICE FOR MONITORING

As shown in the sample database environment, we have 3 database servers running as a cluster. Let's begin registering all the 3 PostgreSQL servers for PMM Server to monitor them.

MASTER DB

#Adding percona repolist into host running master postgresql server
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
#Installing pmm client running as agent
sudo yum install pmm2-client
#Configuring pmm client running as agent with the pmm server 
sudo pmm-admin config --server-insecure-tls --server-url=https://username:password@server_ip_address:8443

#Create user monitoring on master db
CREATE USER pmm WITH SUPERUSER ENCRYPTED PASSWORD 'monitoring_usr_pass'; --(create on master)

#Modify pg_hba.conf for monitoring user
host   	all             pmm         server_ip_address/32       md5
# TYPE  DATABASE        USER        ADDRESS                METHOD

#Modify postgresql.conf for extension pg_stat_statements
shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 3072 # Increase tracked query string size
pg_stat_statements.track = all   # Track all statements including nested
track_io_timing = on             # Capture read/write stats
#Next, you must restart PostgreSQL

#Now let's install pg_stat_statements extension
psql postgres postgres -c "CREATE EXTENSION pg_stat_statements SCHEMA public" --(create on master)

#Registering PostgreSQL service for pmm server to monitor
pmm-admin add postgresql --host=server_ip_address --port=db_port --username='pmm' --password='monitoring_usr_pass' --service-name=service_name_in_pmm --server-insecure-tls --server-url=https://username:password@server_ip_address:8443
PostgreSQL Service added.
Service ID  : /service_id/eb52642c-323f-454d-a0f0-5c343f51e56b
Service name: service_name_in_pmm

STANDBY DB

#Installing pmm client manually by download rpm file
rpm -ivh pmm2-client-2.28.0-6.el7.x86_64.rpm
#Configuring pmm client running as agent with the pmm server
sudo pmm-admin config --server-insecure-tls --server-url=https://username:password@server_ip_address:8443

#Modify pg_hba.conf for monitoring user
host   	all             pmm         server_ip_address/32       md5
# TYPE  DATABASE        USER        ADDRESS                METHOD
#Modify postgresql.conf for extension pg_stat_statements
shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 3072 # Increase tracked query string size
pg_stat_statements.track = all   # Track all statements including nested
track_io_timing = on             # Capture read/write stats
#Next, you must restart PostgreSQL

#Registering PostgreSQL service for pmm server to monitor
pmm-admin add postgresql --host=server_ip_address --port=db_port --username='pmm' --password='monitoring_usr_pass' --service-name=service_name_in_pmm --server-insecure-tls --server-url=https://username:password@server_ip_address:8443
PostgreSQL Service added.
Service ID  : /service_id/4f1946b1-88af-4760-8530-5b189df2e349
Service name: service_name_in_pmm

3. ADD HAPROXY SERVICE FOR MONITORING

In our sample database environment, we use this article link to install HAProxy 2.5.5 from the source code. Use the following command to check if the HAProxy is installed with the Prometheus exporter enabled.

haproxy -vv
#Check to see this line of statement do exists
Built with the Prometheus exporter as a service

If you can't see this line of the statement, let's begin this setup to enable

#Stop haproxy service which is currently running
sudo systemctl stop haproxy
#Build haproxy
make -j $(nproc) TARGET=linux-glibc USE_ZLIB=1 USE_PCRE=1 USE_SYSTEMD=1 USE_PROMEX=1
#Install haproxy
make install
#Copy/Replace haproxy command
cp /usr/local/sbin/haproxy /usr/sbin/haproxy

#Change haproxy config file under section listen stats
listen stats
    http-request use-service prometheus-exporter if { path /metrics }
#Start haproxy service
sudo systemctl start haproxy
#Using below command to check again
haproxy -vv
Built with the Prometheus exporter as a service

#Next check the exposed haproxy statistic using web browser	
HTTP://haproxy_ip_address:haproxy_stat_port/metrics (--To check the statistic of haproxy)

Next, we can install the PMM client agent and then add the HAProxy service to monitor

#Installing pmm client manually by download rpm file
rpm -ivh pmm2-client-2.28.0-6.el7.x86_64.rpm
#Configuring pmm client running as agent with the pmm server
sudo pmm-admin config --server-insecure-tls --server-url=https://username:password@server_ip_address:8443

#Registering HAProxy service for pmm server to monitor
pmm-admin add haproxy --listen-port=haproxy_state_port
HAProxy Service added.
Service ID  : /service_id/e663c922-54f5-4521-a19c-2f16cc38b8cf
Service name: haproxy-service-name

4. ADD PGBOUNCER SERVICE FOR MONITORING

Since Percona is not supporting PgBouncer as a pre-defined service, we will register PgBouncer as an external service to the PMM server for monitoring by using docker "prometheus-pgbouncer-exporter" running as a service to expose PgBouncer statistics. Follow the steps below to get it done.

#Get image of prometheus-pgbouncer-exporter
docker pull spreaker/prometheus-pgbouncer-exporter

#Need to allow hba_bouncer.conf for ip address generated by docker (Ex. 172.17.0.2) if you use pgbouncer with tightening security
#Mapping port 9100 with port 9127 in docker

docker run -p 127.0.0.1:9100:9127/tcp --name prometheus-pgbouncer-exporter --restart always \
   --env PGBOUNCER_HOST=server_ip_address \
   --env PGBOUNCER_PORT=bouncer_port \
   --env PGBOUNCER_USER=bouncer_user \
   --env PGBOUNCER_PASS=bouncer_pass \
   --env PGBOUNCER_EXPORTER_HOST=172.17.0.2 \
   --env PGBOUNCER_EXPORTER_PORT=9127 \
   docker.io/spreaker/prometheus-pgbouncer-exporter:latest


#Additional configuration for container: prometheus-pgbouncer-exporter
docker exec -it prometheus-pgbouncer-exporter vi /etc/pgbouncer-exporter/config.yml

#Edit following below format for file /etc/pgbouncer-exporter/config.yml

# The host on which the exporter should listen to (defaults to 127.0.0.1)
exporter_host: $(PGBOUNCER_EXPORTER_HOST)

# The port on which the exporter should listen to (defaults to 9127)
exporter_port: $(PGBOUNCER_EXPORTER_PORT)

# The list of pgbouncer instances to monitor
pgbouncers:
  -
    # The pgbouncer connection string. Supports environment variables replacement
    # Ie. $(PGBOUNCER_PASS) is replaced with the content of "PGBOUNCER_PASS" environment
    #     variable if exist, or left untouched if doesn''t exist
    dsn: postgresql://$(PGBOUNCER_USER):$(PGBOUNCER_PASS)@$(PGBOUNCER_HOST):$(PGBOUNCER_PORT)/pgbouncer

    # The pgbouncer connection timeout in seconds (defaults to 5 seconds)
    connect_timeout: 5
    include_databases:
    exclude_databases:
      - pgbouncer
    extra_labels:
      node_ip: server_ip_address

Next, after modifying a config file /etc/pgbouncer-exporter/config.yml for additional parameter settings, let's continue the step below

#Restart the docker after modifying the config
docker stop prometheus-pgbouncer-exporter
docker start prometheus-pgbouncer-exporter

#Testing the pgbouncer service is it up properly (It will show as below if it is properly config)
curl 172.17.0.2:9127/metrics | grep pgbouncer_up
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 97435  100 97435    0     0  3154k      0 --:--:-- --:--:-- --:--:-- 3281k
# HELP pgbouncer_up PgBouncer is UP and the scraping of all metrics succeeded
# TYPE pgbouncer_up gauge
pgbouncer_up{node_ip="server_ip_address"} 1.0

Finally, we can now register PgBouncer as an external service to the PMM server. In our experiment, we use PgBouncer running on the same server as PostgreSQL, therefore, we don't have to install the PMM client agent again. However, if you install PgBouncer in a separate server from PostgreSQL, you have to install the PMM client agent.

pmm-admin add external --service-name=pgbouncer_service_name --listen-port=9100 --metrics-path=/metrics --scheme=http --server-insecure-tls --server-url=https://username:password@pmm-server-ip-address:8443
External Service added.
Service ID  : /service_id/1be419ac-ded1-4304-9cfb-e07995bb700c
Service name: pgbouncer_service_name
Group       : external

Once you have done this, now you have 3 services PostgreSQL, PgBouncer, and HAProxy monitoring by Percona Monitoring Management Tool. ENJOY YOUR TIME.

THANK YOU, Find Us @
Facebook
Telegram
Twitter
LinkedIn