Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Situation Awareness
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
Software Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

TopSQL Query Example

Updated on 2022-06-11 GMT+08:00

In this section, TPC-DS sample data is used as an example to describe how to query Real-time TopSQL and Historical TopSQL.

Configuring Cluster Parameters

To query for historical or archived resource monitoring information about jobs of top SQLs, you need to set related GUC parameters first. The procedure is as follows:

  1. Log in to the GaussDB(DWS) management console.
  2. On the Cluster Management page, locate the required cluster and click the cluster name. The cluster details page is displayed.
  3. Click the Parameter Modifications tab to view the values of cluster parameters.
  4. Set an appropriate value for parameter resource_track_duration and click Save.
    NOTE:

    If enable_resource_record is set to on, storage space expansion may occur and thereby slightly affects the performance. Therefore, set is to off if record archiving is unnecessary.

  5. Go back to the Cluster Management page, click the refresh button in the upper right corner, and wait until the cluster parameter settings are applied.

Example for Querying for Top SQLs

The TPC-DS sample data is used as an example.

  1. Open the SQL client tool and connect to your database.
  2. Run the EXPLAIN statement to query for the estimated cost of the SQL statement to be executed to determine whether resources of the SQL statement will be monitored.

    By default, only resources of a query whose execution cost is greater than the value (default: 100000) of resource_track_cost are monitored and can be queried by users.

    For example, run the following statements to query for the estimated execution cost of the SQL statement:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    SET CURRENT_SCHEMA = tpcds;
    EXPLAIN WITH customer_total_return AS
    ( SELECT sr_customer_sk as ctr_customer_sk,
    sr_store_sk as ctr_store_sk, 
    sum(SR_FEE) as ctr_total_return 
    FROM store_returns, date_dim
    WHERE sr_returned_date_sk = d_date_sk AND d_year =2000
    GROUP BY sr_customer_sk, sr_store_sk )
    SELECT  c_customer_id
    FROM customer_total_return ctr1, store, customer
    WHERE ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 
    FROM customer_total_return ctr2
    WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk) 
    AND s_store_sk = ctr1.ctr_store_sk
    AND s_state = 'TN'
    AND ctr1.ctr_customer_sk = c_customer_sk
    ORDER BY c_customer_id
    limit 100;
    

    In the following query result, the value in the first row of the E-costs column is the estimated cost of the SQL statement.

    Figure 1 EXPLAIN result

    In this example, to demonstrate the resource monitoring function of top SQLs, you need to set resource_track_cost to a value smaller than the estimated cost in the EXPLAIN result, for example, 100. For details about the parameter setting, see resource_track_cost.

    NOTE:

    After completing this example, you still need to reset resource_track_cost to its default value 100000 or a proper value. An overly small parameter value will compromise the database performance.

  3. Run SQL statements.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    SET CURRENT_SCHEMA = tpcds;
    WITH customer_total_return AS
    (SELECT sr_customer_sk as ctr_customer_sk, 
    sr_store_sk as ctr_store_sk, 
    sum(SR_FEE) as ctr_total_return
    FROM store_returns,date_dim
    WHERE sr_returned_date_sk = d_date_sk
    AND d_year =2000
    GROUP BY sr_customer_sk ,sr_store_sk)
    SELECT  c_customer_id
    FROM customer_total_return ctr1, store, customer
    WHERE ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 
    FROM customer_total_return ctr2
    WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk)
    AND s_store_sk = ctr1.ctr_store_sk
    AND s_state = 'TN'
    AND ctr1.ctr_customer_sk = c_customer_sk
    ORDER BY c_customer_id
    limit 100;
    

  4. During statement execution, query for the real-time memory peak information about the SQL statement on the current CN.

    1
    SELECT query,max_peak_memory,average_peak_memory,memory_skew_percent FROM gs_wlm_session_statistics ORDER BY start_time DESC;
    

    The preceding command queries for the real-time peak information at the query-level. The peak information includes the maximum memory peak among all DNs per second, average memory peak among all DNs per second, and memory usage skew across DNs.

    For more examples of querying for the real-time resource monitoring information of top SQLs, see Real-time TopSQL.

  5. Wait until the SQL statement execution in 3 is complete, and then query for the historical resource monitoring information of the statement.

    1
    select query,start_time,finish_time,duration,status from gs_wlm_session_history order by start_time desc;
    

    The preceding command queries for the historical information at the query-level. The peak information includes the execution start time, execution duration (unit: ms), and execution status. The time unit is ms.

    For more examples of querying for the historical resource monitoring information of top SQLs, see Historical TopSQL.

  6. Wait for 3 minutes after the execution of the SQL statement in 3 is complete, query for the historical resource monitoring information of the statement in the info view.

    If enable_resource_record is set to on and the execution time of the SQL statement in 3 is no less than the value of resource_track_duration, historical information about the SQL statement will be archived to the gs_wlm_session_info view 3 minutes after the execution of the SQL statement is complete.

    The info view can be queried only when the gaussdb database is connected. Therefore, switch to the gaussdb database before running the following statement:

    1
    select query,start_time,finish_time,duration,status from gs_wlm_session_info order by start_time desc;
    

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback