PMM is the monitoring and management tools offers by Percona. It uses the power of hosted graphite to let you monitor metrics and Orchestrator to manage the replica chain and see what is the current status of the infrastructure. Percona-guide
Setup the Pmm server
Run-server The server can be created on a Docker service, as a virtual appliance or on a EC2 server using the AMI image provided by Percona.
The simplest way to create a PMM Server is using the AMI image. It doesn't need us to learn something new. We have already the right knowledge to create the server even with the cloudformation The instance type can be relatively small. Just a note, I couldn't ssh on the server once it was up.
Once the server is up and running, you can access to the PMM via url the link is the ip. Then you can create the pmm credentials that you are going to use for each client that wants to connect.
Configure client
Install the package
apt-get install pmm-client
Connect client to the PMM server
pmm-admin config --server 10.112.50.60 --server-user sebba --server-password sebba
Start collecting data
There are two important agents to install for Mysql
- mysql:queries alias to enable MySQL query analytics.
- mysql:metrics alias to enable MySQL metrics monitoring.
USAGE
pmm-admin add [METRICS] [NAME] [OPTIONS]
The pmm-agent runs for the mysql:metrics and queries:metrics and it runs in the background on the server. The pmm agent for the Aurora clusters can run remotely on the dbhk server.
This command starts the deamon reading from the mysql server localhost
pmm-admin add mysql
Using PMM with Amazon RDS
- Query analytics requires
- Performance Schema as the query source
- P_S statements_digest and events_statements_history
- Mysql User
Create the pmm user with the following privileges on the RDS instance that you want to monitor:
GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'pmm'@'%' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, UPDATE, DELETE, DROP ON performance_schema.* TO 'pmm'@'%';
- Unique identifier
When adding a monitoring instance for RDS, specify a unique name to distinguish it from the local MySQL instance. If you do not specify a name, it will use the client's host name.
# mysql:metrics
sudo pmm-admin add mysql:metrics --host catalog-database-1-read-replica-4-internal.catalogtest.website.com --user pmm --password pass catalog-aurora-1-w
# mysql:queries
sudo pmm-admin add mysql:queries --host catalog-database-1-read-replica-4-internal.catalogtest.website.com --user pmm --password pass catalog-aurora-1-w
Useful commands
pmm-admin info
pmm-admin check-network
removing Data Collection
localhost
pmm-admin remove mysql
Remotelly for RDS
# session-aurora-w is the name of the collection service
pmm-admin remove mysql session-aurora-w
Using the Percona Monitoring and Management Platform
Using-PMM You can access the PMM web interface using the IP address of the host where PMM Server is running. IE if PMM Server is 192.168.100.1, access to => http://192.168.100.1
The landing page has links to corresponding PMM tools:
- Query Analytics
- Metrics Monitor
- Orchestrator
The Query Analytics tool enables database administrators and application developers to analyze MySQL queries over periods of time and find performance problems.
The summary table contains top 10 queries ranked by %GTT (percent of grand total time), which is the percentage of time that the MySQL server spent executing a specific query, compared to the total time it spent executing all queries during the selected period of time.
If you have multiple MySQL hosts with PMM Client installed, you can switch between those hosts using the drop-down list at the top.
To configure the QAN agent running on a MySQL host with PMM Client, click the gear icon at the top.
The Metrics Monitor tool provides a historical view of metrics that are critical to a database server. Time-based graphs are separated into dashboards by themes: some are related to MySQL or MongoDB, others provide general system metrics.
Orchestrator is a MySQL replication topology management and visualization tool. You can access it using the /orchestrator URL after PMM Server address. This tool can not be used with Aurora clusters because the privilege "SUPER" can not be given to any user.
Conclusion
The agent runs remotely and it is meant to work with RDS instances as well.
Queries on the P_S database can be heavy sometimes I recommend installing the agent firstly on the database with more headroom so that we can monitor the overhead on the performance. While the first two dashboards are handy and meaningful for our scopes, the "Mysql replication topology management" is not partial working because it can not manage the replication stream of Aurora even though we really don't change often the replication chain infrastructure.
It comes free so it is harmless to have it. The Query analytics is useful if the performance_schema is tuned on the right configuration like what you want to audit and how often. I'd prefer to set it to the default and then modify it later.
I tried to run a load test on the instances in order to have some meaningful data, but I couldn't work out which was the right configurations for sysbench to have meaningful statements.
The "Metrics Monitor" which uses the power of Hosted Graphite, it is embedded internal at the pmm server, provides a historical view of metrics that are critical to a database server. I didn't go through all available dashboards but first look I understood that we really can use it to improve performance.