GaussDB问题定位及问题处理相关语句

GaussDB问题定位相关语句
审计日志
SELECT * FROM pg_query_audit(‘$start_time', '$finish_time’);
复制
查看内存
select * from pv_total_memory_detail;

复制
select contextname, count(*), sum(totalsize)/1024/1024 as “totalsize-MB”, sum(usedsize)/1024/1024 as “usedsize-MB”, sum(freesize)/1024/1024 as “freesize-MB” from PV_SESSION_MEMORY_CONTEXT group by contextname order by 3 desc limit 20;

select contextname, sum(totalsize)/1024/1024 sum, sum(freesize)/1024/1024, count() count from pv_session_memory_detail group by contextname order by sum desc limit 10;

查看磁盘使用情况
df -h du -sh ./ | sort -nr
GaussDB问题处理相关语句
sql命令
按时间清理所有用户连接
EXECUTE DIRECT ON ALL ‘select pg_terminate_session(pid,sessionid),pid,sessionid from pg_stat_activity where usename in (’’’’, ‘’’’) and query_start < ‘‘2022-01-25 02:09:00’’’;
清理DN上的用户idle连接
EXECUTE DIRECT ON datanodes ‘select pg_terminate_session(pid,sessionid),pid,sessionid from pg_stat_activity where usename in (’‘usename1’’, ‘‘usename2’’) and state = ‘‘idle’’’;
清理单个CN上的所有连接
select pg_terminate_session(pid,sessionid),pid,sessionid from pg_stat_activity where usename in (‘$usename1', '$usename’);
清理单个节点上的连接
select pg_terminate_session(pid,sessionid),pid,sessionid from pg_stat_activity where usename in (‘$usename1', '$usename’) and query_start < ‘$time’;
确认长事务
EXECUTE DIRECT ON ALL ‘select pgxc_node_str() as node_name, query_start, pid, sessionid, query from pg_stat_activity where state = ‘‘active’’ order by query_start’;
gs_guc命令
调整磁盘只读阈值
gs_guc reload -Z cmserver -N all -I all -c “datastorage_threshold_value_check=95”
cm_ctl 命令
切换主DN到指定的DN上
cm_ctl switchover -n nodeid -D datadir