Percona Monitoring and Management is a great open source database monitoring, observability, and management tool. Query analytics is one of the prominent features DBA uses actively to trace the incidents and query performance identification.

We all know and love the Query Analytics (QAN) dashboard… It’s the first place we look when an incident alert fires or when a developer asks, “Why is the app slow?” or “What was going on during the midnight production outage?”

But sometimes, the standard dashboards just don’t tell the whole story or maybe are not clear enough. QAN is great, but shouldn’t we have more? If you have PMM running, you already have a Ferrari engine under the hood: ClickHouse. Most of us just drive it in first gear using the default UI.

In this post, we are going to take the training wheels off. We will bypass the standard QAN interface and talk directly to the ClickHouse backend to build highly specialised dashboards. We aren’t just looking for “slow” queries anymore; we are hunting for inefficiency, volatility, and the “silent killers” that standard monitoring often misses.

This is the hands-on blog, so grab your coffee and let’s turn that PMM instance into a deep-dive forensic tool.

Create a New Dashboard in PMM

1. Connect to PMM > Dashboards > Create New Dashboard
2. Save it with name “Slow Query Analysis” and Description “Slow Query Analysis from PMM’s QAN database (clickhouse)”
3. Click on add visualisation & select datasource “ClickHouse”
   
   
4. Choose SQL Builder
   
   
5. Paste the following query to get top 10 slow queries from the database
   

   SELECT fingerprint
       FROM pmm.metrics
       WHERE service_type = 'mysql'
         AND $__timeFilter(period_start)
       GROUP BY fingerprint
       ORDER BY sum(m_query_time_sum) DESC
       LIMIT 10

6. Choose “Table View” on the top to view the list
   When you click “Run Query” you will see the top 10 slow queries in the chosen time period.
7. Let’s Save the dashboard after Panel Options updates as follows7.1 Change Panel Name and Description to: “Slow Query Analysis”7.2 Legend Placement to “Bottom”, Values to “min”,”max”, “mean”7.3 Change Axis’ Scale to “Logarithmic”Logarithmic scale on an axis compresses large ranges of data, making it ideal for visualizing metrics with vastly different magnitudes. This provides good visualisation for queries of different execution time frames.7.4 Save DashboardAlright, we’re at our first step. This first result set shows the top 10 slow query fingerprints across all MySQL services tracked by PMM for the selected time range. It provides a quick, environment-wide view of the most expensive query patterns. But this does not provide a clear picture. Let’s refine the dashboard to focus on specific queries, servers and observe their performance over time.Now, let’s introduce a variable to filter the data.
8. Click on Settings on Dashboard’s home page8.1 Choose “Variables” tab and click on “Add Variable”8.2 Add variable configuration and Save Dashboard 
9. Go Back to Dashboard and Edit “Slow Query Analysis” Panel.
   • Now you should see the Query ID filter on the top.
10. Change the query to the following
    

    SELECT
      period_start AS time,
      left(fingerprint, 80) AS query_text,
      sum(m_query_time_sum/m_query_time_cnt) AS query_time
    FROM
      pmm.metrics
    WHERE
      service_type = 'mysql'
      AND $__timeFilter(period_start)
      AND fingerprint IN (
        SELECT fingerprint
        FROM pmm.metrics
        WHERE service_type = 'mysql'
          AND $__timeFilter(period_start)
          AND ($queryid = '' OR queryid = $queryid)
        GROUP BY fingerprint
        ORDER BY sum(m_query_time_sum) DESC
        LIMIT 10
      )
    GROUP BY
      time,
      fingerprint
    ORDER BY
      time,
      query_time DESC

    • Basically the query is fetching start time, query text and average query time for the selected period for the top 10 Queries in that time-frame.
    • There is a filter for the “queryid” variable which you may use if you want to filter on a specific queryid.
    • Choose “Time Series” as “Query Type”
11. Adjust Panel Options11.1 Choose “Standard options” > “Unit” as “Time / Seconds (s)” from drop down.11.2 Choose “Standard options” > “Display name” as “${__field.labels.query_text}”11.3 Click on “Save Dashboard”
12. Your dashboard should be ready

Now, by default this dashboard is plotting top 10 queries. If you have a query fingerprint handy, you may be able to filter the search by that specific query.  That said, this is still plotting queries across all the monitored instances. Let’s move on to add the service_name filter.

 

Adding service_name filter

1. Add Variable
   1. Create new variable named “service_name”
   2. Use variable type “Query”
   3. Use Data Source as “ClickHouse”
   4. Query:
      

      select distinct service_name from pmm.metrics where service_type = 'mysql';

   5. Unselect all checkboxes in “Selection options”
   6. Save Dashboard
2. Update Query

SELECT
  period_start AS time,
  left(fingerprint, 80) AS query_text,
  sum(m_query_time_sum/m_query_time_cnt) AS query_time
FROM
  pmm.metrics
WHERE
  (service_name = '' OR service_name = '$service_name')
  AND service_type = 'mysql'
  AND $__timeFilter(period_start)
  AND fingerprint IN (
    SELECT fingerprint
    FROM pmm.metrics
    WHERE service_type = 'mysql'
      AND $__timeFilter(period_start)
      AND (service_name = '' OR service_name = '$service_name')
    GROUP BY fingerprint
    ORDER BY sum(m_query_time_sum) DESC
    LIMIT 10
  )
GROUP BY
  time,
  left(fingerprint, 80) 
ORDER BY
  time,
  query_time DESC

I know many of you are naturally curious and enjoy experimenting with PMM and Grafana… So you’ve probably already started thinking about how far this can be taken. Feel free to share your ideas or custom dashboards in the comments.

Sample Dashboards:

The Query Analysis and Insights Dashboard

Okay, for those who are looking to have quick results, I’ve prepared the complete Query Analysis and Insights Dashboard for you to import and use instantly.

By importing the JSON file, you’ll get the full working dashboard with all panels preconfigured, including:

• Slow Query Analysis
• Latency Distribution Heatmap
• Query Volatility (P99 vs Average)
• Lock Wait Ratio Over Time (Top Contended Queries)
• Temporary Table Usage (Disk & Memory)
• Query Efficiency (Rows Examined vs Rows Sent)
• Error Rate vs Throughput
• Workload Distribution by User
• Query Volume by Client Host
• Execution Time vs Lock Wait Time

This allows you to instantly explore PMM Query Analytics data, adjust time ranges and filters, and correlate query performance, contention, and workload behavior without recreating the dashboard from scratch.

Dashboard JSON available here:

• Grafana: https://grafana.com/grafana/dashboards/24896
• GitHub:  https://github.com/Percona-Lab/pmm-dashboards/query_analysis_insights.json

Give it a go and let me know if you have suggestions or requests. Also consider sharing if you create something interesting.

Cheers.

The post Building Query Analysis and Insights Dashboard in PMM appeared first on Percona.

The Problem If you’ve integrated Grafana Alerting with PagerDuty, you’ve probably noticed something frustrating: the PagerDuty incident details are cluttered with every single label and annotation from your alerts. Here’s what you typically see: [crayon-69c5fc8666ca2504109802/] This wall of text makes it hard for your on-call engineers to quickly identify what’s wrong. And actually, this was […]

Unveiling the Limits: A Performance Analysis of MongoDB Sharded Clusters with plgm In any database environment, assumptions are the enemy of stability. Understanding the point at which a system transitions from efficient to saturated is essential for maintaining uptime and ensuring a consistent and reliable user experience. Identifying these limits requires more than estimation—it demands […]

A vulnerability has been discovered in all versions of Percona Monitoring and Management (PMM). There is no evidence this vulnerability has been exploited in the wild, and no customer data has been exposed. Vulnerability details This vulnerability stems from the way PMM handles input for MySQL services and agent actions. By abusing specific API endpoints, […]

Monitoring your databases is critical, especially in Kubernetes environments where visibility and automation are key. That’s why, in Percona Everest 1.6.0, we introduced a highly requested feature: the ability to automatically deploy Percona Monitoring and Management (PMM) as part of the Everest Helm chart using just one flag. This simplifies the process for teams who […]

Evolution is one of the inherent traits of modern software. Many people reach out to product teams daily, asking to add more functionality to the software products they use and love. This is understandable: there will always be ways to make a product better by adding more features to the users’ delight so they can […]

At Percona, we’re committed to providing you with the best database monitoring and management tools. With the release of Percona Monitoring and Management 3 (PMM 3), we’re now entering a critical phase in the lifecycle of PMM 2. We understand that PMM 2 remains a vital tool for many of our users, and we want […]

Database disasters come with a hefty price tag. According to Information Technology Intelligence Consulting’s 2024 Hourly Cost of Downtime Report, 41% of enterprises face downtime costs ranging from $1 million to over $5 million per hour of outage. How can enterprises ensure their critical database infrastructure remains healthy and performant? This question keeps IT leaders […]

What index will be used when you count all rows in a table? Well, the MySQL documentation provides a straightforward answer to this, quoting: InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is […]

This article will be helpful if you use the Percona Monitoring and Management (PMM) instance and alert notifications, as it is nice to capture the image of the graph when you receive the alert. We will see how to capture and attach the image of the graph when receiving the alert notification (email, telegram, Slack, […]