MogDB/openGauss 故障排查思路

--负载
top 命令
htop 命令

--cpu
lscpu 命令

--内存大小
free -g

--磁盘大小
df-Th 

--磁盘使用跟踪
nohup iostat -xmt 1 > iostat.log 2>&1 &

--网络延时
应用程序与数据库之间的网络延时，集群内主库与同步备库之间的网络延时
nohup ping 目标ip | awk '{ print $0"\t" strftime("%Y-%m-%d %H:%M:%S",systime())}' > ping.log 2>&1 &

enable_xlog_prune = on
max_size_for_xlog_prune：默认是2T，建议修改值为104857600 （100GB），或根据磁盘空间自行调整

--查看复制槽
select slot_name,coalesce(plugin,'_') as plugin,
       slot_type,datoid,coalesce(database,'_') as database,
       active,coalesce(xmin,'_') as xmin,
       pg_size_pretty(pg_xlog_location_diff(CASE WHEN pg_is_in_recovery() THEN pg_last_xlog_receive_location() ELSE pg_current_xlog_location() END , restart_lsn))  AS retained_bytes
from pg_replication_slots;

--清理复制槽
select pg_drop_replication_slot('slot_name');

1、当前正在执行的SQL汇总 
select query,count(*) from pg_stat_activity group by query order by 2 desc limit 5;

2、查看SQL的执行计划
explain (analyze,costs,buffers,timing) QUERY 

3、SQL涉及的表是否有表膨胀、索引失效或缺失或重复 的情况，这步可以处理80%的慢SQL

--表结构
\d+ 表名

--表及索引占空间大小
SELECT CURRENT_CATALOG AS datname,nsp.nspname,rel.relname,
        pg_size_pretty(pg_total_relation_size(rel.oid))       AS totalsize,
        pg_size_pretty(pg_relation_size(rel.oid))             AS relsize,
        pg_size_pretty(pg_indexes_size(rel.oid))              AS indexsize,
        pg_size_pretty(pg_total_relation_size(reltoastrelid)) AS toastsize
FROM pg_namespace nsp
JOIN pg_class rel ON nsp.oid = rel.relnamespace
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND rel.relkind = 'r'
order by pg_total_relation_size(rel.oid) desc
limit 20;

--表膨胀
select schemaname,relname,n_live_tup,n_dead_tup,
	round((n_dead_tup::numeric/(case (n_dead_tup+n_live_tup) when 0 then 1 else (n_dead_tup+n_live_tup) end ) *100),2) as dead_rate
from pg_stat_user_tables
where n_live_tup > 0 and (n_dead_tup::numeric/(n_dead_tup+n_live_tup))>0
order by 5 desc limit 50;

--索引使用率
select schemaname||'.'||relname tablename,schemaname||'.'||indexrelname indexname,idx_scan,idx_tup_read,idx_tup_fetch from pg_stat_user_indexes;

--重复索引
SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
       (array_agg(idx))[1] AS idx1, (array_agg(idx))[2] AS idx2,
       (array_agg(idx))[3] AS idx3, (array_agg(idx))[4] AS idx4
FROM (
    SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'||COALESCE(indexprs::text,'')||E'\n' || COALESCE(indpred::text,'')) AS KEY
    FROM pg_index) sub
GROUP BY KEY HAVING COUNT(*)>1
ORDER BY SUM(pg_relation_size(idx)) DESC;

4、根据执行计划判断SQL是否需要改写

select * from gs_total_memory_detail;

查看连接数分布
select state,count(*) from pg_stat_activity group by state;

各状态连接占用总内存情况
select state,pg_size_pretty(sum(totalsize))
from gs_session_memory_detail m,pg_stat_activity a 
where substring_inner(sessid,position('.' in sessid)+1)=a.sessionid
group by state;

单会话占用内存排序
select sessid,pg_size_pretty(sum(totalsize)),pg_size_pretty(sum(freesize)) from gs_session_memory_detail group by sessid order by sum(totalsize) desc limit 10;

内存上下文使用内存分布
select contextname,pg_size_pretty(sum(totalsize)),pg_size_pretty(sum(freesize)) from gs_session_memory_detail group by contextname order by sum(totalsize) desc limit 10;动态内存高一般有以下几个原因：