Using the Greenplum® command center

Navigate to the folder dashboard and select Yandex MPP Analytics for PostgreSQL.

Click the cluster name and navigate to the Command center tab.

Select what you want to view and navigate to relevant tab:

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

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

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

To view details for:

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

For session and query parameters, see Greenplum® Command Center parameters.

Navigate to the folder dashboard and select Yandex MPP Analytics for PostgreSQL.

Click the cluster name and navigate to Command center → Usage history.

Select the consumption metric you need:

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

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

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

Navigate to the folder dashboard and select Yandex MPP Analytics for PostgreSQL.

Click the cluster name and navigate to the Command centertab.

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

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

Confirm stopping the session.

1. Click the session number. The session info page will open.
2. Compare the computing load and I/O load metrics (CPU time, Peak memory, Spill, Disk W, Disk R, Net recv, Net sent) against the overall load charts for the cluster or its individual hosts.

Navigate to the folder dashboard and select Yandex MPP Analytics for PostgreSQL.

Click the cluster name and navigate to Command center → Current state.

Navigate to the Sessions tab.

Enable displaying only active sessions by turning off all status buttons except Active.

Filter the sessions by Start time.

Find a long-lived session with high values ​​in the CPU time and Peak memory columns. Click its number to open its info page.

Analyze the CPU time, Peak memory, Spill, Skew, Net sent, Net recv, and Interconnect retransmits values.

• If you see high values ​​for Net sent and Net recv, and a non-zero value for Interconnect retransmits:

  1. Navigate to the Queries tab.
  2. Apply the SSID: filter by specifying the transaction ID of the selected session.
  3. Sort queries by the Key of running query column in descending order.
  4. Open execution plans for multiple queries.
  5. If you see Gather or Gather Merge after Sort, Aggregate, or Distinct, move GROUP BY/DISTINCT/ORDER BY to subqueries.
  6. If you get broad selections with a full set of columns, limit the results with the help of LIMIT or pagination, plus select only the columns you need and apply filters at the early stages of your query.

• If you see a non-zero value for Spill:

  1. Navigate to the Queries tab.
  2. Apply the SSID: filter by specifying the transaction ID of the selected session.
  3. Sort queries by the Key of running query column in descending order.
  4. Open execution plans for multiple queries.
  5. If you see subqueries with outer row dependency (EXISTS or IN with correlation) and the execution plan contains the SubPlan or InitPlan nodes, decorrelate such subqueries.
  6. If you see sorting or materialization followed by WindowAgg over large selections, apply pre-aggregation or filtering, and exclude unnecessary columns before applying the window functions.
  7. If you see Sort or Distinct at different nesting levels, reduce the number of such operations and their nesting depth.

• If you see high CPU time or Peak memory values ​​with non-zero Skew values:

  1. Navigate to the Queries tab.
  2. Apply the SSID: filter by specifying the transaction ID of the selected session.
  3. Sort queries by the Key of running query column in descending order.
  4. Open the execution plans for several queries and check how the joins are executed:
     • If joins are based on columns different from the actual table distribution key, rewrite your query.
     • If you are joining large sets but filters are either missing or applied after JOIN, use filtering in subqueries before JOIN.

• Status; usually Active or Idle transaction.
• Start time and Status changed values.
• Amount of consumed resources.
• Number of blocked sessions.
• Query text.

1. Make sure CPU time is not increasing and the Reason for wait field is empty.
2. In the top-right corner, click Terminate session.
3. Confirm stopping the session.

Find out when the spike occurred:

1. Navigate to the folder dashboard and select Yandex MPP Analytics for PostgreSQL.

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

3. Set the CPU usage filter.

4. Use the chart to find out when CPU consumption became abnormally high.

   Hover over the highest point on the chart curve. You will see a pop-up displaying the cluster state details for the selected time point. The window will include the time point when the spike occurred.

Identify the CPU-intensive queries:

1. Navigate 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 query groups by CPU time.
5. Open the group with the highest CPU time value.
6. Examine the SQL text of your queries and their execution plans to figure out the cause of high CPU consumption.

Find the approximate time period when network issues and errors were observed, for example:

• Cluster connection failure or slow response complaints.
• Network anomalies and errors based on cluster logs and monitoring data.

Establish the cause of the errors:

1. Navigate to the folder dashboard and select Yandex MPP Analytics for PostgreSQL.
2. Click the cluster name and navigate to Command center → State history.
3. Set the time range during which errors were observed.
4. In the drop-down list above the chart, select Connections and then Net usage. Compare the charts.
5. If unusually large Net usage values ​​were observed, abnormal network activity is the most likely cause.
6. If unusually large Connections values ​​were observed, a spike in connections is the most likely cause.

If the errors are caused by abnormal network activity:

1. Navigate to the Usage history tab.
2. Set the time range based on the state history data.
3. Select Group by: → user.
4. Filter the query groups by the Net sent and Net recv columns.
5. Find the user with the highest values ​​in these columns. Filter the query groups by this user.
6. Select Group by: → Query ID and disable grouping by user.
7. Find the queries that generated the most traffic. Save the text of these queries and their start time.
8. Navigate to the Sessions tab.
9. Use the query text-based search to find the queries of interest.
10. Identify the source of traffic using the Application column.
11. Based on your analysis, tweak the applications generating abnormal network activity:
    • Limit the amount of upload data.
    • Use materialized views or temporary tables.
    • Optimize table distribution (DISTRIBUTED BY) and update table statistics prior to large inserts.
    • Review the ETL pipeline architecture.

If the errors are caused by a spike in connections:

1. Go to the Sessions tab below the chart.
2. Filter the sessions by Start time.
3. On the chart, select a time point with the highest Connections values. Use the At time: section and the < > arrows to set the exact time point.
4. Use the User and Application name: filters to compare the number of new sessions per second for each source.
5. If one source creates many more sessions than the others:
   • Check whether the application is reusing its connections.
   • Set the interval between retries and total number of attempts for reconnections.
6. Optionally, edit the connection manager settings based on your analysis.

Navigate to the folder dashboard and select Yandex MPP Analytics for PostgreSQL.

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

Navigate to the Queries tab.

Select a time point when issues were reported by the monitoring data. Use the At time: section and the < > arrows to set the exact time point.

Filter the queries by the Query state column.

Find queries with the Canceled or Error status.

Establish the query sources based on the User and Application columns. Optionally, use the User: and Application name: filters.

If one source generates significantly more canceled and failed queries than the others:

• Check and, optionally, optimize business logic and the structure of your SQL queries.

  Pay special attention to frequent full selections, lack of data filtering, redundant table joins, or nested subqueries.

• Set the interval between reconnections and the total number of attempts.

Optionally, use the connection manager to limit the number of concurrent active connections and balance the load between clients.

The optimal parameters depend on the number of segments and cluster resources.