Even 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql_innodb_index_stats: ## The namespace (prefix of the metric name) for the custom query. query: "select database_name as schema_name,table_name,index_name,stat_value*@@innodb_page_size as size_bytes from mysql.innodb_index_stats where stat_name='size';" metrics: - table_name: usage: "LABEL" description: "Table name" - size_bytes: usage: "GAUGE" description: "Index Size in Bytes" - schema_name: usage: "LABEL" description: "Schema Name" - index_name: usage: "LABEL" description: "Index name" |
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.
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.
1 2 3 4 5 6 | tail /var/log/syslog | grep mysqld_exporter May 9 20:05:18 karl-OMEN pmm-agent[1044]: #033[36mINFO#033[0m[2020-05-09T20:05:18.662-03:00] time="2020-05-09T20:05:18-03:00" level=error msg="Error scraping for collect.custom_query.mr: mysql_performance_schema_blog:error running query on database: mysql_performance_schema_blog, Error 1356: View 'sys.memory_global_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" source="exporter.go:116" #033[36magentID#033[0m=/agent_id/4d308ab4-4791-4b8b-b7cb-24b05c012be8 #033[36mcomponent#033[0m=agent-process #033[36mtype#033[0m=mysqld_exporter |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql_performance_index_stats_blog2: query: "select database_name as schema_name,table_name, 'index_name',stat_value*@@innodb_page_size as size_bytes from mysql.innodb_index_stats where stat_name='size';" metrics: - schema_name: usage: "LABEL" description: "Performance Schema Event Name" - table_name: usage: "LABEL" description: "Performance Schema Event Name" - index_name: usage: "LABEL" description: "Performance Schema Event Name" - size_bytes: usage: "GAUGE" description: "Memory currently allocated to the Event" |
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:
1 2 3 4 | pmm-agent[1044]: #033[36mINFO#033[0m[2020-05-10T10:21:59.202-03:00] time="2020-05-10T10:21:59-03:00" level=error msg="error gathering metrics: 24 error(s) occurred: [from Gatherer #1] <strong><span style="color: #ff0000;">collected metric</span></strong> "<span style="color: #0000ff;"><strong>mysql_performance_index_stats_blog2_size_bytes</strong></span>" { label:<name:"index_name" value:"index_name" > label:<name:"schema_name" value:"sakila" > label:<name:"table_name" value:"actor" > gauge:<value:16384 > }<span style="color: #ff0000;"><strong> was collected before with the same name and label values</strong></span> |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql_performance_index_stats_blog: query: "select database_name as schema_name,table_name,index_name,stat_value*@@innodb_page_size as leaf_pages_size_bytes, 'abc001' AS instance from mysql.innodb_index_stats where stat_name='n_leaf_pages';" metrics: - schema_name: usage: "LABEL" description: "Performance Schema Event Name" - instance: usage: "LABEL" description: "fake instance" - table_name: usage: "LABEL" description: "Performance Schema Event Name" - index_name: usage: "LABEL" description: "Performance Schema Event Name" - leaf_pages_size_bytes: usage: "GAUGE" description: "Memory currently allocated to the Event" |
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
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 …
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”
}