Running Custom MySQL Queries in Percona Monitoring and Management

custom mysql queriesEven though Percona Monitoring and Management 2 (PMM) comes with a lot of dashboards and metrics out of the box, sometimes we need to extend the default metrics by running custom MySQL queries.

For example, suppose you want to have information about cached indexes from Innodb tables from innodb_cached_indexes table. That metric is not being captured by any default dashboard, but it is possible to extend PMM and make it capture the result of custom queries.

Getting Started With Custom Queries

Custom queries can be added to mysqld_exporter by adding them to the appropriate config file in /usr/local/percona/pmm2/collectors/custom-queries/mysql. There are three subdirectories inside it: high-resolution, low-resolution, and medium-resolution. PMM allows three resolutions for MySQL exporter: five seconds, five seconds (it is not an error), and 60 seconds.

Suppose we want to add metrics from the innodb_cached_indexes table in MySQL and we want to run this query every five seconds (high resolution). To achieve that, let’s edit the file high-resolution/queries-mysqld.yml located in the exporter’s custom queries config mentioned above, and add this:

As we can see, this query will return four columns and the column size_bytes will be used to plot the data graph (usage gauge). The metric name in Grafana will be a composition of the name we defined, mysql_innodb_index_stats plus the column name: mysql_innodb_index_stats_size_bytes.
Yaml description:

query: The query itself. It can be split into several lines as long as you keep it enclosed in quotes. There is no limit for the query size and special chars can be escaped using regular \ escape sequences or Unicode escapes like \u0022  for a double quote.

metrics: This is where the query fields are being described. Is the list of the query fields with their attributes.

usage: The metric type. It should be the one from the following: LABEL, COUNTER, GAUGE, DURATION, DISCARD. If usage is LABEL this value will be used as Prometheus dimension.
description: The field description.

Where Can I See the Dashboard?

Go to the search box located at the upper left corner and start writing Advanced. When it appears, click on Advanced Data Exploration.

Then, look for the metric name. Remember the name is a composition between the name we gave to the metric + the column name.

Percona Monitoring and Management dashboard

and here we have the graph:

It is possible to run multiple queries from the same yml file by adding more queries. There’s just the one in the example, but consider this:

  • All queries are going to be executed sequentially
  • Query execution should fit into the selected resolution. If the resolution is 5 seconds, all queries should run in less than 5 seconds.

Because of these limits, remember that if a query runs for more than four seconds, no data will be collected.  Also, there is no need to set a maximum number of connections to the database since all queries run sequentially, so, only one connection at a time will be used.

As an example, if you add a query that takes more than four seconds to run (in this example, just a SELECT SLEEP), the graph might look like this:

All the gaps are the result of the timeout in the queries execution.

Error handling

Query error

If a query fails, you first will notice it because there won’t be any metric for that query but, why is it failing? All the errors from the mysqld exporter are logged into syslog. A tail to /var/log/syslog combined with a grep, it will show the queries with errors, if there are any.

Notice that if a query fails, it won’t affect the results for all the other queries before or after it.

Field definitions errors:

Every row in the query result must have a unique combination of names and labels. Following the previous examples, what happens if I make the index name constant? All rows will have different numeric values but the label’s combination will be the same for all rows. Even when this is a coarse example, it lets me show you a Prometheus metrics collector error.

In the following example, notice that the index_name  is now enclosed in single quotes so, it is a constant.

Since all the metrics will have duplicated labels, only the first one will be used and all the rest will be skipped by Prometheus because label combinations should be unique. In the error log, you can find a message like this:

And in the graph, there will be only one metric.

Another example of possible error with labels, is what happens is a label duplicates an inherited label, for example, the instance name?

Well, let’s try it. Let’s change the query to make it return a new constant field name “instance”. It will duplicate the inherited instance name label.

As we can see, duplicated labels will be ignored in favor of the already set labels:

 

There is a repository at PerconaLab with some query examples located here. Feel free to contribute with your custom queries.

A related article by Daniel Guzmán Burgos: PMM’s Custom Queries in Action: Adding a Graph for InnoDB mutex waits

Share this post

Comments (2)

  • Sai Charan Reply

    I tried the same exact steps on on my PMM server that is running in docker. I cannot find the new custom queries in Advanced Data Exploration. I restarted the pmm container and the agent .. they both started just fine but I dont find ‘mysql_innodb_index_stats_size_bytes’ and nothing in the system logs of the agent or the server.

    Please help me troubleshoot …

    October 14, 2020 at 12:26 pm
    • Renan Benedicto Pereira Reply

      The same happens to me.

      {
      “version”:”2.8.0″,
      “server”:{
      “version”:”2.8.0″,
      “full_version”:”2.8.0-34.2006251358.f31f32e.el7″,
      “timestamp”:”2020-06-25T13:58:43Z”
      },
      “managed”:{
      “version”:”2.8.0″,
      “full_version”:”a35b5127806bef4d41b1e4f70a0d35a9577677ab”,
      “timestamp”:”2020-06-18T09:56:03Z”
      }

      November 10, 2020 at 7:22 am

Leave a Reply