[[425902]]

本文轉載自微信公眾號「數據和云」，作者高云龍。轉載本文請聯系數據和云公眾號。

前提

當我們收到反饋說數據庫響應慢或者壓測過程中數據庫有報錯，第一步先收集數據庫服務器資源使用情況，這一步是處理所有故障的前提。

--負載 
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 & 

*模擬網絡延時小知識*

模擬同城機房網絡延遲在0.7ms ~ 0.9ms ;

添加網絡延遲模擬：tc qdisc add dev enp23s0f1(網卡) root netem delay 0.8ms 0.1ms ;

刪除網絡延時模擬：tc qdisc dev dev enp23s0f1(網卡) root netem delay 0.8ms 0.1ms。

常見問題

一.Xlog目錄磁盤空間不足

Xlog日志目錄滿的原因有以下幾個：

• 集群內有宕機的備節點，或者主備節點之間的網絡不通;
• 無效的復制槽未及時清理;
• 開啟歸檔，但歸檔失敗;
• Xlog保留數量過多。

備節點故障：

通過網絡及數據庫日志信息，判斷節點故障原因，并盡快恢復主備節點之間的復制關系，當故障無法快速解決時，建議修改數據庫參數來改變主庫Xlog保留大小。

enable_xlog_prune = on 
max_size_for_xlog_prune：默認是2T，建議修改值為104857600 （100GB），或根據磁盤空間自行調整 

無效復制槽：

查看是否存在無效的復制槽導致Xlog清理不及時，需要將延時最大的復制槽刪除。

--查看復制槽 
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'); 

歸檔失效：

先檢查歸檔目錄是否有歸檔日志，如果沒有，需要查看數據庫日志歸檔失效的原因。

Xlog參數不合理：

檢查數據庫Xlog保留參數值是否合理: wal_keep_segments。

二.CPU使用率高

除了數據庫BUG、其他程序耗CPU高影響數據庫外，絕大部分原因是SQL執行慢且并發量大引起。

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是否需要改寫 

三.內存不足

①.查看服務器物理內存整體使用情況。

②.檢查數據庫內存參數設置是否合理：

• max_process_memory 建議設置物理內存80%;
• shared_buffers 建議設置為物理內存的40%。

數據庫內存使用分布：

查看整體內存使用情況,當dynamic_used_memory 與 max_dynamic_memory 的值接近時說明動態內存可能不足，如果dynamic_peak_memory超過了max_dynamic_memory，說明曾經發生過OOM。

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; 

• 緩存機制

會話的緩存機制不合理，也會導致內存無法快速釋放，可能與參數local_syscache_threshold有關系。

內存上下文使用內存分布 
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;動態內存高一般有以下幾個原因： 

總結：

①.連接數過多會導致動態內存耗盡，

• 如果是IDLE連接多，可能是開發端長連接保留數量不合理;
• 如果是ACTIVE連接多，可能是硬件內存不足，需要擴內存。

②.單個會話占用內存多，需要根據SQL去分析占用內存情況。

關于作者

高云龍，云和恩墨服務總監。長期從事PG運維工作，目前在支持openGauss生態發展。