Skip to content

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:

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 occupies the left side of the dashboard. It lists filters, grouped by category. Selecting one reduces the Overview list to those items matching the 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

Overview Panel

To the right of the Filters panel and occupying the upper part of the dashboard is the Overview 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

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.

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

Ranges (numbers are in miliseconds):
0 - 3
3 - 10
10 - 31
31 - 100
100 - 316
316 - 1000
1000 - 3162
3162 - 10000
10000 - 31622
31622 - 100000

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: Not supported.

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

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


Last update: 2022-05-23