Common O&M Commands of GaussDB(DWS)

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.

• View the overall running status of the current service.

  1	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.

  1	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.

  1	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).

  1

  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.

  1

  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.)

  1	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.)

  1	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.)

  1

  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

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.

  1 2	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.

  1	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.

  1 2	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.

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

• Lock abnormal users.

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

• Add a service to the blacklist.

  1 2 3

  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.

• 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.

  1	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.

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

• View the dirty page rate of a single table.

  1

  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.

  1	select pg_get_tabledef('schema_name.table_name');

• Check the table size.

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

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

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

• View details about dirty data.

  1 2 3 4 5 6 7

  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;