解決postgresql 數(shù)據(jù)庫(kù) update更新慢的原因
;大約140000條數(shù)據(jù)) 竟然運(yùn)行了一個(gè)小時(shí)還沒有完成
下面是我的幾點(diǎn)解決方案
我的update 語(yǔ)句 是從一個(gè)臨時(shí)表更新值到另一個(gè)正式表
因?yàn)榫唧w數(shù)據(jù)需要保密,我就不截圖了 只說說大體思路,與方法
1.查看語(yǔ)句是否有問題
復(fù)制倆個(gè)一模一樣的表 和數(shù)據(jù) 手動(dòng)執(zhí)行語(yǔ)句 發(fā)現(xiàn)不到一分鐘就運(yùn)行成功了 這樣就可以確認(rèn)語(yǔ)句沒有問題
2.查找影響updata的因素
我的第一反應(yīng)是不是有鎖 有鎖的情況會(huì)導(dǎo)致等待或者死鎖
查詢鎖
select w1.pid as 等待進(jìn)程, w1.mode as 等待鎖模式, w2.usename as 等待用戶, w2.query as 等待會(huì)話, b1.pid as 鎖的進(jìn)程, b1.mode 鎖的鎖模式, b2.usename as 鎖的用戶, b2.query as 鎖的會(huì)話, b2.application_name 鎖的應(yīng)用, b2.client_addr 鎖的IP地址, b2.query_start 鎖的語(yǔ)句執(zhí)行時(shí)間 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)程時(shí),發(fā)現(xiàn)自動(dòng)收縮 也執(zhí)行10分鐘還沒好 就查詢表收縮的情況
用于服務(wù)器監(jiān)控,可查詢進(jìn)程,時(shí)間消耗與鎖相關(guān)
SELECT C.relname 對(duì)象名稱, l.locktype 可鎖對(duì)象的類型, l.pid 進(jìn)程id, l.MODE 持有的鎖模式, l.GRANTED 是否已經(jīng)對(duì)鎖進(jìn)行授權(quán), l.fastpath, psa.datname 數(shù)據(jù)庫(kù)名稱, psa.usesysid 用戶id, psa.usename 用戶名稱, psa.application_name 應(yīng)用程序名稱, psa.client_addr 連接的IP地址, psa.client_port 連接使用的TCP端口號(hào), psa.backend_start 進(jìn)程開始時(shí)間, psa.xact_start 事務(wù)開始時(shí)間, psa.query_start 事務(wù)執(zhí)行此語(yǔ)句時(shí)間, psa.state_change 事務(wù)狀態(tài)改變時(shí)間, psa.wait_event_type 等待事件類型, psa.wait_event 等待事件, psa.STATE 查詢狀態(tài), backend_xid 事務(wù)是否有寫入操作, backend_xmin 是否執(zhí)事務(wù)快照, psa.query 執(zhí)行語(yǔ)句, now( ) - query_start 持續(xù)時(shí)間 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á)自動(dòng)清理的表
SELECT c.relname 表名, (current_setting('autovacuum_analyze_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_analyze_scale_factor')::NUMERIC(12,4))*reltuples AS 自動(dòng)分析閾值, (current_setting('autovacuum_vacuum_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_vacuum_scale_factor')::NUMERIC(12,4))*reltuples AS 自動(dòng)清理閾值, 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)死元祖太多
然后我手動(dòng)收縮了這個(gè)表 之后更新的就快了
VACUUM FULL VERBOSE 表名; VACUUM FULL VERBOSE ANALYZE 表名;
5.總結(jié)
遇到這種情況 先需求確保你的sql語(yǔ)句沒有問題,然后查看有沒有鎖 可以EXPLAIN 一下 ,看看數(shù)據(jù)庫(kù)參數(shù),是不是數(shù)據(jù)庫(kù)的性能原因 最后再看看是不是需要收縮表
到此這篇關(guān)于解決postgresql 數(shù)據(jù)庫(kù) update更新慢的原因的文章就介紹到這了,更多相關(guān)postgresql 數(shù)據(jù)庫(kù) update更新慢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
postgresql 實(shí)現(xiàn)多表關(guān)聯(lián)刪除
這篇文章主要介紹了postgresql 實(shí)現(xiàn)多表關(guān)聯(lián)刪除操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01postgresql踩坑系列之關(guān)于to_date()問題
這篇文章主要介紹了postgresql踩坑系列之關(guān)于to_date()問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-03-03如何查看postgres數(shù)據(jù)庫(kù)端口
這篇文章主要介紹了如何查看postgres數(shù)據(jù)庫(kù)端口操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01PostgreSQL 實(shí)現(xiàn)給查詢列表增加序號(hào)操作
這篇文章主要介紹了PostgreSQL 實(shí)現(xiàn)給查詢列表增加序號(hào)操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01PostgreSQL通過oracle_fdw訪問Oracle數(shù)據(jù)的實(shí)現(xiàn)步驟
通過類似于Oracle數(shù)據(jù)庫(kù)DBLINK的方式去實(shí)現(xiàn)PostgreSQL訪問oracle數(shù)據(jù)庫(kù),本地搭建測(cè)試環(huán)境并配置相關(guān)配置,接下來通過本文給大家分享PostgreSQL通過oracle_fdw訪問Oracle數(shù)據(jù)的實(shí)現(xiàn)步驟,感興趣的朋友一起看看吧2021-05-05CentOS PostgreSQL 12 主從復(fù)制(主從切換)操作
這篇文章主要介紹了CentOS PostgreSQL 12 主從復(fù)制(主從切換)操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01PostgreSQL數(shù)據(jù)庫(kù)事務(wù)插入刪除及更新操作示例
這篇文章主要為大家介紹了PostgreSQL事務(wù)的插入刪除及更新操作示例,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步早日升職加薪2022-04-04PostgreSQL與MySQL優(yōu)劣勢(shì)比較淺談
這篇文章主要詳細(xì)介紹了PostgreSQL與MySQL有哪些優(yōu)劣勢(shì),文中介紹的非常詳細(xì),對(duì)于學(xué)習(xí)有一定的幫助,感興趣的小伙伴可以參考一下2023-04-04