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.

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 may experience delays due to network conditions. As a result, a small amount of data (up to 1 hour) will be buffered in the memory and reported when the connection is restored.

Filters Panel

• 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.

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

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.

  

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.

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.

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

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.

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

  

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

  

Adding and removing columns

• Metrics columns are added with the Add column button.

  

• 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.

  

• 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.

• 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.

  

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.

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:

Examples Tab

(For Query dimension.)

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

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.

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

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" ] } ])

Tables Tab

(For Query dimension.)

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

Plan Tab

(For Query dimension.)

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

Query Analytics (QAN) for PostgreSQL

QAN offers support for two types of query sources: pg_stat_monitor (PGSM) and pg_stat_statements (PGSS). While PGSS used to be the default query source, recent PMM versions have transitioned to PGSM. If PGSM is unavailable on your system, QAN will seamlessly fall back to PGSS and you will be notified of this in pmm-admin.

PMM has integrated support for pg_stat_monitor 2.0 (PGSM 2.0) into QAN starting with version 2.36. This robust tool enhances PostgreSQL query performance monitoring by providing access to the latest improvements and fixes included in 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.

QAN under the hood

To understand how metrics are calculated and to learn more details about QAN, see

QAN under the hood.

Get expert help

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.

Community Forum Get a Percona Expert