Setting up open-source DB monitoring using PMM
There are plenty of proprietary DB monitoring services, but did you know there is a great open-source alternative – Percona Monitoring and Management (PMM)? PMM provides monitoring, alerts, and query analysis for MySQL, PostgreSQL, MongoDB, and others. The package is open source, has a large community, and can be set up in minutes.
What do you get once the Percona PMM is set up?
There are 3 main features of Percona Monitoring and Management: monitoring, alerts, and query analytics.
Once you complete the PMM setup, you already have plenty of preset Grafana-based dashboards. These dashboards display the info about:
- Your host machine
- CPU load
- RAM usage
- Network usage
- Disk usage
- Disk space forecast
- Your database
- Queries Per Second
These default dashboards are already customisable dashboards that help you keep an eye on the infrastructure.
Real-time alerts notify you if something goes wrong.
PMM helps you with troubleshooting by providing data from a high-level overview to query-level analytics.
5 Steps To Set Up DB Monitoring With PMM
The PMM consists of 2 parts:
- Client (Agent)
PMM Agent should be installed on your database host machine. It will be collecting telemetry data about the host machine and database(s) and sending it to the PMM Server.
Unlike most cloud-based monitoring services, the PMM Server is a part you should also care about. It will store and process all the telemetry data received from agents, and provide UI for analysis.
There are 5 steps to set up PMM for DB monitoring:
- Set up a PMM Server
- Set up a PMM Agent
- Connect the Agent to the Server
- Connect DB to the Agent
- Customise Dashboards and Alerts
Let’s look into the details of each step.
1. Set up a PMM Server
You can get a fully managed PMM Server at hostedpmm.com in several clicks or deploy it by yourself.
If you want to host a PMM server by yourself, you need a VPS that meets PMM Server requirements. It will be processing all the telemetry data from your infrastructure, so it needs up to 2 GB per monitored DB. The PMM Server will also be storing all the telemetry data for some time. It requires around 1 GB of storage per monitored database per week.
Once you have a VPS, you can install PMM Server. For example, you can use an easy-install script by running
wget -O - <https://www.percona.com/get/pmm> | /bin/bash
- Installs Docker if it is not already installed on your system.
- Stops and backs up any PMM Server Docker containers that are currently running.
- Pulls and runs the latest PMM Server Docker image.
After the deployment, you need to properly set up your network. If everything is correct you can access the PMM Server web interface.
Use admin/admin to log in.
As you can see, it already shows the info about the PMM Server host machine and its DB. Your PMM server is set up and works properly.
2. Set up a PMM Agent
You need ssh access to your DB host machine.
There are several ways to set up a PMM server:
- Package manager
- Binary package
Not everyone wants to set up docker to their DB host machine just for monitoring. The binary package is hard to update compared to the package manager way to install. So we advise using package manager installation.
To do this you need to run these commands:
wget <https://repo.percona.com/apt/percona-release_latest.generic_all.deb> dpkg -i percona-release_latest.generic_all.deb apt update apt install -y pmm2-client
yum install -y <https://repo.percona.com/yum/percona-release-latest.noarch.rpm> yum install -y pmm2-client
In most cases, there should not be any problems. At this point, the PMM Agent is set up and we need to connect it to the PMM Server.
3. Connect the Agent to the Server
Run one command:
pmm-admin config --server-insecure-tls --server-url=https://admin:admin@X.X.X.X:443
The better way to authorise the agent is to create a token via UI and run this command instead:
pmm-admin config --server-insecure-tls --server-url=https://api_key:token@X.X.X.X:443
An option –server-insecure-tls should be used if you haven’t installed SSL certificates to the PMM Server.
💡 This process might be tricky but is recommended for security reasons. You can avoid this routine by getting a fully managed PMM server at hostedpmm.com. There you can get SSL ready PMM server in a few clicks.
After connecting the PMM agent to the PMM Server, you are already able to see your host machine metrics.
But as you can see, there is N/A for all the DB-related metrics. So we need to connect DB to the agent.
4. Connect DB to the Agent
It is good practice to create a dedicated non-superuser account to connect the database instance to PMM Agent. E.g. user with name
pass, and the necessary permissions.
Connecting your DB to the PMM Agent depends on your DB. Basically, you need to run a command in DB. For example, MySQL commands look like this:
CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10; GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'localhost';
Then you should add a service using PMM Server UI
After that, you should be able to see DB metrics in your PMM Server web UI.
The basic DB monitoring setup is done! We can improve it by customising PMM for your needs.
5. Customise Dashboards and Alerts
There are a lot of preset dashboards but you can customize them for your tasks and workflows.
E.g. if you want an alert for some condition, you can easily set it up.
That’s it! 🎉
Now you have great monitoring for your infrastructure setup. You can keep an eye on a load, check drive usage forecast, and scale in advance. Get alerts if something goes wrong and increase the availability of your service. Use query analysis to find out which requests need to be optimised and improve your service responsiveness.The easiest way to get PMM Server for DB monitoring is HostedPMM.
The easiest way to get PMM Server for DB monitoring is HostedPMM.
Get a Hosted PMM Server and Receive a $200 Bonus
The easiest way to get a PMM Server is hostedpmm.com. It takes several clicks and a couple of minutes.
Everyone who signs up through December 31 will receive a $200 bonus credit.