configure PMM for Percona MYSQL

Percona Monitoring and Management (PMM) is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

PMM is a free and open-source solution that you can run in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.

PMM, at a high-level, is made up of two basic components: the client and the server. The PMM Client is installed on the database servers themselves and is used to collect metrics. The client contains technology specific exporters (which collect and export data), and an “admin interface” (which makes the management of the PMM platform very simple). The PMM server is a “pre-integrated unit” (Docker, VM or AWS AMI) that contains four components that gather the metrics from the exporters on the PMM client(s). The PMM server contains Consul, Grafana, Prometheus and a Query Analytics Engine that Percona has developed. Here is a graphic from the architecture section of our documentation. In order to keep this post to a manageable length.

In this post i will setup the PMM on docker.

Pulling the PMM Server Docker Image

docker pull percona/pmm-server:2

Create a persistent data container.

docker create --volume /srv \
--name pmm-data percona/pmm-server:2 /bin/true

Run the image to start PMM Server.

docker run --detach --restart always \ --publish 80:80 --publish 443:443 \ --volumes-from pmm-data --name pmm-server \ percona/pmm-server:2

Once you completed the server configuration, you have to install the client on desired one, for example in our case we want to install it to monitor MySQL so we will go to install PMM Client on MySQL server.

To install the PMM client package, follow these steps.

Configure Percona repositories using the percona-release tool

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb

Note

If you have previously enabled the experimental or testing Percona repository, don’t forget to disable them and enable the release component of the original repository as follows:

sudo percona-release disable all
sudo percona-release enable original release

Install the PMM client package:

sudo apt-get update
sudo apt-get install pmm2-client

Register your Node:

Before doing this, there are some MySQL requirements should be done from Database side.

Enable Logs in MySQL follow these step

SET GLOBAL slow_query_log_file = '/path/to/slow_query.log';

Determine what makes a query “slow”, by setting the limit (in seconds) after which a query is logged to the slow query log. The example below logs every query that exceeds 10 seconds in duration

mysql> SET GLOBAL long_query_time = 10;

Now enable the Slow Query log.

mysql> SET GLOBAL slow_query_log = 'ON';
mysql> FLUSH LOGS;

If you want to make these changes persistent, modify the my.cnf and add these lines to the [mysqld] part of the config.

[mysqld]
...
slow_query_log = /path/to/slow_query.log
long_query_time = 10
log_queries_not_using_indexes = ON

Verify

mysql> SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';

Once you are done you have to create username/password for PMM,this user should have necessary privileges for collecting data. If the pmm user already exists, you can grant the required privileges as follows:

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10;

GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'localhost';

Once you done, register your node.

pmm-admin config --server-insecure-tls --server-url=https://admin:admin@<IP Address>:443

You have to wait for couple of minutes till it will be sync.

You should see the following output:

Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.

Regards

Osama

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.