Percona has bundled together some great open source pieces of software to create Percona Monitoring and Management (PMM), a very flexible and powerful monitoring solution. The goal of this post is not to introduce you to the existing PMM dashboards but to show you how to create your own application-centric custom PMM dashboards.
If you have deployed PMM, chances are you have an application using a database like MySQL or PostgreSQL. PMM relies on Grafana for its dashboards. And although there is a wealth of performance-oriented dashboards, nothing prevents you from adding business-related custom PMM dashboards.
A few years ago, I installed on my home roof a small solar array. Although the equipment provider provides a basic dashboard, I wanted to create my own custom PMM dashboard showing how much electricity I am producing.
Data
The first ingredient needed for the custom dashboard is the data. The inverter system I am using has a status web page that shows the current power production level. With some HTML parsing, I used this status page to populate a MySQL table with the power production data every 15 minutes. The table structure is the following:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | mysql> show create table productionG *************************** 1. row *************************** Table: production Create Table: CREATE TABLE `production` ( `power` smallint(5) unsigned NOT NULL, `time` datetime NOT NULL, PRIMARY KEY (`time`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec) mysql> select * from production limit 5; +-------+---------------------+ | power | time | +-------+---------------------+ | 736 | 2023-07-20 14:00:00 | | 698 | 2023-07-20 14:15:00 | | 657 | 2023-07-20 14:30:00 | | 615 | 2023-07-20 14:45:00 | | 568 | 2023-07-20 15:00:00 | +-------+---------------------+ 5 rows in set (0.00 sec) |
Grafana data source
Now that we have the power production data in MySQL, we need to configure Grafana to access the MySQL server. This differs from the performance metrics, which use the collectors part of the PMM client package. If you are looking to create custom performance metrics dashboards, have a look at Adding Custom Graphs and Dashboards to Percona Monitoring and Management from Daniel.
Instead of using the Prometheus data source, we will configure a MySQL data source. On the PMM web page, log in with the admin credentials. Then click on the Gear icon located bottom-left.
Then choose Data sources in the drop-down menu. This opens the Data sources configuration page:
On this page, click on Add data source and scroll down to MySQL item:
Clicking on this item opens the configuration page:
Fill the forms with the details of your database server, and once ready, click on Save & Test. The database credentials should at least provide SELECT on the solar.production table from the PMM host. If all is good, you should see a green box with a checkmark at the bottom with the message Database Connection OK. We now have our data source, and we can proceed with creating our custom dashboard.
Adding a dashboard
To add a dashboard, select Dashboards icon in the left vertical bar and then + New dashboard. This opens a New dashboard page. We’ll first rename the Dashboard to “Solar power” – click on the top right gear button:
And in the top text box, enter the desired name. Once done, click on ‘Save dashboard.’
At this point, we are ready to add a panel. Click on the “Add panel” button, two positions to the left of the gear button we click, and then “Add a new panel.”
This opens the “Edit Panel” page, where we first need to select the data source we previously created. Next, although we could use the Query builder directly, we’ll proceed manually by clicking on the “Edit SQL” button. The query must return three columns, the time of measurement as time_sec, the measurement itself as value, and the metric name as metric. With the where and order by clauses, the query should look like this:
Once you’re done with the query, edit the Title in the Panel options on the right. Finally, click on the Apply button at the top right. If all went well, you should see your first panel:
There are many types of panels, each with many different options. Explore to your liking. The goal of this post is to show how to link PMM with a database, and if you followed to the end, that’s exactly what we accomplished.
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.
Download Percona Monitoring and Management Today
