[[425902]]

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

前提

當(dāng)我們收到反饋說(shuō)數(shù)據(jù)庫(kù)響應(yīng)慢或者壓測(cè)過(guò)程中數(shù)據(jù)庫(kù)有報(bào)錯(cuò)，第一步先收集數(shù)據(jù)庫(kù)服務(wù)器資源使用情況，這一步是處理所有故障的前提。

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

*模擬網(wǎng)絡(luò)延時(shí)小知識(shí)*

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

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

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

常見(jiàn)問(wèn)題

一.Xlog目錄磁盤(pán)空間不足

Xlog日志目錄滿的原因有以下幾個(gè)：

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

備節(jié)點(diǎn)故障：

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

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

無(wú)效復(fù)制槽：

查看是否存在無(wú)效的復(fù)制槽導(dǎo)致Xlog清理不及時(shí)，需要將延時(shí)最大的復(fù)制槽刪除。

--查看復(fù)制槽 
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; 
 
--清理復(fù)制槽 
select pg_drop_replication_slot('slot_name'); 

歸檔失效：

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

Xlog參數(shù)不合理：

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

二.CPU使用率高

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

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

三.內(nèi)存不足

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

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

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

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

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

select * from gs_total_memory_detail; 

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

主要排除是連接數(shù)過(guò)多導(dǎo)致內(nèi)存不足的場(chǎng)景

查看連接數(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; 
 
單會(huì)話占用內(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; 

• 緩存機(jī)制

會(huì)話的緩存機(jī)制不合理，也會(huì)導(dǎo)致內(nèi)存無(wú)法快速釋放，可能與參數(shù)local_syscache_threshold有關(guān)系。

內(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;動(dòng)態(tài)內(nèi)存高一般有以下幾個(gè)原因： 

總結(jié)：

①.連接數(shù)過(guò)多會(huì)導(dǎo)致動(dòng)態(tài)內(nèi)存耗盡，

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

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

關(guān)于作者

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