Query Monitor

The Query Monitor view allows you to view information for all Greenplum Database server queries, including details about queries running, queued to run, and blocked by other queries. Users with Admin or Operator permission can see and cancel all users’ queries, or can move queries.

Query Monitor

If a Command Center administrator has enabled Query Monitor Guest Access, anyone able to access the Command Center web server can view the system status and query list on this page without signing in to Command Center. Anonymous users, however, cannot cancel queries or access any other Command Center features.

With the information available in this view, Greenplum Database administrators can easily:

  • Understand how the system is being used — both in real-time and trending over time.
  • Identify and diagnose problem queries while they are running, detect skew, find runaway queries, and so on.
  • Review and balance the query load on the system by better optimizing and scheduling the query load.
  • Cancel queries that disrupt system performance.
  • View and isolate queries with high CPU consumption.

The Query Monitor does not display queries executed by the gpmon user in the gpperfmon database.

Query Metrics

The Query Monitor table displays the following columns for queries.

Query ID
An identification string for the query. If the column is blank, no query ID has been assigned yet. In the Console, this looks like “1295397846-56415-2”. Command Center generates this ID by combining the query record’s tmid, ssid, and ccnt fields.

  • tmid is a time identifier for the query.
  • ssid is the session id.
  • ccnt is the number of the command within the session.

The status of the query. This can be one of the following:

  • Queued: the query has not yet started to execute
  • Running: execution has started, but is not yet complete
  • Blocked: the query is waiting for one or more other queries to release locks
  • Done: completed successfully
  • Cancelling: cancel request sent, cancel pending
  • Cancelled: terminated, no longer running
  • Idle Transaction: the transaction is open, but idle, for example, waiting while a user in an interactive session enters a statement

The Greenplum Database role that submitted the query.

The name of the database that was queried.

The resource group or resource queue that is managing the query. With Greenplum 6.8 or later, you can use the drop-down menu to reassign the query to a different resource group.

The time the query was submitted to the query planner.

Queued Time
The amount of time the query has been (or was) in queue awaiting execution.

Run Time
The amount of time since query execution began.

Spill Files
The total size of spill files created for the query. Greenplum Database creates spill files when there is insufficient memory to to execute the query in memory. See Managing Spill Files Generated by Queries for information about spill files.

CPU Time
Shows the amount of system CPU consumed by the individual query.

Using the Query Monitor Controls

  • If a query shows the Blocked status, use the tooltip to display the transaction that is blocking the query.

    Query Monitor

  • Click a column heading to sort the rows on that column in ascending or descending order.

  • Click the checkbox at the left of a row to choose a query to cancel or export. Click the checkbox in the heading row to choose all queries.

  • Click Cancel Query to cancel selected queries. A pop-up box prompts you to enter a reason. You can enter a message of up to 128 characters to display with the error message that is received by users whose queries are cancelled.

  • Click Export to download a comma-separated values (CSV) text file containing rows for the selected queries. When no queries are selected, all rows are exported. The default file name is spreadsheet.csv.

  • Click any query ID to see the Query Details, including metrics, the text of the query, and the query plan.

  • With Greenplum 6.8 or later, use the drop-down menu in the [Workload] column to reassign a query to a different resource group.