Contact UsGet started

Monitoring and managing sessions and queries in the Command Center

Written by
Updated at February 12, 2024

Greenplum® Command Center provides the following functionality:

Check how you can utilize the Command Center in these use cases.

For more information about the statistics available to you from the Command Center, see Greenplum® Command Center.

Note

The Command Center is available in clusters with Greenplum® version 6.25 or higher.

Viewing information about sessions and queries

You can view a list of sessions and queries with details on them. For each session, you can analyze its history and queries made within it. For each query, you can analyze its execution plan and a JSON file with details.

To view information about sessions and queries:

  1. Go to the folder page and select Managed Service for Greenplum.

  2. Click the cluster name and go to the  Command center tab.

  3. Select what you want to view and go to relevant tab:

    • Current state for the current sessions and queries.
    • State history for sessions or queries at a given point in the past.

  4. Go to the Sessions or Queries section. In the State history tab, these are placed under the chart.

  5. To filter the session or query list, click Filters and select relevant parameters.

  6. To view the details of:

    • Sessions: Click the session name.
    • Queries: Click the key of the query you are running.

Viewing the resource consumption history for completed queries

The resource consumption history offers a number of system metrics. These show how a Managed Service for Greenplum® cluster was consuming resources to process queries at different points of time. You can also view a list of completed queries. Using this information, you can determine how to manage your cluster hosts' CPU and memory to process queries more effectively.

To view the resource consumption history for completed queries:

  1. Go to the folder page and select Managed Service for Greenplum.

  2. Click the cluster name and go to the  Command center → Usage history tab.

  3. Select the appropriate consumption metric:

    • CPU Time: Time it took the CPU resources to process the queries, seconds.
    • Peak memory: Maximum memory the cluster used to process a query during its lifetime.
    • Disk R: Memory used for data reads, bytes.
    • Disk W: Memory used for data writes to the DB, bytes.
    • Spill: Additional memory used for query execution.
    • Total time: Total memory used for query processing, bytes.

    Once you select the consumption metric, you will see a chart with detailed information and the list of queries. The chart will show the metric value, the name of the user who ran the query, and query execution time.

  4. To filter the results, click Filters and select relevant parameters.

Aborting the current session

To free up resources for sessions, you can abort a session, for example, an Idle one. To do this:

  1. Go to the folder page and select Managed Service for Greenplum.

  2. Click the cluster name and go to the  Command center tab.

  3. In Current state → Sessions, click in the appropriate line and select Terminate session.

    If you see Terminate query, select it and stop the query.

  4. Confirm stopping the session.

Terminating the current query

To free up resources for queries, you can terminate a query, for example, an Idle query within an idle session. To do this:

  1. Go to the folder page and select Managed Service for Greenplum.
  2. Click the cluster name and go to the  Command center tab.
  3. In Current state → Queries, click in the appropriate line and select Terminate query.
  4. Confirm terminating the query.

Examples

Identifying a current session that is consuming excessive resources

There might be cases when a user is no longer working with the database, but failed to terminate their session. In such cases, a session remains idle tying up cluster resources and degrading its performance. To identify and terminate such a session, do the following:

  1. Go to the folder page and select Managed Service for Greenplum.
  2. Click the cluster name and go to the  Command center → Current state tab.
  3. Filter the sessions by their Start time.
  4. Find the session in the Idle status with the earliest start time.
  5. Click the session number. You will see a page with the detailed information about this session.
  6. In the Query start time field of Session info, check the time of the latest query. If you see that no queries were run for a long time, this probably means the user is no longer working with the database, but did not close their session. You can terminate the session.
  7. In the top-right corner, click Terminate session.
  8. Confirm stopping the session.

Identifying CPU-intensive queries

At times you might observe unusual spikes in the CPU consumption. To determine which queries caused the spikes, do the following:

  1. Find out when the spike occurred:

    1. Go to the folder page and select Managed Service for Greenplum.

    2. Click the cluster name and go to  Command center → State history.

    3. Set the CPU usage filter.

    4. Use the graph to find out the peak CPU consumption time.

      Hover over the highest point on the graph curve. You will see a pop-up displaying the cluster state information for the selected time. The time of the spike is indicated in that window.

  2. Identify the CPU-intensive queries:

    1. Go to the Usage history tab.
    2. Set the time range based on the state history data.
    3. Group the queries by user, database, and query ID. This will group similar queries together.
    4. Filter the groups of queries by CPU time.
    5. Open the group with the highest CPU time value.
    6. Check the details of each query and adjust them if needed. You can also terminate a query that is still running.

Greenplum® and Greenplum Database® are registered trademarks or trademarks of VMware, Inc. in the United States and/or other countries.

Previous
User authentication rules
Next
Managing client processes and user sessions