What's New
Function Overview
Product Bulletin
- Product Notice
- Version Support Notes
Service Overview
- GaussDB(DWS) Infographics
- What Is GaussDB(DWS)?
- Data Warehouse Types
- Data Warehouse Flavors
- Advantages
- Application Scenarios
- Functions
- Concepts
- Related Services
- Security
- GaussDB(DWS) Permissions Management
- GaussDB(DWS) Access
- Restrictions
- Technical Support
- Service Quotas
- GaussDB(DWS) Technical Specifications
Billing
- GaussDB(DWS) Billing Overview
- Billing Modes
- Item
- Billing Examples
- Billing Mode Change
- Renewal
  - Overview
  - Manual Renewal
- Bills
- Arrears
- Stopping Billing
- Cost Management
- Billing FAQs
Getting Started
- Checkpoint Vehicle Analysis
- Supply Chain Requirement Analysis of a Company
- Operations Status Analysis of a Retail Department Store
- Creating a Time Series Table
- Best Practices of Hot and Cold Data Management
- Best Practices for Automatic Partition Management
- Creating a Cluster and Connecting to It
- Using CDM to Migrate MySQL Data to the GaussDB(DWS) Cluster
- Using DLI Flink Jobs to Write Kafka Data to GaussDB(DWS) in Real Time
- Basic SQL Operations
- Database Quick Start
- Getting Started with Common Practices
User Guide
- Using GaussDB(DWS)
- Preparations
- Creating a GaussDB(DWS) Cluster
- Connecting to a GaussDB(DWS) Cluster
- Creating a GaussDB(DWS) Database and User
- Migrating Service Data to a GaussDB(DWS) Cluster
  - Migrating Data to a GaussDB(DWS) Cluster Using GDS-Kafka
  - Data Source Management
- GaussDB(DWS) Cluster Data Security and Encryption
- GaussDB(DWS) Cluster Management
- GaussDB(DWS) Cluster O&M
Best Practices
- Import and Export
- Data Migration
- Data Analytics
- Decoupled Storage and Compute
  - DWS 3.0 Decoupled Storage and Compute Usage Suggestions and Performance Optimization
- Data Development
- Database Management
- Performance Tuning
  - Optimizing Table Structure Design to Enhance GaussDB(DWS) Query Performance
  - Analyzing SQL Statements That Are Being Executed to Handle GaussDB(DWS) Performance Issues
- Cluster Management
  - Binding Different Resource Pools to Two Types of Jobs to Balance Load for GaussDB(DWS)
  - Scaling Options for GaussDB(DWS) with a Coupled Storage-Compute Architecture
Data Migration and Synchronization
- Data Migration to GaussDB(DWS)
- Importing Data
- Full Database Migration
  - Using CDM to Migrate Data to GaussDB(DWS)
  - Using DSC to Migrate SQL Scripts
- Real-time Import
  - Using DRS to Import Data to GaussDB(DWS)
  - Real-time Data Import From Kafka GaussDB(DWS)
- Metadata Migration
  - Using gs_dump and gs_dumpall to Export Metadata
  - Using gs_restore to Import Data
- Exporting Data
- Other Operations
Developer Guide
- Standard Data Warehouse (9.1.0.x)
- Standard data warehouse (8.1.3.x)
- Hybrid Data Warehouse (9.1.0.x）
- Hybrid Data Warehouse (8.1.3.x）
- Historical Versions
SQL Syntax Reference
- SQL Syntax Reference (9.1.0.x)
- SQL Syntax Reference (8.1.3.x)
- Historical Versions
Performance Tuning
- Overview of Query Performance Optimization
- Query Execution Process
- SQL Execution Plan
- SQL Optimization Guide
- Optimization Cases
- SQL Execution Troubleshooting
- query_band Load Identification
- Common Performance Parameter Optimization Design
Tool Guide
- Overview
- Downloading Related Tools
- gsql
- Data Studio
- GDS
- DSC
- DataCheck
- DWS-Connector
- Server Tool
API Reference
- Before You Start
- API Overview
- Calling APIs
- Getting Started
- API Description
- Application Cases
  - Using Postman to Call the API for Creating a Cluster
  - Using Postman to Call the API for Creating a Snapshot
- Introduction
- Appendix
SDK Reference
- SDK Overview
FAQs
- Top FAQs
- Product Consulting
- Database Connections
- Data Migration
- Database Usage
- Cluster Management
- Account Permissions
- Database Performance
- Backup and Restoration
  1. Why Does It Take a Long Time to Create an Automated Snapshot in GaussDB(DWS)?
  2. Does a GaussDB(DWS) Snapshot Have the Same Function as an EVS Snapshot?
Troubleshooting
- Database Connections
- JDBC/ODBC
- Data Import and Export
- Database Parameter Modification
- Account/Permission/Password
- Cluster Performance
- Cluster Exceptions
  - The Disk Usage Alarm Is Frequently Generated
- Database Use
Videos
Performance White Paper
- Overview
- Test Result
  - TPC-H Single Query Test
  - TPC-DS Single Query Test
- Test Methods
- Appendixes
  - TPC-H Test Sets
  - TPC-DS Test Sets
Technical White Paper
- GaussDB(DWS)
- Platforms and Technical Specifications Supported by GaussDB(DWS)
  - Technical Specifications
- GaussDB(DWS) Core Technologies
- GaussDB(DWS) Tools
  - Client Tools
  - Database Monitoring Tool
- External APIs
Error Code Reference
- 8.2.0 and earlier versions
  - Management Console Error Code
  - Data Warehouse Service Error Codes
- 8.2.1 or later versions
Glossary
More Documents
- User Guide
- API Reference (ME-Abu Dhabi Region)
- Developer Guide (ME-Abu Dhabi Region)
- SQL Syntax Reference (ME-Abu Dhabi Region)
- Tool Guide (ME-Abu Dhabi Region)
- Error Code Reference (ME-Abu Dhabi Region)
  - Management Console Error Code
- User Guide (Paris Region)
- API Reference (Paris Region)
- Developer Guide (Paris Region)
- SQL Syntax Reference (Paris Region)
- Tool Guide (Paris Region)
- Error Code Reference (Paris Region)
  - Management Console Error Code
- User Guide (Kuala Lumpur Region)
- API Reference (Kuala Lumpur Region)
- Developer Guide (Kuala Lumpur Region)
- SQL Syntax Reference (Kuala Lumpur Region)
- Tool Guide (Kuala Lumpur Region)
- Error Code Reference (Kuala Lumpur Region)
  - Management Console Error Code
General Reference
- Glossary
- Service Level Agreement
- White Papers
- Endpoints
- Permissions

On this page

Show all

Help Center/ GaussDB(DWS)/ User Guide/ GaussDB(DWS) Cluster O&M/ Monitoring and Diagnosing a GaussDB(DWS) Cluster

Monitoring and Diagnosing a GaussDB(DWS) Cluster

Updated on 2025-03-03 GMT+08:00

View PDF

Context

GaussDB(DWS) offers a multi-dimensional optimization and diagnosis function to enhance self-O&M capability for tenants. It helps identify slow and abnormal SQL statements to ensure the smooth and efficient operation of user services. This allows you to perform historical query diagnosis analysis, real-time query analysis, real-time session analysis, and table diagnosis analysis on clusters.

Historical Query Analysis: offers the ability to diagnose exceptions by monitoring the top SQL statements in the historical data. It presents SQL trend statistics and analysis curves that showcase the historical execution trend of SQL statements. It identifies top SQL exceptions, detects slow SQL statements that consume significant resources and have long execution times, and displays the count of abnormal SQL statements for each type. It also filters out these abnormal SQL statements. Additionally, it provides a one-click diagnosis for individual SQL statements, including checking the statement, diagnosing the execution plan, and visualizing the results. This assists users in analyzing the execution plans and performance impact of their SQL statements.
Real-Time Query Analysis: provides analysis of real-time resource consumption and query plans, including information on concurrency and user distribution, lock waiting jobs, slow SQL statements, SQL queues, service concurrency trend chart, real-time queries, and real-time data aggregation.
Real-Time Session Analysis: provides analysis of real-time session query details, including the number of all sessions, user distribution, idle sessions, active sessions, the number of jobs in the CCN queue, session quantity trend chart, real-time sessions, and real-time data aggregation.

NOTE:

This feature is supported only by clusters of version 8.1.3 or later.
The real-time query monitoring function is disabled by default. To enable it, choose Settings > Monitoring, click Monitoring Collection, and enable Real-Time Query Monitoring. For details, see Monitoring Collection. Exercise caution when enabling this as it may generate a large amount of data.
The historical query monitoring function is disabled by default. To enable it, choose Settings > Monitoring, click Monitoring Collection, and enable Historical Query Monitoring. For details, see Monitoring Collection. Exercise caution when enabling this as it may generate a large amount of data.

Accessing the Optimization Diagnosis Page

Log in to the GaussDB(DWS) console.
Choose Monitoring > Optimization Diagnosis on the navigation pane.
Select the cluster to be optimized from the drop-down list in the upper left corner of the page. Click Historical query analysis, Real-time query analysis, and Real-time session analysis as needed.
- Toggle on Hide System Query in the upper right corner to hide the system user queries.
- To automatically refresh data, toggle on Auto Refresh in the upper right corner. You can also set the desired refresh interval by specifying Refresh Interval (s) for the data to update at regular intervals.
  Figure 1 Optimization Diagnosis page

Historical Query Analysis

This Historical query analysis page contains three tab pages: General trend of historical queries, SQL quality trends, and SQL runtime trends. On this page, you can find an overview of SQL quality, detailed information about the top historical SQL queries, perform one-click diagnosis of the execution plan and SQL statements for the top historical SQL statements, and get a summary of historical data.

Figure 2 Historical query analysis
Click to enlarge

General trend of historical queries: displays aggregated statistics by different dimensions (cluster, user, instance, application name, and resource pool). You can also check the number of SQL statements executed per minute within a specified period on this tab page.
SQL quality trends: collects statistics on the number of SQL statements of each exception type within a specified time range.
SQL runtime trends: collects statistics on the historical SQL execution duration (minimum, average, and maximum durations) of a cluster.
SQL statement performance overview: displays the number of abnormal SQL statements of each type. You can click different cards to filter the exception types.
Exception types include execution plan pushdown failures, CPU usage exceeding 100,000 seconds, the presence of NOT IN subqueries, query duration exceeding 1 hour, memory usage exceeding 10 GB, disk space usage exceeding 50 GB, and the number of streaming tasks exceeding 50.
NOTE:

For how to customize exception thresholds for different cluster, contact technical support. Exception diagnosis rules can be flexibly configured based on the historical top SQL fields in a GaussDB(DWS) cluster.
Historical query details: displays the details of all historical queries. You can search by criteria or sort all fields to quickly find desired information. Click the setting button in the upper right corner of the list to display or hide columns.
- View Details: Click View Details in the Operation column of the row that contains the target top SQL statement to view its details, including the basic information, real-time resource consumption during execution, complete SQL statement, and query plan.
- One-click diagnosis: Click One-click diagnosis in the Operation column of the row that contains the target top SQL statement to view its static check and execution plan diagnosis result and visualize the result.
  - Planned diagnostics: parses the execution plan character string based on the execution plan format and diagnoses the execution plan in the historical top SQL tables. A visualized tree chart is used to display information about each node, such as the execution duration, type, and number of scanned rows.
    The exception types that can be identified include redistribution exceptions, estimation exceptions, computing skews, partition scanning exceptions, and cross-logical cluster queries.
    NOTE:
    
    To check the execution duration of each step in the execution plan, set resource_track_level to perf.
    
    Figure 3 Plan visualization
    
    Figure 4 Plan diagnosis
  - SQL Diagnosis: performs a static check on user-written SQL statements based on SQL development specifications. It analyzes non-compliant SQL statements and provides rectification suggestions.
    NOTE:
    
    The SQL development specifications are created based on the usage of GaussDB(DWS) and are only meant for reference. The provided specifications may need to be revised according to actual usage. For details, see GaussDB(DWS) Development Design Specifications.
    
    Figure 5 SQL check results

Data summary: aggregates historical top SQL data by various dimensions (database, user, resource pool, application name, instance, and unique SQL ID) and displays the number of SQL statements and their resource consumption.
Click View Details to view the resource consumption details of a particular object.

Real-Time Query Analysis

This page provides information on real-time concurrency and user distribution, lock waiting jobs, slow SQL statements, SQL queues, service concurrency trend chart, real-time queries, and real-time data aggregation.

Figure 6 Real-time query analysis
Click to enlarge

Real-time Concurrency: displays the number of SQL statements currently running in the cluster and the concurrency distribution for each user.
Lock waiting jobs: indicates the number of SQL statements waiting for locks in the cluster.
Slow SQL statements: displays the number of SQL statements whose duration exceeds 60s.
SQL queues: displays the total number of all queuing SQL statements in the clusters, resource pools, and CCNs.
Service concurrency: refers to the number of services running concurrently in a cluster during a specific time period. This information is displayed in a line chart, making it easier to collect and compare statistics. You can click User, Node, or Resource Pools to view this information in different dimensions.
Real-time query: allows you to view all the details of running queries in the cluster. You can sort and filter displayed items to help you quickly locate the information you need. Click the setting button in the upper right corner of the list to display or hide columns.
- Click termination in the Operation column to terminate a real-time top SQL query.
- Select multiple real-time top SQL queries and click Terminate Query above the list to terminate them.
- Click Execute Plan in the Operation column to view a visualized tree chart of real-time top SQL execution plans, including the execution duration, type, and number of scanned rows of each node.
NOTE:

The fine-grained permission control function is added. Only users with the operate permission are able to terminate queries. For users with the read-only permission, the Terminate Query button is grayed out.
Real-time data aggregation: summarizes the top SQL query data in real-time, categorizing it by different dimensions such as node, query ID, user, and resource pool. It provides information on the number of running SQL statements, queuing SQL statements, slow SQL statements, complex statements, lock waiting jobs, and simple statements.

Real-Time Session Analysis

This page displays the number of real-time sessions and their distribution among users, the number of idle and active sessions, the number of jobs in the CCN queue, session quantity trend chart, real-time session list, and real-time data aggregation.

Figure 7 Real-time session analysis
Click to enlarge

Sessions: displays the total number of real-time sessions in the cluster and their distribution among users.
Total Idle Sessions: refers to the total number of idle sessions in the cluster.
Total Active Sessions: refers to the total number of active sessions in the cluster.
CCN queued jobs: displays the total number of jobs in the CCN queue.
Sessions: refers to the number of sessions in a cluster during a specific time period. This information is displayed in a line chart, making it easier to collect and compare statistics. You can click User, Node, or Resource Pools to view this information in different dimensions.
Sessions: allows you to view the real-time information about all running sessions. You can sort and filter displayed items to help you quickly locate the information you need. Click the setting button in the upper right corner of the list to display or hide columns.
- Click termination in the Operation column to terminate a real-time session.
- Select multiple sessions and click Terminate Session above the list to terminate them.
NOTE:

The fine-grained permission control function is added. Only users with the operate permission are able to terminate sessions. For users with the read-only permission, the Terminate a Session button is grayed out.
Real-time data aggregation: summarizes session data by various dimensions (node, user, and resource pool) and displays metrics such as the number of active sessions, the number of idle sessions, CPU usage time (s), average memory usage (MB), estimated memory usage (MB), and spilled data volume (MB).