Monitoring, troubleshooting, and query analytics for PostgreSQL on Kubernetes

If you are learning about databases and Kubernetes or running or migrating PostgreSQL to Kubernetes, I would like to show you a great open-source tool for database monitoring and troubleshooting.

I will discuss a tool to help you better understand your database, its parameters, and its health. You can access a Query Analytics tool to help you find slow queries. In addition, you will have dashboards to monitor the Kubernetes cluster itself.

In the previous article, I discussed the pgAdmin and PostgreSQL cluster created using Percona Everest. Today, I installed Percona Monitoring and Management (PMM) in my cluster, made some test queries to the database using pgAdmin, and explored the dashboards.

PMM is a free, open-source database monitoring tool for MySQL, PostgreSQL, and MongoDB. PMM has configured Grafana dashboards to monitor various PostgreSQL metrics:

• Connections, Tuples, Transactions
• Checkpoints, Buffers, and WAL usage
• Blocks, Conflicts and Locks
• Disk Cache and Memory Size
• CPU, RAM, Disk IO
• Vacuum monitoring and more

You need to install it (I'll show it below)

1. PMM Server, which includes dashboards and collects metrics from your databases.
2. PMM Client for each of your databases that sends database metrics to PMM Server. You need to configure pg_stat_monitor or pg_stat_statements extensions for PostgreSQL. If you use Percona Operator for PostgreSQL or Percona Everest, PMM is already integrated and enabled in the settings.

Let's get to the installation.

Installing PMM in a Kubernetes cluster.

You can install the PMM server on any server or cluster; I use the same cluster where the database is installed.

The documentation offers many installation methods, such as using Docker, Podman, AWS, or HELM.

I used the installation with HELM and the instructions from the official documentation.

1. Create a separate namespace or use an existing one. I create a separate one

kubectl create namespace monitoring

1. I have installed HELM and the Percona repositories as per the documentation, and now install PMM using the commands:

helm repo add percona https://percona.github.io/percona-helm-charts/

helm install pmm -n monitoring \
--set service.type="ClusterIP" \
--set pmmResources.limits.memory="4Gi" \
--set pmmResources.limits.cpu="2" \
percona/pmm

I added parameters with resource limits for PMM since my test cluster has limited resources.

The installation is quick, and I have the next steps.

1. We need to get the administrator password created during installation. (I just took that command from the last step.)

kubectl get secret pmm-secret -n monitoring -o jsonpath='{.data.PMM_ADMIN_PASSWORD}' | base64 --decode

1. Let's do a port-forward for Pod with pmm to an available port on our laptop to open PMM in the browser. (I used 8081 because 8080 is used for Percona Everest, which manages the database.)

kubectl -n monitoring port-forward pmm-0 8081:80

1. Opened PMM in a browser and used the password to log in.

Connecting the database to the PMM server

Now that we have the PMM itself, we need to make our Postgres database pass metrics to it. I created the cluster using Percona Everest; however, you can connect any PostgreSQL cluster to PMM.

1. If you are not using Percona's Postgres, please refer to the documentation on installing the PMM Client and Postgres extensions (pg_stat_statements or pg_stat_monitor).

2. If you are using Percona Distribution for PostgreSQL, Percona Operator for PostgreSQL, or Percona Everest, then the necessary extensions are already installed. I will explain how to enable monitoring below.

Postgres database created using Percona Operator for PostgreSQL

The setup process is described in sufficient detail in the documentation, if briefly:

1. You need to create an API key in the PMM settings.
2. Specify the API key as the PMM_SERVER_KEY value in the deploy/secrets.yaml secrets file. Using the deploy/secrets.yaml file, create the Secrets object.
3. Update the pmm section in the deploy/cr.yaml file.

  pmm:
    enabled: true
    image: percona/pmm-client:2.42.0
    secret: cluster1-pmm-secret
    serverHost: monitoring-service

Apply the changes, and you will see the databases in PMM.

PostgreSQL cluster created using Percona Everest

That's my way.

1. We need to get the IP address of the PMM in the Kubernetes cluster.

kubectl get svc -n monitoring

1. Now, in the Percona Everest settings, let's add a new Monitoring Endpoint using the IP address, user, and password from PMM.

1. Let's edit the database and enable monitoring in the created endpoint.

Done; now we will see the metrics in PMM.

Testing how it works.

1. Open pgAdmin and make some complex queries.

I found a SQL query that generates random data in rows.

INSERT INTO demo.LIBRARY(id, name, short_description, author,
                              description,content, last_updated, created)
SELECT id, 'name', md5(random()::text), 'name2'
      ,md5(random()::text),md5(random()::text)
      ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100)
      ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100 + 100)
FROM generate_series(1,1000) id;

And made several million rows by changing the value of generate_series(1,1000)

I've also done various SELECT queries.

1. After that, I went to look at the dashboards, which immediately showed that I had a problem. I got a list of slow queries and a spike in the graph. I created a test table without indexes, and queries were already processing slowly on many rows. I did this purposely to see the result in the monitoring tool.

I also found a dashboard that shows the cluster resource utilization for each Pod, such as CPU and RAM.

Conclusion

PMM has various dashboards for monitoring PostgreSQL. I won't show you all of them, but I recommend installing and monitoring your database, especially if you are not using database monitoring tools.