Work with queries in ADQM Control

Elena Dvoryadkina

The Queries page in the ADQM Control web interface displays SQL queries executed in ADQM clusters connected to the monitoring system. On this page, you can get an overview of queries to cluster databases and view various query execution metrics — for example, to analyze query performance, amounts of read/written data, resource usage, errors if they have occurred during query execution, etc. Information on queries can also be presented in the form of graphs, which allow you to visualize the number of queries of different types, see how the total number of queries sent to the cluster is distributed among users, and track any queries that are too long (compared to the average).

The Queries page
The Queries page

At the top of the screen, you can set up the following filters to select which data the Queries page should display:

  • Cluster — ADQM cluster where queries were executed.

  • Time — time period when queries were launched. In the window that opens when you click the field, you can select an interval from the offered options on the Range tab or set a custom time range (at least 1 hour) on the Calendar tab.

  • Refresh — frequency of data updates.

Query history is always displayed on the Queries page according to these filters — it contains all queries run on the selected ADQM cluster during the specified time period. For each DDL query (with ON CLUSTER), it also includes the queries executed on each host of the cluster and displays the /* ddl_entry=query-num */ prefix before the main text of such queries.

 
You can also get a list of the longest-running queries or queries used the most memory in the Top 10 queries section on the Dashboard page.

Fields of the Queries table

The table with the list of queries on the Queries page can include the following fields.

Field Description

Query ID

Unique query identifier.

The column also shows an icon indicating the query status:

statuses good dark statuses good light — a query was successfully executed (the Query finished status);

statuses active dark statuses active light — a query is being executed at the current time (the Query started status);

statuses aborted dark statuses aborted light — an error occurred before a query execution started (the Exception before start status);

statuses failed dark statuses failed light — an error occurred during a query execution (the Exception while processing status).

statuses unfinished — a query execution was not completed for some reason (the Uncompleted status).

Query status can also be separately displayed in the Status column that allows filtering

Query text

Query text. For a long query, its text is truncated and when you hover the mouse cursor over the column cell, it is displayed as [query start] <…​> [query end]. You can see the full text of a query on a separate page — to open it, click the query ID in the Query ID column

Status

Current status of a query. Possible values:

  • Query started — a query execution is in progress;

  • Query finished — a query was successfully executed;

  • Exception before start — an error occurred before a query execution started;

  • Exception while processing — an error occurred during a query execution;

  • Uncompleted — a query execution was not completed for some reason, i.e. there is no information in the log about the successful completion or error of the query execution (for example, if a host was restarted during the query execution).

Host

Host where a query was run

Exception

Text of an error occurred before or during a query execution

Exception code

Code of an error occurred before or during a query execution

Frequency

How many times a query was executed in the cluster during the selected time period in the context of all filters currently applied on the page. Statistics are calculated for queries whose text completely matches (i.e. for parameterized queries, specific parameter values ​​are taken into account)

Databases

Names of databases accessed by a query (there may be multiple — for example, if a query uses JOIN)

Tables

Names of tables accessed by a query (there may be multiple — for example, if a query uses JOIN)

Used memory

Amount of memory consumed by a query

User

Name of an ADQM user who initiated a query execution. For queries executed on cluster hosts when running a distributed query, a user is not defined — the User column shows an empty string (-)

Time started

Query execution start time

Duration

Duration of a query execution or the current execution time (now - time_started) of a query that has not yet been completed (when a query is completed, a value in the Duration column will be replaced with the real query execution time once ADQM Control receives information about queries from ADQM the next time)

Average duration

Average query execution time. Statistics are calculated for queries whose text completely matches (i.e. for parameterized queries, specific parameter values ​​are taken into account)

Read bytes

Total amount of data read from all tables and table functions participated in a query. For distributed queries, this is the number of bytes read on all replicas — each replica sends its Read bytes value and the server-initiator of the query sums all received and local values

Result bytes

Amount of memory to store a query result

Result rows

Number of rows in the result of a SELECT query or number of rows in an INSERT query

Read rows

Total number of rows read from all tables and table functions participated in a query. For distributed queries, this is the total number of rows read on all replicas — each replica sends its Read rows value and the server-initiator of the query sums all received and local values

Written rows

Number of rows written by an INSERT query (for other queries, the field value is 0)

Written bytes

Amount of data written by an INSERT query (for other queries, the field value is 0)

Type

Query type. Possible values:

  • Write — for the CREATE, INSERT, ALTER, DROP, UNDROP, DELETE, MOVE, RENAME, OPTIMIZE, GRANT, REVOKE, BACKUP, RESTORE, SET, SYSTEM, KILL QUERY, BEGIN, COMMIT, ROLLBACK, SET TRANSACTION SNAPSHOT, ASYNC INSERT FLUSH, EXTERNAL DDL queries;

  • Read — for the SELECT, EXPLAIN, SHOW, DESCRIBE, CHECK, USE, EXISTS queries and queries that failed with the SYNTAX_ERROR and INVALID_GRANT errors.

To display/hide fields of the Queries table, click Customize table and select the desired fields in the drop-down list. The Query ID and Query text fields are always visible, they cannot be hidden.

Managing fields of the Queries table
Managing fields of the Queries table

Sort and filter data

In the column headers of the table with the list of queries, there are filters that you can use to select specific data. To open a filter, click the icon open filter default dark open filter default light. Filters are available in the following columns:

  • Query text — enter a truncated query text (as it is displayed in a tooltip that appears when you hover the mouse cursor over a column cell) or its fragment;

  • Databases, Tables — enter a name of a database or table (for rows with multiple databases or tables separated by commas in the Databases or Tables column, filtering works only if the search is performed by one item from a list of databases or tables in a cell);

  • Exception code — enter a numerical error code;

  • Host, User — you can enter names of multiple hosts or users, respectively (for queries initiated by a distributed query, filtering by an empty value in the User column is not supported);

  • Duration — you can enter the minimum and maximum query execution time (in seconds);

  • Status, Type — you can select a value (query status or type) from the drop-down list.

The icon filter dark focus filter light focus indicates that a filter by column values ​​is applied. To reset all filter conditions, click reset btn dark reset btn light.

Data sorting is available in the following columns: Exception (actually sorting is performed by error code), Exception code, Used memory, Time started, Duration, Read bytes, Result bytes, Result rows, Read rows, Written rows, and Written bytes. To change a sort order by column values, use the icon asc dark active default asc light active default or desc dark active default desc light active default in the column header.

Above the table with queries, there is a panel that shows the sorting (Sorted by) and filtering (Filtered by) conditions currently applied to data.

Current conditions for sorting and filtering data
Current conditions for sorting and filtering data

Query details

To view detailed information on a query, click its identifier in the Query ID field of the Queries table.

Selecting a query
Selecting a query

The Query details page that opens contains the full text of the selected query, as well as the description of an error if it has occurred during the query processing. The page also displays query metrics in the columns selected on the Queries page via Customize table.

The Query details page
The Query details page

Graphs

On the Queries page, you can build graphs to analyze queries sent to your ADQM cluster tables during a specified period of time, based on the following metrics:

  • Duration — query execution time;

  • User — number of queries sent by particular users;

  • Kind — number of queries of certain types.

To build a graph, click Show graph and select the desired graph type from the drop-down list.

Build a graph for query analysis
Build a graph for query analysis

In the drop-down menu to the right of the graph, you can change the graph type, as well as choose the level of detail (number of points on the graph): Low or High.

Change the graph type and select the level of detail
Change the graph type and select the level of detail

Duration

The Duration graph shows the overall trend of query execution duration allowing you to find queries that have taken a long time to complete (relative to other queries in the same time interval) and need to be analyzed.

Graph of queries by execution duration
Graph of queries by execution duration

This graph displays the following information on the execution time of queries sent to an ADQM cluster during a specified period of time:

  • 95th Quantile — 95th quantile of query execution time.

  • Average — average query execution time.

  • Extreme zone (area shaded in red if the Average line is above the 95th Quantile line) — time interval when there was one or more long queries relative to most others. For example, in the graph above, such a zone is observed in the time range from 6:00 to 7:00 — in the queries table, you can see that at 06:58:07 there was a query with a duration of 1m 17s, while all other queries were completed much faster (the queries in the table are sorted by the Duration column in descending order).

     
    Calm zone (area between the Average and 95th Quantile lines, shaded in green) — time interval when duration of all queries was comparable. A peak in this zone means that all queries in a given time interval have become longer to complete.

  • Query — number of queries.

  • No data (area shaded in gray) — time interval for which there is no data about queries according to the applied filters. The No data item appears in the legend only if the graph contains one or more time intervals with missing query information.

    Example of a graph with no data on queries
    Graph including No data time intervals
    Graph including "No data" time intervals

For the Duration graph, you can select the scale type: Normal or Log (the logarithmic scale can be more convenient for larger time intervals — for example, a month or a week).

User

The User graph allows you to see the number of queries sent to the cluster by each user and evaluate this number relative to the number of queries from other users and to the total of all queries. The graph is built using data on queries from users whose names are listed in the filter by the User column. You can also specify user names in the field to the right of the graph (the list of selected users is automatically synchronized with the filter by the User column).

Graph showing the number of queries from different users
Graph showing the number of queries from different users

Kind

On the Kind graph, you can see how many queries of different types were executed — select the required query types using the drop-down list to the right of the graph.

Graph showing the number of queries of different types
Graph showing the number of queries of different types

The graph is built according to the filters set in the table of queries. For example, the graph above shows the number of queries performed by the user_29 user for the selected time period.

Found a mistake? Seleсt text and press Ctrl+Enter to report it