Greenplum® Command Center

Greenplum® Command Center is a tool that allows you to:

• Monitor query metrics and execution plans.
• Terminate individual sessions or queries.
• View execution history for completed queries.
• Analyze database queries.
• Perform database tuning.

The Command Center allows you to analyze the following session and query data:

• Current state
• State history
• Resourse consumption history

The Command Center also assigns query keys and IDs to identify and group queries.

For more information on how to work with the Command Center, see Monitoring and managing sessions and queries in the Command Center.

Current stateCurrent state

A current state is a real-time state of sessions or queries in a cluster. The Current state tab shows the users currently connected to the DB, queries being run, and amount of resources being consumed.

Let's assume that some users are performing resource-intensive computations within multiple sessions. These computations are still in progress by the time you need to run your regular workload.

On the Current state tab, you can sort the sessions by CPU time to see which ones are using the most CPU. You can also find and terminate the most resource-intensive sessions, freeing up resources for other operations. For more information on how to diagnose such cases, see Identifying a current session that is consuming excessive resources.

State historyState history

The state history shows snapshots of sessions or queries at specific points in the past. The state history allows you to determine which sessions or queries caused abnormal cluster performance.

For example, you might observe that your overnight ETL jobs took a longer time to complete than they normally would. Analyzing the state history helps you gain useful insights into the overnight sessions: what queries were run and how long they took, who was blocking the operations, and when a given query started executing.

Consumption historyConsumption history

The consumption history shows information about CPU time, Peak memory, and other resource consumption statistics at specific points in the past for completed queries.

Let's assume that in the state history you observe an unusually high CPU load over a certain period of time. To find out which queries caused the spike, you can refer to the consumption history. For a sample analysis of this scenario, see Identifying CPU-intensive queries.

The consumption history may contain a large number of completed queries. These queries are grouped together, and their collective statistics are available.

A group of queries is essentially a list which displays information about each query. The information displayed about a query depends on its duration:

• More than one hour: Complete information about a query is saved. This includes details, such as the start time of the query, the user who ran the query, and the amount of resources consumed at each Greenplum® segment.
• Less than one hour: Aggregate statistics similar to the pg_stat_statements PostgreSQL views are available. These only show total resource consumption at a certain point in time.

You can group queries by one or multiple parameters:

• User running the query.
• Database used.
• Query ID.
• Resource group.

You can also sort groups of queries by various parameters, e.g., CPU time. This allows you to find the group you need faster.

Query keysQuery keys

A key is a unique attribute assigned to a query when it is executed. Every time you run the same query, it receives a new key. This allows you to differentiate between multiple runs of the same query based on time.

Key template:

<session_ID>/<timestamp>/<query_number>

Where:

• session_ID: Session when the query was executed.
• timestamp: Greenplum® segment start time.
• query_number: Query sequence number within the session.

Sample key: 7247590/1701763297/5.

Query IDsQuery IDs

An ID is a non-unique hash value generated from the text of the query. An ID is calculated based on query duration. If it took more than one hour to complete the query:

1. All the numeric and string literals are removed from the query.
2. The rest of the query is used to calculate the hash. This hash value serves as the ID.

If it took less than one hour to complete the query:

1. The Command Center selects several other queries that can be grouped together with this particular query.

   Sometimes the same SQL query is executed repeatedly. In this case, the Command Center gathers information about multiple queries which differ only in the execution time.

2. All the numeric and string literals are removed from the query.

3. Then, minor differences between the queries within the group are identified and removed.

4. The rest of the query is used to calculate the hash. This hash value serves as the ID.

As a result, queries that ran for less than one hour and can form a single group receive the same ID. This ID is used to group queries.

Sample ID: 1f9c40bf.

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