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 (starting with 4.7.5 version, you can edit them in the ADB Control web UI):

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

The Monitoring → Commands → Online tab
The Monitoring → Commands → Online tab
The Monitoring → Commands → Online tab
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)

Tags

The command tags

Run time

A total command duration in hours, minutes, seconds

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.

Additional columns
Additional columns
Additional columns
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.

Filter by clusters and databases
Filter by clusters and databases
Filter by clusters and databases
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.

The Monitoring → Commands → History tab
The Monitoring → Commands → History tab
The Monitoring → Commands → History tab
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 field is 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.

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.

Filters on the Monitoring → Commands → History tab
Filters on the Monitoring → Commands → History tab
Filters on the Monitoring → Commands → History tab
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.

Select a command
Select a command
Select a command
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.

Header of the page with command details
Header of the page with command details
Header of the page with command details
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:

      where:

      • ActualRows — an actual number of processed table rows (tuples).

      • EstimatedRows — the planner’s assumption of the number of rows (tuples) that will be processed for the current plan node.

The Overview section
The Overview section
The Overview section
The Overview section

If the command fails (has the Error status), the error message can be found below the Overview section table.

Command error
Command error
Command error
Command error

Performance

The Performance section displays the statistics of system resource consumption by the selected command. The metrics available for monitoring are listed in System metrics collected for commands.

At the top of the Performance section, the average metric values with skews for all cluster segments are displayed.

Display average metric values for a cluster
Display average metric values for a cluster
Display average metric values for a cluster
Display average metric values for a cluster

You can select an alternative display of metrics by activating the switcher that is located under the Performance section. The result is a table where not only the current value but also a detailed description is displayed for each metric.

Display detailed metric descriptions
Display detailed metric descriptions
Display detailed metric descriptions
Display detailed metric descriptions

In the bottom part of the Performance section, the metric values are shown separately for each cluster segment, including the master segment seg-1 (where Segment ID equals to -1).

Fields of the table with segment metrics
Field Description

Segment ID

The content identifier of a segment (or master) instance. Corresponds to gp_segment_configuration.content. For more information, see the System catalog tables → gp_segment_configuration section of the Tables article

Hostname

A host name

CPU

The amount of CPU consumed by the segment (percentage). Based on the metric values for all segments, the average value CPU avg usage % is calculated

RAM

The amount of RAM consumed by the segment (in bytes). Based on the metric values for all segments, the average value RAM average is calculated

Read (per sec)

The amount of data read per second by the segment (in bytes). Based on the metric values for all segments, the average value Read avg per sec is calculated

Write (per sec)

The amount of data written per second by the segment (in bytes). Based on the metric values for all segments, the average value Write avg per sec is calculated

Virtual memory

The amount of virtual memory allocated by the segment (in bytes). Based on the metric values for all segments, the average value Virtual memory average is calculated

Spill size

Size of spill files (in bytes). Filled in if the Spill metrics collection mode value is set to Cluster and segment on the Configuration → General → Metrics tab

Spill files number

Number of spill files. Filled in if the Spill metrics collection mode value is set to Cluster and segment on the Configuration → General → Metrics tab
Display metric values for each segment
Display metric values for each segment
Display metric values for each segment
Display metric values for each segment

Starting with ADBM 2.1.2 you can filter and sort data in the table displayed above as follows:

  • Filters are available for the Segment ID and Hostname columns. To apply a filter, click the openside dark openside light icon in the column header and enter the value to search. The filtered dark filtered light icon means that the filter is defined for the column. To reset all filters, click Reset.

  • Data sorting is available for all columns except Hostname. To change a sort order for a column, click the arrow up dark arrow up light or arrow down dark arrow down light icon in the column header.

IMPORTANT

System metrics are collected from ADB segments by ADB Control agents only for commands longer than 15 seconds. Due to this fact, the section with metrics will be empty for the commands that complete faster.

Command text

The Command text section contains the SQL command text, which you can copy by clicking copy hover dark copy hover light in the top right corner of the code sample.

The Command text section
The "Command text" section
The Command text section
The "Command text" section

Query plan sections

Below the Command text section, there are several tabs that display query execution details:

  • Plan & progress

  • Text

  • Relations

The first tab Plan & progress contains a graphical view of the query plan in the form of a tree. Execution is done from child nodes to the root. Each plan node displays a type of the performed operation and the following information:

  • For DB queries in progress:

    • The actual number of processed tuples at the current moment. This value is being refreshed.

    • The actual node execution time of a slice (group of nodes) at the current moment. This value is being refreshed and displayed on all nodes that belong to the same slice.

  • For finished DB queries:

    • The total node execution time.

    • The total number of processed tuples.

    • The ratio of the node execution time to the total query execution time (in percentage).

In the top right corner of each plan node, there is an icon corresponding to its status:

  • running plan — the plan node is being executed.

  • finished plan — the plan node is finished.

  • error plan — some error occurred when running the slice that contains the current plan node. The error status is available starting with ADB Control 4.10.3. Plan nodes in the error status are highlighted in red.

  • unreachable plan — the plan node is unreachable. This status is used for plan nodes that are above the error slice in the query plan tree, and also for those nodes whose parent node appeared in the error slice in the initialization status (before execution). The unreachable status is available starting with ADB Control 4.10.3.

Plan nodes that belong to the same slice are highlighted in the same color and have the same status.

The Plan and progress section: successful execution
The "Plan & progress" section: successful execution
The Plan and progress section: successful execution
The "Plan & progress" section: successful execution
The Plan and progress section: execution errors
The "Plan & progress" section: execution errors
The Plan and progress section: execution errors
The "Plan & progress" section: execution errors

When you click on a query plan node, a panel with additional information appears on the right side of the screen.

Plan node information
Field Description

Cost

A plan node cost

Schema

A schema name. Can be used to distinguish eponymous tables that belong to different schemas

Relation

A name of the relation that is used in the current plan node

Row skew

A data skew indicator. The difference between 1 and the ratio of the average number of tuples obtained from the segments to the maximum:

Est.tuples

An estimated number of table rows (tuples) that should be processed or scanned by the plan node

Actual tuples

An actual number of processed table rows (tuples)

Prediction accuracy

A ratio of the actual number of processed tuples to the estimated number (percentage). The value that exceeds 100% indicates that the planner has made a wrong assumption about the number of tuples that should be processed/scanned when executing the plan node

Operation keys

Additional conditions that are used to perform specific operations. For example, Hash Key, Join Filter, Merge Key, Filter

Execution time

A plan node execution time in hours, minutes, seconds
Display the plan node information
Display the plan node information
Display the plan node information
Display the plan node information

The next tab Text outputs a text presentation of the query plan, which is the result of the EXPLAIN command. You can copy the text by clicking the copy hover dark copy hover light icon in the top right corner of the code sample.

The Text tab
The Text tab
The Text tab
The Text tab

By clicking PlanChecker, you can open the plan text in the web interface of the PlanChecker service to view detailed information on possible performance problems that can occur during the current SQL query execution.

PlanChecker
PlanChecker
PlanChecker
PlanChecker

The last tab Relations lists the database relations that are used in the query. For each relation, the name of the corresponding schema is displayed.

NOTE

To view audit details for the specific relation, you can click the row corresponding to that relation in the table above. This functionality is available for ADB Control users with appropriate permissions (see View secured relation audit in the Authorization article).
The Relations tab
The Relations tab
The Relations tab
The Relations tab

System metrics collected for commands

Both Online and History tabs allow you to view the statistics of the system resource consumption by commands (on the Performance tab in the command details). The metrics available for commands are listed below.

NOTE

  • The Online tab displays current metric values, whereas the History tab shows metric values at the end of the command execution.

  • The following metrics are not calculated on the Online tab: Spill files max and Spill files skew.

  • The calculation of average and skew values does not take into account the master segment (where Segment ID is equal to -1).
Command metrics
Group Metric Description

CPU

CPU usage total

The total CPU consumption across cluster segments (in seconds)

CPU avg usage %

The CPU percent average for all segment processes executing the query. Calculated as the result of dividing the total CPU consumption on all cluster segments (excluding the master and standby) by the total number of processes processing the query on segment hosts (excluding the master and standby)

CPU skew

The indicator that displays the CPU consumption skew across cluster segments (percentage). A value other than zero indicates that one of the segments uses more CPU than others

RAM

RAM average

The average amount of RAM consumed across cluster segments (in bytes). The RAM consumption at any given moment is calculated based on the operating system metric rss (Resident Set Size)

RAM skew

The indicator that displays the RAM consumption skew across cluster segments (percentage). A value other than zero indicates that one of the segments uses more RAM than others

Virtual memory

Virtual memory average

The average amount of virtual memory allocated across cluster segments (in bytes). The virtual memory consumption at any given moment is calculated based on the operating system metric vsize (Virtual Memory Size)

Virtual memory skew

The indicator that displays the virtual memory skew across cluster segments (percentage). A value other than zero indicates that one of the segments uses more virtual memory than others

Read

Read total

The total amount of data read across cluster segments (in bytes). The following formula is used for calculation:

Read avg per sec

The average amount of data read across cluster segments per second (in bytes). The data read rate at any given moment is calculated based on the I/O Read information from /proc/[pid]/stat as a delta between the current and previous values

Read skew

The indicator that displays the data reading skew across cluster segments (percentage). A value other than zero indicates that one of the segments reads more data from a disk than others

Write

Write total

The total amount of data written across cluster segments (in bytes). The following formula is used for calculation:

Write avg per sec

The average amount of data written across cluster segments per second (in bytes). The data write rate at any given moment is calculated based on the I/O Write information from /proc/[pid]/stat as a delta between the current and previous values

Write skew

The indicator that displays the data writing skew across cluster segments (percentage). A value other than zero indicates that one of the segments writes more data to a disk than others

Spill files

Spill files host

The total amount of spill files on cluster hosts at the current moment (in bytes)

Spill files abs host

The maximum value of Spill files host detected on cluster hosts during the command execution (in bytes)

Spill files abs seg

The maximum amount of spill files produced by one cluster segment during the command execution (in bytes)

Spill files skew

The indicator that displays the spill file amount skew (in percents) at the current moment. A value other than zero indicates that one of the segments uses more spill files than others

Spill files abs seg skew

The indicator that displays the spill file amount skew (in percents) at the moment when the Spill files abs seg value was fixed. The following formula is used for calculation:
Found a mistake? Seleсt text and press Ctrl+Enter to report it