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/ Common O&M Commands of GaussDB(DWS)

Common O&M Commands of GaussDB(DWS)

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

View PDF

This section lists only common O&M commands. The system objects to be queried can be changed based on the site requirements. For details about the returned fields, see the description of system catalogs, system views, and system functions in the product manualsDeveloper Guide.

Viewing O&M Status

Prerequisites: The GaussDB(DWS) cluster has been connected.

View the overall running status of the current service.

     
        select coorname, usename, client_addr, sysdate-query_start as duration, state, enqueue,waiting, pid, query_id, substr(query,1,60) from pgxc_stat_activity where usename != 'Ruby' and usename != 'omm' and state = 'active' order by duration desc;

View the overall concurrency of the current service.

     
        select usename,coorname,enqueue,state,count(*) from pgxc_stat_activity where usename <> 'omm' and usename <> 'Ruby' group by 1,2,3,4 order by 4,5 desc limit 30;

Check the overall waiting status in the current cluster.

     
        select wait_status,wait_event,count(*) as cnt from pgxc_thread_wait_status where wait_status <> 'wait cmd' and wait_status <> 'synchronize quit' and wait_status <> 'none' and wait_status <> 'wait stream task' group by 1,2 order by 3 desc limit 50;

View the service running information of the resource pool in the current cluster (resource management and control scenario).

     
        select s.resource_pool as rpname, count(1) as session_cnt,sum(case when a.state = 'active' then 1 else 0 end) as active_cnt,sum(case when s.enqueue ='global' then 1 else 0 end) as global_wait,sum(case when s.lane = 'fast' and s.status = 'running' then 1 else 0 end) as fast_run,sum(case when s.lane = 'fast' and s.status = 'pending' and s.enqueue not in ('global','none') then 1 else 0 end) as fast_wait,sum(case when s.lane = 'slow' and s.status = 'running' then 1 else 0 end) as slow_run,sum(case when s.lane = 'slow' and s.status = 'pending' and s.enqueue not in ('global','none') then 1 else 0 end) as slow_wait,sum(case when s.status = 'running' then s.statement_mem else 0 end) as est_mem from pg_catalog.pgxc_session_wlmstat s,pg_catalog.pgxc_stat_activity a where s.threadid=a.pid(+) and s.attribute != 'internal' and s.resource_pool != 'root' group by 1;

Check the dynamic memory watermark of the current cluster.

     
        select a.nodename,a.memorymbytes as dynamic_used_memory,b.memorymbytes as max_dynamic_memory, dynamic_used_memory/max_dynamic_memory*100 as used_rate  from pgxc_total_memory_detail a join pgxc_total_memory_detail b on a.nodename=b.nodename  where a.memorytype = 'dynamic_used_memory' and b.memorytype = 'max_dynamic_memory' order by a.memorymbytes desc;

Check the memory usage of each thread. (Check the dynamic memory watermark of the current cluster. Connect to the CN/DN node with high dynamic memory usage that we found to retrieve the information.)

     
        select b.state, sum(totalsize) as totalsize, sum(freesize) as freesize, sum(usedsize) as usedsize from pv_session_memory_detail a , pg_stat_activity b where split_part(a.sessid,'.',2) = b.pid group by b.state order by totalsize desc limit 20;

Check the memory used by each session in the current instance. (Check the dynamic memory watermark of the current cluster. Connect to the CN/DN node with high dynamic memory usage that we found to retrieve the information.)

     
        select split_part(pv_session_memory_detail.sessid,'.',2) pid,pg_size_pretty(sum(totalsize)) total_size,count(*) context_count from pv_session_memory_detail group by pid order by sum(totalsize) desc;

Check the memory used by each SQL statement in the current instance. (Check the dynamic memory watermark of the current cluster. Connect to the CN/DN node with high dynamic memory usage that we found to retrieve the information.)

     
        select sessid, contextname, level,parent, pg_size_pretty(totalsize) as total ,pg_size_pretty(freesize) as freesize, pg_size_pretty(usedsize) as usedsize, datname,query_id, query from pv_session_memory_detail a , pg_stat_activity b where split_part(a.sessid,'.',2) = b.pid order by totalsize desc limit 100;

Emergency Recovery

CAUTION:

Always confirm emergency operations that may impact services with the customer before proceeding. Never attempt to perform these operations independently.

Prerequisites: The GaussDB(DWS) cluster has been connected.

View the overall running status of the current service. Obtain the PID of the statement to be scanned.

     
        execute direct on(cn_name) 'select pg_cancel_backend(PID of the scanned statement)';
execute direct on(cn_name) 'select pg_terminate_backend(PID of the scanned statement)';

Group scan statements together in batches, without executing the scan command.

     
        select 'execute direct on(' || coorname || ') ''select pg_terminate_backend(' || pid || ')'';',sysdate-query_start as dur, substr(query,1,60) from pgxc_stat_activity where usename != 'omm' and usename != 'Ruby' and state = 'active' order by dur desc limit 30;

Clear idle connections.

     
        clean connection to all for database xxx;    --Clear idle connections.
select * from pgxc_clean_free_conn();        --Clear pooler cache connections.

Fix the CCN count by connecting to the CCN.

     
        select * from pg_stat_get_workload_struct_info();  --Retain the CCN information.
select gs_wlm_node_recover(true);  --Repair CCNs.

Lock abnormal users.

     
        alter user usename account lock;    --Lock a user.
alter user usename account unlock;   --Unlock a user.

Add a service to the blacklist.

     
        select * from gs_append_blocklist(unique_sql_id);   --Add a service to the blacklist.
select * from gs_blocklist_query;   --Query the existing blacklist.
select gs_remove_blocklist(unique_sql_id);  --Remove the blacklist.

Service Analysis

Prerequisites: The GaussDB(DWS) cluster has been connected.

View the waiting status of the currently executing SQL statement. Run the statement user for viewing the overall running status of the current service and obtain the value of the query_id field, which can be used as the Actual query ID in the following statement.

     
        select * from pgxc_thread_wait_status where query_id = Actual query ID order by node_name,wait_status,wait_event;

View the running process information of the running SQL statement. Run the statement user for viewing the overall running status of the current service and obtain the value of the query_id field, which can be used as the Actual query ID in the following statement.
1

select * from pgxc_wlm_session_statistics where queryid = Actual query ID;

View the running information of historical SQL statements. Run the statement used for viewing the overall running status of the current service and obtain the value of the query_id field, which can be used as the Actual query ID in the following statement.
1

select * from pgxc_wlm_session_info where queryid = Actual query ID;

View the skew information of a single table.

     
        select * from table_distribution('schema_name','table_name');

View the dirty page rate of a single table.

     
        select c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, cast( (n_dead_tup / (n_live_tup + n_dead_tup + 0.0001) * 100) AS numeric(5,2)) AS dirty_page_rate from pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace where c.oid = 'schema_name.table_name'::regclass::oid;

View the table definition and index information.

     
        select pg_get_tabledef('schema_name.table_name');

Check the table size.

     
        select pg_size_pretty(pg_table_size('schema_name.table_name'));

View the creation time, modification time, and last analysis time of the table.

     
        select * from pg_object where object_oid='schema_name.table_name'::regclass;

View details about dirty data.

     
        start transaction read only;
set enable_show_any_tuples = true;
set enable_indexscan = off;
set enable_bitmapscan = off;
select ctid,xmin,xmax,pgxc_is_committed(xmin),pgxc_is_committed(xmax),oid,* from schema_name.table_name;
select xmin,xmax,ctid, * from pgxc_node;
rollback;