[[425902]]

本文轉(zhuǎn)載自微信公眾號「數(shù)據(jù)和云」，作者高云龍。轉(zhuǎn)載本文請聯(lián)系數(shù)據(jù)和云公眾號。

前提

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

--負載 
top 命令 
htop 命令 
 
--cpu 
lscpu 命令 
 
--內(nèi)存大小 
free -g 
 
--磁盤大小 
df-Th  
 
--磁盤使用跟蹤 
nohup iostat -xmt 1 > iostat.log 2>&1 & 
 
--網(wǎng)絡延時 
應用程序與數(shù)據(jù)庫之間的網(wǎng)絡延時，集群內(nèi)主庫與同步備庫之間的網(wǎng)絡延時 
nohup ping 目標ip | awk '{ print $0"\t" strftime("%Y-%m-%d %H:%M:%S",systime())}' > ping.log 2>&1 & 

*模擬網(wǎng)絡延時小知識*

模擬同城機房網(wǎng)絡延遲在0.7ms ~ 0.9ms ;

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

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

常見問題

一.Xlog目錄磁盤空間不足

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

• 集群內(nèi)有宕機的備節(jié)點，或者主備節(jié)點之間的網(wǎng)絡不通;
• 無效的復制槽未及時清理;
• 開啟歸檔，但歸檔失敗;
• Xlog保留數(shù)量過多。

備節(jié)點故障：

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

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

無效復制槽：

查看是否存在無效的復制槽導致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'); 

歸檔失效：

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

Xlog參數(shù)不合理：

檢查數(shù)據(jù)庫Xlog保留參數(shù)值是否合理: wal_keep_segments。

二.CPU使用率高

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

1、當前正在執(zhí)行的SQL匯總  
select query,count(*) from pg_stat_activity group by query order by 2 desc limit 5; 
 
2、查看SQL的執(zhí)行計劃 
explain (analyze,costs,buffers,timing) QUERY  
 
3、SQL涉及的表是否有表膨脹、索引失效或缺失或重復 的情況，這步可以處理80%的慢SQL 
 
--表結(jié)構(gòu) 
\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、根據(jù)執(zhí)行計劃判斷SQL是否需要改寫 

三.內(nèi)存不足

①.查看服務器物理內(nèi)存整體使用情況。

②.檢查數(shù)據(jù)庫內(nèi)存參數(shù)設置是否合理：

• max_process_memory 建議設置物理內(nèi)存80%;
• shared_buffers 建議設置為物理內(nèi)存的40%。

數(shù)據(jù)庫內(nèi)存使用分布：

查看整體內(nèi)存使用情況,當dynamic_used_memory 與 max_dynamic_memory 的值接近時說明動態(tài)內(nèi)存可能不足，如果dynamic_peak_memory超過了max_dynamic_memory，說明曾經(jīng)發(fā)生過OOM。

select * from gs_total_memory_detail; 

• 連接過多耗盡內(nèi)存

主要排除是連接數(shù)過多導致內(nèi)存不足的場景

查看連接數(shù)分布 
select state,count(*) from pg_stat_activity group by state; 
 
各狀態(tài)連接占用總內(nèi)存情況 
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; 
 
單會話占用內(nèi)存排序 
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; 

• 緩存機制

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

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

總結(jié)：

①.連接數(shù)過多會導致動態(tài)內(nèi)存耗盡，

• 如果是IDLE連接多，可能是開發(fā)端長連接保留數(shù)量不合理;
• 如果是ACTIVE連接多，可能是硬件內(nèi)存不足，需要擴內(nèi)存。

②.單個會話占用內(nèi)存多，需要根據(jù)SQL去分析占用內(nèi)存情況。

關于作者

高云龍，云和恩墨服務總監(jiān)。長期從事PG運維工作，目前在支持openGauss生態(tài)發(fā)展。