GaussDB(DWS) 日常維護命令

在日常使用GaussDB(DWS) 過程中，會遇到各種各樣的問題，通過熟練的掌握常用的維護命令和問題定位方法，可以使我們提高問題定位效率，快速解決問題。根據以往的經驗，將常用的操作命令分成了以下三個部分。在實際使用的過程中可能還需要掌握其它更多的命令，本文僅列舉了部分命令。

1、日常維護DB命令

會話查殺

select pg_terminate_backend(procpid); --殺掉會話

select pg_cancel_backend(procpid); --取消正在執行的語句

主備切換命令

將DN備實例切換為主實例。假設備實例所在主機plat1，路徑為“/gaussdb/data/data_dnS1”。

gs_om -t switch -h plat1 -D /gaussdb/data/data_dnS1

參數q表示快速切換，nodeid為需要升主的備實例所在節點ID，/srv/BigData/mppdb/data2為備DN或GTM的數據目錄。

cm_ctl switchover -n nodeid -D /srv/BigData/mppdb/data2 -q

查看表分布情情況

select getdistributekey(‘item’);

SELECT n.nspname ,c.relname ,getdistributekey(c.oid) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname <> ‘pg_catalog’ AND n.nspname <> ‘information_schema’ AND n.nspname <> ‘cstore’ AND c.relkind = ‘r’ ORDER BY 1,2; --查找多個表的分布列信息

select pg_size_pretty(pg_table_size(‘public.item’));

select table_skewness(‘inventory’);

查詢審計日志

select * from pgxc_query_audit(‘2020-07-16 10:36:05’,‘2020-07-16 12:36:05’) where username!=‘omm’;

SELECT * FROM pg_catalog.pgxc_query_audit_ext (‘2021-09-01 19:15:00’,current_timestamp) where username = ‘xxx’ and audit_type = ‘user_login’; --查看審計日志

查詢pooler池

select node_name, in_use, count() from pg_pooler_status group by node_name, in_use order by 3;

select database,user_name,in_use,count() from pg_pooler_status group by 1, 2, 3 order by 4;

clean connection to all for database xxx;

根據filenode 查找對應的物理表

select oid, * from pg_class where reltoastrelid = (select oid from pg_class where relfilenode = 103892072);

查看內存使用情況

select * from pgxc_total_memory_detail where memorytype = ‘dynamic_used_memory’ order by 3 desc;

select split_part(pv_session_memory_detail.sessid,’.’,2),sum(totalsize),count(*) from pv_session_memory_detail group by split_part(pv_session_memory_detail.sessid,’.’,2) order by sum(totalsize) desc;

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 and query_id = ‘76561193666355359’ order by totalsize desc limit 100;

查詢等待視圖

select query_start, state_change, waiting, enqueue, state, a.query_id, substr(replace(query, chr(10), ’ '), 0, 10), node_name,thread_name,tid,lwtid,ptid,tlevel,smpid,wait_status,wait_event from pgxc_stat_activity a, pgxc_thread_wait_status b where state = ‘active’ and a.query_id = b.query_id and a.query_id <> 0;

select node_name, wait_status, count(*) from pgxc_thread_wait_status group by node_name, wait_status order by 3 desc;

select nodename,username,application_name, start_time, max_peak_memory , queryid, substr(query,1, 10), substring(warning from ‘Statistic Not Collect’) as warning from wlm_session where warning like ‘%Statistic Not Collect%’ and application_name = ‘Data Studio’ order by max_peak_memory desc;

查看活躍會話信息

select coorname, usename, datname, enqueue , count(*) from pgxc_stat_activity where usename <> ‘omm’ and state = ‘active’ group by coorname, usename, datname, enqueue ;

select coorname, usename, client_addr, sysdate - query_start as dur, enqueue, query_id, replace(query, chr(10), ’ ') from pgxc_stat_activity where usename!= ‘omm’ and state = ‘active’ order by coorname, dur desc;

SELECT coorname, usename ,client_addr ,sysdate - query_start AS dur ,query_id ,substr(replace(query, chr(10), ’ '), 0, 100) FROM pgxc_stat_activity WHERE usename != ‘omm’ AND STATE = ‘active’ ORDER BY dur DESC;

集群負載管理相關視圖查詢

select usename,enqueue,datname,status,attribute,count(),sum(statement_mem) from pg_session_wlmstat group by 3,1,2,4,5 order by 1,3,4,5 ;

select usename,processid,threadid,priority,attribute,lane,enqueue,status,block_time,elapsed_time,statement_mem from pg_session_wlmstat where usename=‘usr1’;

select * from pg_stat_get_workload_struct_info();

select count() from pg_stat_get_wlm_realtime_session_info(NULL);

查找刪除復制槽

select * from pg_get_replication_slots(); – 查找復制槽

select pg_drop_replication_slot(‘dn_6004’); --刪除復制槽信息

查看集群事務信息

select * from pg_prepared_xacts; --查看殘留事務

select * from pgxc_prepared_xacts; --查看全局殘留事務視圖

select * from pg_running_xacts; --查看運行時事務情況

select * from pgxc_running_xacts; --查看集群運行事務情況

集群啟停

checkpoint;

cm_ctl stop -mi

cm_ctl start -mi

數據文件和日志解析

pg_xlogdump 000000010000000000000002 -z

pg_xlogdump 000000010000000000000004 -n

pagehack -f pg_filenode.map -t filenode_map

pagehack -f 16502 -t heap

2 常用GUC參數設置

檢查active sql配置

show use_workload_manager;

show enable_control_group;

show enable_resource_record;

show enable_resource_track;

show resource_track_level;

show resource_track_duration;

show resource_track_cost;

打開 TOPSQL功能

set use_workload_manager = on;

set enable_control_group = on;

set enable_resource_record = on;

set resource_track_level = query;

修改收集統計信息的比例

set default_statistics_target = -10;

analzye public.customer;

打開集群DEBUG2 級別的日志

set log_min_messages=debug2;

set logging_module=‘on(ALL)’;

查看頁面上所有元組的事務信息

start transaction read only;

set enable_show_any_tuples = true;

set enable_indexscan = off;

set enable_bitmapscan = off;

select xmin,xmax,pgxc_is_committed(xmin),pgxc_is_committed(xmax),oid,* from pg_class where relname=‘表名’ ;

優化器相關參數，通過調整參數干預估算模型

cost_model_version;

cost_param

join_num_distinct

qual_num_distinct

取消集群只讀設置只讀級別

gs_guc reload -Z coordinator -Z datanode -N all -I all -c “default_transaction_read_only=off”

gs_guc reload -Z coordinator -Z datanode -N all -I all -c “datastorage_threshold_value_check=95”

3 OS相關命令

查看進程相關信息

ps -eo pid,lstart,etime,cmd | grep gaussdb;date

strace -p 47148 -r -T -o strace.log

查看透明大頁信息

cat /sys/kernel/mm/transparent_hugepage/enabled

[never]表示未打開

gsql進程查殺

ps -ef | grep gsql | grep -v grep | awk ‘{print $2}’ | xargs kill -9

查看CPU占用高的線程

top -b -p 39450 -H -n 1|head -100

sar命令

sar -r 5 4 輸出物理內存和虛擬內存的統計信息

sar -B 5 5 分頁統計

sar -u 3 5 顯示CPU使用信息

sar -b 3 5 磁盤IO信息

sar -n DEV 2 3 網絡流量信息

網絡問題定位常用命令

netstat –anop|grep “on (”| sort –rnk 3|head -50

netstat -naop | grep 54321 --查看端口被占用。

netstat -anop | awk ‘{print $4}’ | grep ip|sort|uniq -c|grep " 1 "|wc -l --隨機端口不足

ping -s 8192 -I eth0 dest_ip --對端IP是否可達

查看raid 卡緩存策略 Write through，IO性能比WriteBack 要慢。

/opt/MegaRAID/MegaCli/MegaCli64 -LDinfo -Lall –aAll

數據倉庫服務 GaussDB(DWS)

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。