Downloads

Blog

Contact Us

Percona Monitoring and Management (PMM) 1.15.0 Is Now Available

October 10, 2018

Author

Share this Post:

Percona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL® and MongoDB® performance. You can run PMM 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.

Percona Monitoring and Management

This release offers two new features for both the MySQL Community and Percona Customers:

MySQL Custom Queries – Turn a SELECT into a dashboard!

Server and Client logs – Collect troubleshooting logs for Percona Support

We addressed 17 new features and improvements, and fixed 17 bugs.

MySQL Custom Queries

In 1.15 we are introducing the ability to take a SQL SELECT statement and turn the result set into metric series in PMM. The queries are executed at the LOW RESOLUTION level, which by default is every 60 seconds. A key advantage is that you can extend PMM to profile metrics unique to your environment (see users table example), or to introduce support for a table that isn’t part of PMM yet. This feature is on by default and only requires that you edit the configuration file and use vaild YAML syntax. The configuration file is in /usr/local/percona/pmm-client/queries-mysqld.yml.

Example – Application users table

We’re going to take a fictional MySQL users table that also tracks the number of upvotes and downvotes, and we’ll convert this into two metric series, with a set of seven labels, where each label can also store a value.

Browsing metrics series using Advanced Data Exploration Dashboard

Lets look at the output so we understand the goal – take data from a MySQL table and store in PMM, then display as a metric series. Using the Advanced Data Exploration Dashboard you can review your metric series. Exploring the metric series app1_users_metrics_downvotes we see the following:

PMM Advanced Data Exploration Dashboard

MySQL table

Lets assume you have the following users table that includes true/false, string, and integer types.

SELECT * FROM `users`<br>+----+------+--------------+-----------+------------+-----------+---------------------+--------+---------+-----------+<br>| id | app  | user_type    | last_name | first_name | logged_in | active_subscription | banned | upvotes | downvotes |<br>+----+------+--------------+-----------+------------+-----------+---------------------+--------+---------+-----------+<br>|  1 | app2 | unprivileged | Marley    | Bob        |         1 |                   1 |      0 |     100 |        25 |<br>|  2 | app3 | moderator    | Young     | Neil       |         1 |                   1 |      1 |     150 |        10 |<br>|  3 | app4 | unprivileged | OConnor   | Sinead     |         1 |                   1 |      0 |      25 |        50 |<br>|  4 | app1 | unprivileged | Yorke     | Thom       |         0 |                   1 |      0 |     100 |       100 |<br>|  5 | app5 | admin        | Buckley   | Jeff       |         1 |                   1 |      0 |     175 |         0 |<br>+----+------+--------------+-----------+------------+-----------+---------------------+--------+---------+-----------+

1

SELECT * FROM `users` +----+------+--------------+-----------+------------+-----------+---------------------+--------+---------+-----------+ | id | app | user_type | last_name | first_name | logged_in | active_subscription | banned | upvotes | downvotes | +----+------+--------------+-----------+------------+-----------+---------------------+--------+---------+-----------+ | 1 | app2 | unprivileged | Marley | Bob | 1 | 1 | 0 | 100 | 25 | | 2 | app3 | moderator | Young | Neil | 1 | 1 | 1 | 150 | 10 | | 3 | app4 | unprivileged | OConnor | Sinead | 1 | 1 | 0 | 25 | 50 | | 4 | app1 | unprivileged | Yorke | Thom | 0 | 1 | 0 | 100 | 100 | | 5 | app5 | admin | Buckley | Jeff | 1 | 1 | 0 | 175 | 0 | +----+------+--------------+-----------+------------+-----------+---------------------+--------+---------+-----------+

Explaining the YAML syntax

We’ll go through a simple example and mention what’s required for each line. The metric series is constructed based on the first line and appends the column name to form metric series. Therefore the number of metric series per table will be the count of columns that are of type GAUGE or COUNTER. This metric series will be called app1_users_metrics_downvotes:

app1_users_metrics:                                 ## leading section of your metric series.<br>  query: "SELECT * FROM app1.users"                 ## Your query. Don't forget the schema name.<br>  metrics:                                          ## Required line to start the list of metric items<br>    - downvotes:                                    ## Name of the column returned by the query. Will be appended to the metric series.<br>        usage: "COUNTER"                            ## Column value type.  COUNTER will make this a metric series.<br>        description: "Number of upvotes"            ## Helpful description of the column.

1

app1_users_metrics: ## leading section of your metric series. query: "SELECT * FROM app1.users" ## Your query. Don't forget the schema name. metrics: ## Required line to start the list of metric items - downvotes: ## Name of the column returned by the query. Will be appended to the metric series. usage: "COUNTER" ## Column value type. COUNTER will make this a metric series. description: "Number of upvotes" ## Helpful description of the column.

Full queries-mysqld.yml example

Each column in the SELECT is named in this example, but that isn’t required, you can use a SELECT * as well. Notice the format of schema.table for the query is included.

---<br>app1_users_metrics:<br>  query: "SELECT app,first_name,last_name,logged_in,active_subscription,banned,upvotes,downvotes FROM app1.users"<br>  metrics:<br>    - app:<br>        usage: "LABEL"<br>        description: "Name of the Application"<br>    - user_type:<br>        usage: "LABEL"<br>        description: "User's privilege level within the Application"<br>    - first_name:<br>        usage: "LABEL"<br>        description: "User's First Name"<br>    - last_name:<br>        usage: "LABEL"<br>        description: "User's Last Name"<br>    - logged_in:<br>        usage: "LABEL"<br>        description: "User's logged in or out status"<br>    - active_subscription:<br>        usage: "LABEL"<br>        description: "Whether User has an active subscription or not"<br>    - banned:<br>        usage: "LABEL"<br>        description: "Whether user is banned or not"<br>    - upvotes:<br>        usage: "COUNTER"<br>        description: "Count of upvotes the User has earned.  Upvotes once granted cannot be revoked, so the number can only increase."<br>    - downvotes:<br>        usage: "GAUGE"<br>        description: "Count of downvotes the User has earned.  Downvotes can be revoked so the number can increase as well as decrease."<br>...

1

--- app1_users_metrics: query: "SELECT app,first_name,last_name,logged_in,active_subscription,banned,upvotes,downvotes FROM app1.users" metrics: - app: usage: "LABEL" description: "Name of the Application" - user_type: usage: "LABEL" description: "User's privilege level within the Application" - first_name: usage: "LABEL" description: "User's First Name" - last_name: usage: "LABEL" description: "User's Last Name" - logged_in: usage: "LABEL" description: "User's logged in or out status" - active_subscription: usage: "LABEL" description: "Whether User has an active subscription or not" - banned: usage: "LABEL" description: "Whether user is banned or not" - upvotes: usage: "COUNTER" description: "Count of upvotes the User has earned. Upvotes once granted cannot be revoked, so the number can only increase." - downvotes: usage: "GAUGE" description: "Count of downvotes the User has earned. Downvotes can be revoked so the number can increase as well as decrease." ...

We hope you enjoy this feature, and we welcome your feedback via the Percona forums!

Server and Client logs

We’ve enhanced the volume of data collected from both the Server and Client perspectives. Each service provides a set of files designed to be shared with Percona Support while you work on an issue.

Server

From the Server, we’ve improved the logs.zip service to include:

Prometheus targets

Consul nodes, QAN API instances

Amazon RDS and Aurora instances

Version

Server configuration

Percona Toolkit commands

You retrieve the link from your PMM server using this format: https://pmmdemo.percona.com/managed/logs.zip

Client

On the Client side we’ve added a new action called summary which fetches logs, network, and Percona Toolkit output in order to share with Percona Support. To initiate a Client side collection, execute:

pmm-admin summary

1	pmm-admin summary

The output will be a file you can use to attach to your Support ticket. The single file will look something like this:

summary__2018_10_10_16_20_00.tar.gz

1	summary__2018_10_10_16_20_00.tar.gz

New Features and Improvements

PMM-2913 – Provide ability to execute Custom Queries against MySQL – Credit to wrouesnel for the framework of this feature in wrouesnel/postgres_exporter!