GaussDB运维常用命令集

–杀掉会话：

select pg_terminate_backend(procpid);

–取消正在执行的语句：

select pg_cancel_backend(procpid);

–查看分布列

SELECT getdistributekey(‘item’);

–查看表大小

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’;

–查看一个表有没有做过统计信息收集。

postgres=# select * from pg_stat_get_last_analyze_time(‘test3’::regclass);

pg_stat_get_last_analyze_time

---

(1 row)

postgres=# analyze test11;

ANALYZE

postgres=# select * from pg_stat_get_last_analyze_time(‘test11’::regclass);

pg_stat_get_last_analyze_time

---

2020-07-23 19:07:06.698894+08

(1 row)

execute direct on (datanode1) ‘select * from pg_stat_activity where usename == ‘‘omm’’’;

pg_stat_get_tuples_changed

CN上执行下面两个函数：

select table_distribution(‘xrapuser’, ‘aj_qtzjtxsm’);

select table_distribution(‘xrapuser’, ‘dz_fwtxxx’);

select pg_size_pretty(pg_total_relation_size(‘xrapuser.dz_fwtxxx’));

select pg_size_pretty(pg_total_relation_size(‘xrapuser.aj_qtzjtxsm’));

select pg_size_pretty(pg_relation_size(‘xrapuser.dz_fwtxxx’));

select pg_size_pretty(pg_relation_size(‘xrapuser.aj_qtzjtxsm’));

–根据relfilenode 查找物理表：

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

–查询表以及分布列信息

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 * from pgxc_stat_activity where usename <> ‘omm’ and state = ‘active’;

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 ,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 ,client_addr ,sysdate - query_start AS dur ,query_id ,replace(query, chr(10), ’ ') FROM pgxc_stat_activity WHERE usename != ‘omm’ AND STATE = ‘active’ ORDER BY dur 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, datname, enqueue , count(*) from pgxc_stat_activity where usename <> ‘omm’ and state = ‘active’ group by coorname, usename, datname, enqueue ;

select substr(query, 1, 100) as sql, count(*) from pgxc_stat_activity where usename <>‘omm’ and state = ‘active’ group by sql;

SELECT coorname,usename,client_addr,client_hostname,application_name,state_change,connection_info FROM pgxc_stat_activity WHERE

client_addr is not null AND application_name NOT SIMILAR TO (‘cn_%|dn_%’) AND application_name NOT IN

(‘JobScheduler’,‘WorkloadMonitor’,‘workload’,‘cm_agent’,‘WLMArbiter’,‘gs_rewind’,‘gs_dump’) order by state_change asc limit 10;

–查看DN的连接：

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

–查看内存使用情况

select * from PV_TOTAL_MEMORY_DETAIL;

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 * from pgxc_total_memory_detail where memoryt