A few weeks ago we did a live stream talking about Percona Monitoring and Management (PMM) and showcased some of the fun things we were doing at the OSS Summit. During the live stream, we tried to enable some custom queries to track the number of comments being added to our movie database example. We ran into a bit of a problem live and did not get it to work. As a result, I wanted to follow up and show you all how to add your own custom metrics to PMM and show you some gotchas to avoid when building them.
Custom metrics are defined in a file deployed on each server you are monitoring (not on the server itself). You can add custom metrics by navigating over to one of the following:
- For MySQL: /usr/local/percona/pmm2/collectors/custom-queries/mysql
- For PostgreSQL: /usr/local/percona/pmm2/collectors/custom-queries/postgresql
- For MongoDB: This feature is not yet available – stay tuned!
You will notice the following directories under each directory:
- high-resolution/ – every 5 seconds
- medium-resolution/ – every 10 seconds
- low-resolution/ – every 60 seconds
Note you can change the frequency of the default metric collections up or down by going to the settings and changing them there. It would be ideal if in the future we added a resolution config in the YML file directly. But for now, it is a universal setting:
In each directory you will find an example .yml file with a format like the following:
1 2 3 4 5 6 | mysql_oss_demo: query: "select count(1) as comment_cnt from movie_json_test.movies_normalized_user_comments;" metrics: - comment_cnt: usage: "GAUGE" description: "count of the number of comments coming in" |
Our error during the live stream was we forgot to include the database in our query (i.e. table_name.database_name), but there was a bug that prevented us from seeing the error in the log files. There is no setting for the database in the YML, so take note.
This will create a metric named mysql_oss_demo_comment_cnt in whatever resolution you specify. Each YML will execute separately with its own connection. This is important to understand as if you deploy lots of custom queries you will see a steady number of connections (this is something you will want to consider if you are doing custom collections). Alternatively, you can add queries and metrics to the same file, but they are executed sequentially. If, however, the entire YML file can not be completed in less time than the defined resolution ( i.e. finished within five seconds for high resolution), then the data will not be stored, but the query will continue to run. This can lead to a query pile-up if you are not careful. For instance, the above query generally takes 1-2 seconds to return the count. I placed this in the medium bucket. As I added load to the system, the query time backed up.
You can see the slowdown. You need to be careful here and choose the appropriate resolution. Moving this over to the low resolution solved the issue for me.
That said, query response time is dynamic based on the conditions of your server. Because these queries will run to completion (and in parallel if the run time is longer than the resolution time), you should consider limiting the query time in MySQL and PostgreSQL to prevent too many queries from piling up.
In MySQL you can use:
1 2 3 4 5 6 7 8 | mysql> select /*+ MAX_EXECUTION_TIME(4) */ count(1) as comment_cnt from movie_json_test.movies_normalized_user_comments ; ERROR 1317 (70100): Query execution was interrupted And on PostgreSQL you can use: SET statement_timeout = '4s'; select count(1) as comment_cnt from movies_normalized_user_comments ; ERROR: canceling statement due to statement timeout |
By forcing a timeout you can protect yourself. That said, these are “errors” so you may see errors in the error log.
You can check the system logs (syslog or messages) for errors with your custom queries (note at this time as of PMM 2.0.21, errors were not making it into these logs because of a potential bug). If the data is being collected and everything is set up correctly, head over to the default Grafana explorer or the “Advanced Data Exploration” dashboard in PMM. Look for your metric and you should be able to see the data graphed out:
In the above screenshot, you will notice some pretty big gaps in the data (in green). These gaps were caused by our query taking longer than the resolution bucket. You can see when we moved to 60-second resolution (in orange), the graphs filled in.
Percona Monitoring and Management 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.
Can we execute multi line query with dirty read.
For example a below query like this:
airbnb_raw_pricing_scrape_count:
query: |
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT COUNT(1) AS count, DATE(updated_at) AS update_date
FROM db.table
WHERE updated_at >= (SELECT DATE(MAX(updated_at)) – INTERVAL 20 DAY FROM db.table)
GROUP BY update_date
ORDER BY update_date DESC
LIMIT 10;
metrics:
– count:
usage: “GAUGE”
description: “Count”
– update_date:
usage: “LABEL”
description: “Update Date”