Monitoring of commands

Daria Barysheva

The Monitoring → Commands page in the ADB Control web interface displays the SQL commands that are performed in the ADB clusters connected to the monitoring system. This page includes two tabs Online and History, each of which is described in detail below.

  • By default, ADB Control monitors (and displays on the Monitoring → Commands page) SQL commands longer than 5 seconds. Starting with ADB Control 4.3.2, you can modify this limit via the queryIdCache.moveToActiveTimeoutSeconds parameter on the agent side.

  • Starting with ADB Control 4.1.0, only top-level queries are fixed in the monitoring system by default. This excludes all queries that are called during the processing of the top-level query within functions, SPI (Server Programming Interface), etc. If you need to enable monitoring for inner queries, set the true value for the following GUCs:

    • adcc.monitor_utility_inner_queries — defines whether to monitor inner queries for utility commands like COPY, VACUUM, DDL, and so on.

    • adcc.monitor_inner_queries — defines whether to monitor inner queries for other commands.

List of commands

Online

The Monitoring → Commands → Online tab displays commands in the following statuses:

  • Queued — the commands on which information about the actual start of execution has not yet received.

  • Running — the commands that are currently running.

  • Cancelling — the commands that are currently cancelling (as a part of the transaction cancellation or termination).

At the top of the tab, the Current count field is located. It displays a total count of online commands in ADB clusters connected to the monitoring system.

adbc commands online dark
The Monitoring → Commands → Online tab
adbc commands online light
The Monitoring → Commands → Online tab

On the Monitoring → Commands → Online tab, there is a table with the following information on commands.

Field Description

Command ID

A unique command identifier, which includes:

  • cluster start timestamp;

  • session number;

  • command number within the current session;

  • hash code based on the cluster name (can be negative).

Command text

The first symbols of a command text. To view the full text (in case of long queries), hover the mouse over the field value

SQL ID

A common identifier for SQL commands with the same structure

Username

A name of the user who started the command

Status

A command status. Possible values are listed above

Database

A name of the database where the command is launched

Resource Group

A name of the resource group that is used for the command

Cluster

A name of the ADB cluster where the command is launched

Planner

A name of the query optimizer that is used to produce the query execution plan. Possible values:

  • GPORCA — GPORCA is used (optimizer = on).

  • Fallbacked — after the attempt to produce a plan via GPORCA (optimizer = on) failed, Postgres query optimizer is used.

  • Legacy — Postgres query optimizer is used (optimizer = off).

Submitted

The timestamp when the user submitted the command (in the DD/MM/YYYY HH:mm:ss format)

If necessary, you can add columns to the table. To do this, click Customize table and select column names in the section that opens. Currently you can add system metrics that are described below.

adbc commands online2 dark
Additional columns
adbc commands online2 light
Additional columns

Above the table with a list of commands, the Cluster filter is located. You can use this filter to select the ADB cluster and its databases for which you want to display data in the table. Initially all databases of the default cluster are selected.

adbc commands online3 dark
Filter by clusters and databases
adbc commands online3 light
Filter by clusters and databases

In the column headers of the table with a list of commands, there are filters that you can use to select specific data. To open a filter, click the openside dark openside light icon. For those columns where the set of possible values is limited (e.g. Status), you can select a value from the drop-down list. For some columns (e.g. Username), the search value should be entered. For columns that show date and time (e.g. Submitted), the time range can be selected from the calendar.

The filtered dark filtered light icon means that a filter is defined for the column. To reset all filters, click Reset.

History

The Monitoring → Commands → History tab displays commands in the following statuses:

  • Done — successfully completed commands.

  • Cancelled — cancelled commands (as a part of the transaction cancellation or termination).

  • Error — the commands during which some errors occurred.

  • Unknown — the commands, the final status of which is undefined. It is set on a timeout for queries in the Queued, Running, Cancelling statuses, for which there are no corresponding rows in the pg_stat_activity view.

adbc commands history dark
The Monitoring → Commands → History tab
adbc commands history light
The Monitoring → Commands → History tab

Most of the fields in the table with the list of commands on the History tab match the fields that are described above for the Online tab. Additionally, the following fields are available:

  • Ended — a command end timestamp in the DD/MM/YYYY HH:mm:ss format. The field is filled in both as a result of successful execution and in case of error or cancellation.

  • Run Time — a total command duration in hours, minutes, seconds.

Like on the Online tab, you have the ability to add additional columns to the table by clicking Customize table.

Above the table with a list of commands, there are filters that you can use to select specific data. In addition to the Cluster filter described above, the History tab contains the following filters:

  • Relation — filter by the relations that are used in commands. Enter a full value.

  • Schema — filter by the database schema, to which the relations used in commands belong. Enter a full value.

adbc commands history2 dark
Filters on the Monitoring → Commands → History tab
adbc commands history2 light
Filters on the Monitoring → Commands → History tab

Command details

To view the command details, click the command identifier (Command ID) in the table on the Monitoring → Commands → Online or Monitoring → Commands → History tab.

adbc commands online4 dark
Select a command
adbc commands online4 light
Select a command

The following page contains multiple sections that are described below.

At the top of the page, the following information is displayed:

  • The command unique identifier (see Command ID above). In the example below — 1695219736-11866-3—​209077013.

  • The command status (see Status above). In the example below — Running.

  • The command sequence number in ADB Control (starting with 1). The continuous numbering is used across all clusters. In the example below — 2683.

  • Transaction ID — the transaction identifier. To view transaction details, click the identifier.

  • Run time — the command duration in hours, minutes, seconds.

adbc command details1 1 dark
Header of the page with command details
adbc command details1 1 light
Header of the page with command details

Overview

The Overview section displays the general command information. In addition to the fields mentioned above, the following information is available:

  • Run time — the command duration in hours, minutes, seconds.

  • Est. progress — the progress of the command execution, which is calculated as a percentage of the time predicted by the query planner. The following formula is used:

    where:

    • Est.Cost(x) — the estimate of the plan node cost made by the planner.

    • NodeProgress(x) — the progress for each node of the query plan, which is calculated by the following formula: