How To Set Up MySQL Monitoring With PMM

Prepare your environment

Before you start setting up PostgreSQL to be connected to PMM monitoring, you need to have a PMM Client and register it on the PMM Server. Let's do it. You may set up the PMM Client in different ways. It can be either running in a Docker container or installed by a package manager or from sources. In this article, we will use the easiest way — we will install it from a repository using a package manager. Make sure you have curl, gnupb, gwet, and sudo installed on your server. First of all, you need to add Percona repo to your repositories list.

For Debian-based OS

a. Configure repositories:

wgethttps://repo.percona.com/apt/percona-release_latest.generic_all.debdpkg -i percona-release_latest.generic_all.deb

b. Install the pmm2-client package:

apt updateapt install -y pmm2-client

c. Check if it was installed correctly:

pmm-admin --version

For RedHat-based OS

a. Configure repositories:

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

b. Install the pmm2-client package:

yum install -y pmm2-client

c. Check if it was installed correctly:

pmm-admin --version

After you have installed the PMM Client, you should register it on your server. You need the PMM Server hostname or IP address and credentials for your connection.

To register the PMM client on the server, just type:

pmm-admin config --server-insecure-tls --server-url=https://admin:<password>@XXX.XXX.XXX.XXX:443 YYY.YYY.YYY.YYY generic mynode

where XXX.XXX.XXX.XXX — IP address or hostname of PMM server, YYY.YYY.YYY.YYY — IP address or hostname of PMM client, generic — client type and mynode — mane of client.

Configure MySQL

Create a database account for PMM. It is a good practice not to use a superuser to connect the PMM client to the monitored database instance:

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10;GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'localhost';

PMM can take query metrics from the slow query log or from Performance Schema. Pick one of them: the two overlap, and each adds a little load to the server. Percona's documentation compares them in detail. In short:

• Slow query log. You get more detail about each query, and on Percona Server for MySQL query sampling keeps the overhead low. The cost: PMM Client has to run on the database host or be able to read the log file, and the log keeps growing, so it needs rotation.
• Performance Schema. It is on by default in current MySQL versions, and PMM parses it faster. You get less detail about each query.

Percona recommends Performance Schema for MySQL 8.0+ and MariaDB 10.0+, and the slow query log for Percona Server for MySQL and Percona XtraDB Cluster 8.0+.

In this setup MySQL is newer than 5.6 and PMM connects to it over the network, so we use Performance Schema. Add these lines to my.cnf:

performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-statements-digest=ON
innodb_monitor_enable=all
query_response_time_stats=ON
userstat=ON

The last two lines don't work on every server. userstat exists in Percona Server for MySQL, Percona XtraDB Cluster and MariaDB. query_response_time_stats exists in Percona Server for MySQL 5.7 and MariaDB 10.0.4+, and needs a plugin. Oracle MySQL doesn't know either variable and won't start with them in my.cnf, so leave them out there.

Restart the mysql and create the PMM service:

pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass --service-name=MYSQL_NODE --host=XXX.XXX.XXX.XXX --port=3306

You may also add the service via the PMM Server user interface in Configuration -> PMM Inventory -> Add Instance menu section. Check if the service is registered successfully:

pmm-admin inventory list services --service-type=mysql

Success!

Congratulations, you have successfully set up MySQL monitoring with PPM. Now you have access to organized, secure, and robust database management systems. Monitoring and managing your data can help your business scale, grow, and flourish.

---

Need help with database monitoring and performance? Our DevOps consulting team sets up monitoring for MySQL, PostgreSQL and MongoDB. The first conversation is free.

Frequently Asked Questions

How do I set up MySQL monitoring with Percona PMM?

Install PMM Client via your package manager, register it with pmm-admin config pointing to your PMM Server, create a dedicated MySQL user (CREATE USER 'pmm'@'localhost' with SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN grants), then run pmm-admin add mysql --query-source=perfschema with your host and port to connect the instance.

Should I use slow query log or Performance Schema for MySQL metrics in PMM?

Percona recommends Performance Schema for MySQL 8.0+ and MariaDB 10.0+: PMM parses it faster, and PMM Client doesn't have to run on the database host. It recommends the slow query log for Percona Server for MySQL and Percona XtraDB Cluster, where the log gives more detail and query sampling keeps the overhead low. If PMM Client can't run on the database host, use Performance Schema.

What MySQL user permissions does PMM require?

PMM requires a MySQL user with SELECT, PROCESS, REPLICATION CLIENT, RELOAD, and BACKUP_ADMIN privileges on all databases (*.*). Limit the account to 10 simultaneous connections with MAX_USER_CONNECTIONS 10. Do not use a root or superuser account — a dedicated pmm user with only these privileges is the correct approach.

How do I enable Performance Schema for PMM in MySQL?

Add these lines to my.cnf: performance_schema=ON, performance-schema-instrument='statement/%=ON', performance-schema-consumer-statements-digest=ON and innodb_monitor_enable=all. On Percona Server for MySQL or MariaDB, also add userstat=ON. Restart MySQL, then register the service with pmm-admin add mysql --query-source=perfschema.

Related Reading

• How To Set Up MongoDB Monitoring With PMM
• How To Set Up PostgreSQL Monitoring With PMM
• How Do We Use PMM For DB Cluster Monitoring – Real-Life Use Case