Skip to content
logo
Percona Monitoring and Management
Query Analytics
Initializing search
    percona/pmm-doc
    percona/pmm-doc
    • Welcome
    • Setting up
      • Server
        • Network
        • Docker
        • Podman
        • Helm
        • Virtual Appliance
        • AWS Marketplace
        • Easy-install script
      • Client
        • MySQL
        • MongoDB
        • PostgreSQL
        • ProxySQL
        • Amazon RDS
        • Microsoft Azure
        • Google Cloud Platform
        • Linux
        • External Services
        • HAProxy
        • Remote instances
    • Get started
      • User Interface
      • Percona Alerting
      • Backup and Restore
        • Prepare a storage location
        • MongoDB backups
          • MongoDB backup prerequisites
          • Create MongoDB on-demand and scheduled backups
          • Create MongoDB PITR backups
          • Restore a MongoDB backup
          • MongoDB Backup and Restore support matrix
        • MySQL backups
          • MySQL backup prerequisites
          • Create a MySQL backup
          • Restore a MySQL backup
        • Edit a scheduled backup
        • Delete a backup
      • Roles and permissions
        • Configure access control
        • Labels for access control
        • Create access roles
        • Manage access roles
        • Assign roles to users
        • Use Case
      • Query Analytics
        • Filters Panel
          • Custom filter groups
            • Supported technologies and agents
            • Enabling custom filter groups
        • Overview Panel
          • Columns
          • Tool-tips
          • Adding and removing columns
          • Sorting
          • Pagination
        • Details Panel
          • Details Tab
          • Examples Tab
          • Explain Tab
          • Tables Tab
          • Plan Tab
        • Query Analytics for PostgreSQL
        • Query Analytics for MongoDB
        • Sharing a link for Query Analytics
      • Working with Advisors
    • How to
      • Configure
      • Manage users
      • Upgrade
      • Secure
      • Optimize
      • Annotate
      • Share dashboards and panels
      • Extend Metrics
      • Resolve issues
      • Integrate with Percona Platform
        • Check Percona Portal account information
    • Details
      • Architecture
      • UI components
      • PMM components and versions
      • Data handling in PMM
      • Develop Advisor checks
      • PMM Inventory
      • Dashboards
        • Manage dashboards
          • Insight
            • Advanced Data Exploration
            • VictoriaMetrics
            • VictoriaMetrics Agents Overview
          • Environments
            • Environment Summary
          • Kubernetes
            • Kubernetes Pods Status
            • Kubernetes Volumes
          • DBaas
          • OS Dashboards
            • Disk Details
            • Network Details
            • Memory Details
            • Node Temperature Details
            • Nodes Compare
            • Nodes Overview
            • Node Summary
            • NUMA Details
            • Processes Details
          • Prometheus Dashboards
            • Prometheus Exporters Overview
          • MySQL Dashboards
            • MySQL Command/Handler Counters Compare
            • MySQL InnoDB Compression Details
            • MySQL InnoDB Details
            • MySQL MyISAM/Aria Details
            • MySQL MyRocks Details
            • MySQL Instance Summary
            • MySQL Instances Compare
            • MySQL Instances Overview
            • MySQL Wait Event Analyses Details
            • MySQL Performance Schema Details
            • MySQL Query Response Time Details
            • MySQL Replication Summary
            • MySQL Group Replication Summary
            • MySQL Table Details
            • MySQL User Details
            • MySQL TokuDB Details
          • MongoDB Dashboards
            • Experimental MongoDB Collection Details
            • Experimental MongoDB Oplog Details
            • MongoDB Cluster Summary
            • MongoDB Instance Summary
            • MongoDB Instances Overview
            • MongoDB Instances Compare
            • MongoDB ReplSet Summary
            • MongoDB InMemory Details
            • MongoDB MMAPv1 Details
            • MongoDB WiredTiger Details
          • PostgreSQL Dashboards
            • PostgreSQL Instance Summary
            • PostgreSQL Instances Compare
            • Experimental PostgreSQL Vacuum Monitoring
          • ProxySQL Dashboards
          • HA Dashboards
            • PXC/Galera Cluster Summary
            • Experimental PXC/Galera Cluster Summary
            • PXC/Galera Nodes Compare
            • HAProxy Instance Summary
      • Commands
        • pmm-admin - PMM Administration Tool
        • pmm-agent - PMM Client agent
      • API
      • VictoriaMetrics
      • ClickHouse
      • PostgreSQL
      • Glossary
      • Introduction
      • DBaaS architecture
        • Setting up DBaaS
        • Create a Kubernetes Cluster
        • Deleting Kubernetes clusters
        • Activating DBaaS
        • Add a Kubernetes cluster automatically
        • Add a Kubernetes cluster manually
        • Manage allowed component versions
          • OLM installation
          • Operators installation
        • Add a DB Cluster
        • Manage a DB Cluster
        • Delete a DB Cluster
        • Create a database cluster from a template
      • Backup and restore
    • FAQ
    • Release Notes
      • PMM 2.39.0
      • PMM 2.38.1
      • PMM 2.38.0
      • PMM 2.37.1
      • PMM 2.37.0
      • PMM 2.36.0
      • PMM 2.35.0
      • PMM 2.34.0
      • PMM 2.33.0
      • PMM 2.32.0
      • PMM 2.31.0
      • PMM 2.30.0
      • PMM 2.29.1
      • PMM 2.29.0
      • PMM 2.28.0
      • PMM 2.27.0
      • PMM 2.26.0
      • PMM 2.25.0
      • PMM 2.24.0
      • PMM 2.23.0
      • PMM 2.22.0
      • PMM 2.21.0
      • PMM 2.20.0
      • PMM 2.19.0
      • PMM 2.18.0
      • PMM 2.17.0
      • PMM 2.16.0
      • PMM 2.15.1
      • PMM 2.15.0
      • PMM 2.14.0
      • PMM 2.13.0
      • PMM 2.12.0
      • PMM 2.11.1
      • PMM 2.11.0
      • PMM 2.10.1
      • PMM 2.10.0
      • PMM 2.9.1
      • PMM 2.9.0
      • PMM 2.8.0
      • PMM 2.7.0
      • PMM 2.6.1
      • PMM 2.6.0
      • PMM 2.5.0
      • PMM 2.4.0
      • PMM 2.3.0
      • PMM 2.2.2
      • PMM 2.2.1
      • PMM 2.2.0
      • PMM 2.1.0
      • PMM 2.0.1
      • PMM 2.0.0
      • Copyright and licensing information
      • Trademark policy

    • Filters Panel
      • Custom filter groups
        • Supported technologies and agents
        • Enabling custom filter groups
    • Overview Panel
      • Columns
      • Tool-tips
      • Adding and removing columns
      • Sorting
      • Pagination
    • Details Panel
      • Details Tab
      • Examples Tab
      • Explain Tab
      • Tables Tab
      • Plan Tab
    • Query Analytics for PostgreSQL
    • Query Analytics for MongoDB
    • Sharing a link for Query Analytics

    Query Analytics¶

    The Query Analytics dashboard shows how queries are executed and where they spend their time. It helps you analyze database queries over time, optimize database performance, and find and remedy the source of problems.

    !image

    Query Analytics supports MySQL, MongoDB and PostgreSQL. The minimum requirements for MySQL are:

    • MySQL 5.1 or later (if using the slow query log).
    • MySQL 5.6.9 or later (if using Performance Schema).

    Query Analytics displays metrics in both visual and numeric form. Performance-related characteristics appear as plotted graphics with summaries.

    The dashboard contains three panels:

    • the Filters Panel;
    • the Overview Panel;
    • the Details Panel.

    Query Analytics data retrieval is not instantaneous and can be delayed due to network conditions. In such situations no data is reported and a gap appears in the sparkline.

    Filters Panel¶

    !image

    • The Filter panel on the left hand side of the dashboard lists the filters grouped by category. It also shows the percentage of the main metrics (explained below). If you select a different metric, the percentages on the left panel will change as per this metric. When you select a metric, it reduces the overview list as per the matching filter.
    • The first five of each category are shown. If there are more, the list is expanded by clicking Show all beside the category name, and collapsed again with Show top 5.
    • Applying a filter may make other filters inapplicable. These become grayed out and inactive.
    • Click the chart symbol to navigate directly to an item’s associated dashboard.
    • Separately, the global Time range setting filters results by time, either your choice of Absolute time range, or one of the predefined Relative time ranges.

    !image

    Custom filter groups¶

    Important/Caution

    This feature is still in Technical Preview and is subject to change. We recommend that early adopters use this feature for testing purposes only.

    Starting with PMM 2.38.0, you can filter queries by custom filter groups based on key=value pairs separated from query comments. By default, this feature is disabled.

    Supported technologies and agents¶

    • MySQL (perfschema, slowlog),
    • PostgreSQL (pg_stat_statements, pg_stat_monitor)

    Example

    !image

    In the image above we have tagged queries running databases on Windows using the following comment:

    comment: /* OperationSystem='windows' */. 
    
    Queries from the database running on Linux are tagged with:

    /* OperationSystem='linux' */. 
    

    All types of comments and multicomments are supported (/* */, --, # etc).

    So the queries are as follows:

    SELECT * /* OperationSystem='windows' */ FROM city;
    SELECT city /* OperationSystem='linux' */ FROM world;
    

    In the output, you can see another custom group in the OperationSystem filter. Use this to easily filter by any custom key or value.

    Enabling custom filter groups¶

    • CLI: While adding a service through CLI use the flag comments-parsing. Possible values are on/off.

      Example for adding MySQL with comments parsing on:

      pmm-admin add mysql --username=root --password=root-password --comments-parsing="on"
      
    • UI: While adding a service through the UI you will see new checkbox to enable/disable comments parsing for current service.

      !image

    MySQL CLI

    • If you are using official MySQL CLI to trigger queries, start mysql with --comments flag. Otherwise comments will not be parsed.
    • In case of PGSM (pg_stat_monitor), set the DB variable pgsm_extract_comments=yes

    Overview Panel¶

    The Overview panel is located above the dashboard, on the right side of the Filters panel.

    !image

    Each row of the table represents the metrics for a chosen object type, one of:

    • Query;
    • Service Name;
    • Database;
    • Schema;
    • User Name;
    • Client Host.

    At the top of the second column is the dimension menu. Use this to choose the object type.

    !image

    On the right side of the dimension column is the Dimension Search bar.

    !image

    Enter a string and press Enter to limit the view to queries containing only the specified keywords.

    Delete the search text and press Enter to see the full list again.

    Columns¶

    • The first column is the object’s identifier. For Query, it is the query’s Fingerprint.
    • The second column is the Main metric, containing a reduced graphical representation of the metric over time, called a sparkline, and a horizontal meter, filled to reflect a percentage of the total value.
    • Additional values are revealed as mouse-over tool-tips.

    Tool-tips¶

    • For the Query dimension, hovering over the information icon reveals the query ID and its example.
    • Hovering on a column header reveals an informative tool-tip for that column.
    • Hovering on the main metric sparkline highlights the data point and a tooltip shows the data value under the cursor.

    !image

    • Hovering on the main metric meter reveals the percentage of the total, and other details specific to the main metric.

      !image

    • Hovering on column values reveals more details on the value. The contents depends on the type of value.

      !image

    Adding and removing columns¶

    • Metrics columns are added with the Add column button.

      !image

    • When clicked, a text field and list of available metrics are revealed. Select a metric or enter a search string to reduce the list. Selecting a metric adds it to the panel.

    • A metric column is removed by clicking on the column heading and selecting Remove column.
    • The value plotted in the main metric column can be changed by clicking a metric column heading and selecting Swap with main metric.

    Sorting¶

    • The entire list is sorted by one of the columns.
    • Click either the up or down caret to sort the list by that column’s ascending or descending values.

    Pagination¶

    • The pagination device lets you move forwards or backwards through pages, jump to a specific page, and choose how many items are listed per page.

      !image

    • Queries are grouped into pages of 25, 50 or 100 items.

    Details Panel¶

    • Selecting an item in the Overview panel opens the Details panel with a Details Tab.
    • If the dimension is Query, the panel also contains the Examples Tab, Explain Tab, and Tables Tab.

    Details Tab¶

    The Details tab contains a Query time distribution bar (only for MySQL databases) and a set of Metrics in collapsible subpanels.

    !image

    • The Query time distribution bar shows a query’s total time made up of colored segments, each segment representing the proportion of time spent on a named activity.

      • query_time: Statement execution time.
      • lock_time: Time to acquire locks.
      • blk_read_time: Total time the statement spent reading blocks (if track_io_timing is enabled, otherwise zero).
      • blk_write_time: Total time the statement spent writing blocks (if track_io_timing is enabled, otherwise zero).
      • innodb_io_r_wait: Time for InnoDB to read the data from storage.
      • innodb_queue_wait: Time the query spent either waiting to enter the InnoDB queue, or in it pending execution.
      • innodb_rec_lock_wait: Time the query waited for row locks.
      • other: Remaining uncategorized query time.
    • Metrics is a table with headings:

      • Metric: The Metric name, with a question-mark tool-tip that reveals a description of the metric on mouse-over;
      • Rate/Second: A sparkline chart of real-time values per unit time;
      • Sum: A summation of the metric for the selected query, and the percentage of the total;
      • Per Query Stats: The value of the metric per query.
    • Each row in the table is a metric. The contents depends on the chosen dimension.

    • Metadata table:

      PMM 2.39.0 now includes a metadata table for QAN to address the issue of identifying problematic queries. This feature allows you easily identify the services, nodes, and labels associated with your identified queries.

      The Metadata table shows the following information:

      • Database: The database being monitored.
      • Environment: Environment being used such as Dev, Staging, Production, etc.
      • Node Name: Name or identifier of a node.
      • Node type: Type of the node.
      • User name: User name of database being monitored.
      • Service Name: The name or identifier of the service.
      • Service type: The service which is being monitored. For example, MySQL, PostgreSQl, etc.

      !image

    For PostgreSQL queries (when using pg_stat_monitor) the top query will also be shown in the details section if the query was called by an outer query.

    !image

    Other useful metrics (when using pg_stat_monitor) to monitor PostgreSQL Server performance are Histograms. Histograms provide more explicit information about number of queries for fingerprint (queryid). Ranges are from 0 seconds up to 100 seconds.

    Here is picture of histogram in graph: !image

    Examples Tab¶

    (For Query dimension.)

    The Examples tab shows an example of the selected query’s fingerprint or table element.

    !image

    Query example and fingerprint can be truncated to 1024 long to reduce space usage. In this case, the query explains section will not work.

    Explain Tab¶

    (For Query dimension.)

    The Explain tab shows the explain output for the selected query, in Classic or JSON formats.

    • MySQL: Classic and JSON.
    • MongoDB: JSON only.
    • PostgreSQL: Supported by pg_stat_monitor (PGSM), not by pg_stat_statements (PGSS).

    Starting with PMM 2.33.0, for MySQL, the Explain tab is supported without the Examples enabled. If a query in the Explain tab contains sensitive data, placeholders will replace them. Before you can run Explain, you must specify the values for these placeholders. This image illustrates the query with placeholders.

    !image

    Below is an illustration of the same query using values instead of placeholders.

    !image

    The image shown above illustrates a query with two placeholders. Therefore, you must enter the correct values in both fields. After filling in these values, click Explain to get the results like in the previous PMM versions without data leaks. You will get result like in previous PMM versions. This method of explain prevents data leak.

    ‘Explain’ for MongoDB

    To run Explain you need the same permissions as for executing the original query. For example, to run explain on updates you need update permissions.

    Example: Grant the explainRole with update permissions.

    db.grantPrivilegesToRole( "explainRole", [ { resource: { db: "", collection: "" }, actions: [ "update" ] } ])
    

    !image

    Tables Tab¶

    (For Query dimension.)

    The Tables tab shows information on the tables and indexes involved in the selected query.

    !image

    Plan Tab¶

    (For Query dimension.)

    The Plan tab shows the plan for PostgreSQL queries (only available when using pg_stat_monitor).

    !image

    Query Analytics for PostgreSQL¶

    In QAN we support two types of query sources, pg_stat_monitor and pg_stat_statements. Before this version, PGSS was the default query source, but now it’s PGSM.

    PMM 2.36 now supports pg_stat_monitor 2.0 (PGSM 2.0) in QAN, a powerful PostgreSQL query performance monitoring tool. By downloading this update, you will have access to the latest improvements and fixes covered by PGSM2, including:

    • Improved internal architecture that results in fewer lock acquisitions and increases performance by approximately 20%.
    • Support for PostgreSQL 15
    • Enhanced consistency with pg_stat_statements so that the pg_stat_monitor view has identical column names, columns, and data types as pg_stat_statements for every major version of PostgreSQL from versions 11 to 15.
    • A bucket status indication (done vs. current) eliminates the need for the tool to evaluate bucket status and facilitates accurate data display.
    • The generation of a unique ID for a query makes it easier to monitor query planning, execution, and performance regardless of version, database, user, or schema.
    • It has backward compatibility with your historical data (data collected by older PMM and PGSM)
    • More detailed histogram ranges
    • Security improvements
    • All previous PGSM versions are still supported

    Postgres 15

    You will probably need to grant more permissions to the user in PostgreSQL 15. An error message will appear in the pmm-agent log if more permissions are required. This behavior is pertaining to PostgreSQL and not PMM.

    You can use this query:

    CREATE USER pmm WITH SUPERUSER ENCRYPTED PASSWORD 'USERNAME';
    
    where USERNAME should be replaced by the user.

    Query Analytics for MongoDB¶

    MongoDB is conceptually different from relational database management systems, such as MySQL and MariaDB.

    Relational database management systems store data in tables that represent single entities. Complex objects are represented by linking tables.

    In contrast, MongoDB uses the concept of a document where all essential information for a complex object is stored in one place.

    Query Analytics can monitor MongoDB queries. Although MongoDB is not a relational database management system, you analyze its databases and collections in the same interface using the same tools.

    Sharing a link for Query Analytics¶

    To share a link for Query Analytics, use Copy Link. It copies the link to the clipboard with all the relevant information such as selected query, table page, selected filters, details tab, and time range. Thus, when you open the link, it will display the exact information.

    Ensure that you use Copy Link to copy the link instead of using the browser address bar or the standard Grafana functionality (to share a dashboard). Otherwise, Query Analytics might not display the exact information that existed while sharing the link. By default, Grafana uses a relative time range and not an absolute range, so it will have a different timestamp when this link is opened.

    !image

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.


    Last update: 2023-09-27
    Percona LLC, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better. Read more about Percona Cookie Policy.