解決postgresql 數(shù)據(jù)庫 update更新慢的原因

更新時間：2021年01月28日 16:39:40 作者：yang_z_1

這篇文章主要介紹了解決postgresql 數(shù)據(jù)庫 update更新慢的原因,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下

1.查看語句是否有問題

復(fù)制倆個一模一樣的表和數(shù)據(jù) 手動執(zhí)行語句發(fā)現(xiàn)不到一分鐘就運行成功了這樣就可以確認(rèn)語句沒有問題

2.查找影響updata的因素

我的第一反應(yīng)是不是有鎖有鎖的情況會導(dǎo)致等待或者死鎖

查詢鎖

select w1.pid as 等待進(jìn)程,
w1.mode as 等待鎖模式,
w2.usename as 等待用戶,
w2.query as 等待會話,
b1.pid as 鎖的進(jìn)程,
b1.mode 鎖的鎖模式,
b2.usename as 鎖的用戶,
b2.query as 鎖的會話,
b2.application_name 鎖的應(yīng)用,
b2.client_addr 鎖的IP地址,
b2.query_start 鎖的語句執(zhí)行時間
from pg_locks w1
join pg_stat_activity w2 on w1.pid=w2.pid
join pg_locks b1 on w1.transactionid=b1.transactionid and w1.pid!=b1.pid
join pg_stat_activity b2 on b1.pid=b2.pid
where not w1.granted;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid='62560'

查詢到有鎖把鎖進(jìn)程殺掉重啟服務(wù) 繼續(xù)跟蹤發(fā)現(xiàn)5分鐘后又出現(xiàn)鎖了反復(fù)試了幾次發(fā)現(xiàn)跟鎖沒有關(guān)系

3.查詢參數(shù)

首先看的的是shared_buffers 參數(shù)，發(fā)現(xiàn)也沒有問題

在這里插入圖片描述

4.收縮表 VACUUM

查詢數(shù)據(jù)進(jìn)程時，發(fā)現(xiàn)自動收縮也執(zhí)行10分鐘還沒好就查詢表收縮的情況

用于服務(wù)器監(jiān)控，可查詢進(jìn)程，時間消耗與鎖相關(guān)

SELECT 

C.relname 對象名稱,
l.locktype 可鎖對象的類型,
l.pid 進(jìn)程id,
l.MODE 持有的鎖模式,
l.GRANTED 是否已經(jīng)對鎖進(jìn)行授權(quán),
l.fastpath,
psa.datname 數(shù)據(jù)庫名稱,
psa.usesysid 用戶id,
psa.usename 用戶名稱,
psa.application_name 應(yīng)用程序名稱,
psa.client_addr 連接的IP地址,
psa.client_port 連接使用的TCP端口號,
psa.backend_start 進(jìn)程開始時間,
psa.xact_start 事務(wù)開始時間,
psa.query_start 事務(wù)執(zhí)行此語句時間,
psa.state_change 事務(wù)狀態(tài)改變時間,
psa.wait_event_type 等待事件類型,
psa.wait_event 等待事件,
psa.STATE 查詢狀態(tài),

backend_xid 事務(wù)是否有寫入操作,
backend_xmin 是否執(zhí)事務(wù)快照,

psa.query 執(zhí)行語句,
now( ) - query_start 持續(xù)時間

FROM

pg_locks l
INNER JOIN pg_stat_activity psa ON ( psa.pid = l.pid )
LEFT OUTER JOIN pg_class C ON ( l.relation = C.oid )
-- where l.relation = 'tb_base_apparatus'::regclass

where relkind ='r'
ORDER BY query_start asc

查詢是否到達(dá)自動清理的表

SELECT
 c.relname 表名,
 (current_setting('autovacuum_analyze_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_analyze_scale_factor')::NUMERIC(12,4))*reltuples AS 自動分析閾值,
 (current_setting('autovacuum_vacuum_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_vacuum_scale_factor')::NUMERIC(12,4))*reltuples AS 自動清理閾值,
 reltuples::DECIMAL(19,0) 活元組數(shù),
 n_dead_tup::DECIMAL(19,0) 死元組數(shù)
FROM
 pg_class c 

LEFT JOIN pg_stat_all_tables d

 ON C.relname = d.relname
WHERE
 c.relname LIKE'tb%' AND reltuples > 0
 AND n_dead_tup > (current_setting('autovacuum_analyze_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_analyze_scale_factor')::NUMERIC(12,4))*reltuples;

然后發(fā)現(xiàn)死元祖太多
然后我手動收縮了這個表之后更新的就快了

VACUUM FULL VERBOSE 表名;
VACUUM FULL VERBOSE ANALYZE 表名;

5.總結(jié)

遇到這種情況先需求確保你的sql語句沒有問題，然后查看有沒有鎖可以EXPLAIN 一下，看看數(shù)據(jù)庫參數(shù)，是不是數(shù)據(jù)庫的性能原因最后再看看是不是需要收縮表

到此這篇關(guān)于解決postgresql 數(shù)據(jù)庫 update更新慢的原因的文章就介紹到這了,更多相關(guān)postgresql 數(shù)據(jù)庫 update更新慢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: