背景
本文將介紹三種資料庫變慢場景的分析與優化方法.
- 1、已經定位出的特定慢SQL
- 2、整個資料庫實體(幾乎所有SQL)變慢, 或者某些時候整個資料庫實體大面積SQL變慢(大面積抖動)
- 3、某些正常情況下很快的SQL偶爾會變慢(抖動)
在優化之前
“治未病”的概念最早出現于《黃帝內經》,在《素問·四氣調神大論》中提出：“是故圣人不治已病治未病,不治已亂治未亂,此之謂也， 夫病已成而后藥之,亂已成而后治之,譬猶渴而穿井,斗而鑄錐,不亦晚乎”，就生動地指出了“治未病”的重要意義，
資料庫優化固然重要, 但這是治已病, 未病則更加重要. 未病建議參考:
- 《PostgreSQL 資料庫開發規范》
- 《PostgreSQL 持續穩定使用的小技巧 - 最佳實踐、規約、規范》
- 《PostgreSQL 11 postgresql.conf 引數模板 - 珍藏級》
- 《PostgreSQL on Linux 最佳部署手冊 - 珍藏級》
一、單一慢SQL優化
單一SQL慢, 比較容易解決, 從執行計劃入手即可, 是否執行計劃不正確, 是否索引未創建或不合理, 是否需要改寫SQL, 是否有膨脹, 是否存在業務邏輯導致的長時間鎖沖突, 是否SQL過于復雜需要固定執行計劃或者采用更高級的優化器.
常用分析工具與方法:
- explain, 分析執行計劃
- 索引推薦
- 檢查膨脹
- perf, 分析單條SQL(或函式)執行時的代碼瓶頸
- 鎖等待分析
- 查詢 其他會話中正在運行的SQL memory context
- show 其他會話中正在運行的SQL的執行計劃
- 動態優化
- 指定、固定、篡改執行計劃
- 資料庫存盤組織、資料庫索引組織、優化器演算法、資料掃描方法等原理
例子, 查詢所有傳感器上報資料的最新值:
create unlogged table tbl_log (gid int, info text, crt_time timestamp); insert into tbl_log select random()*10, md5(random()::Text), clock_timestamp() from generate_series(1,5000000); select gid,info,crt_Time from (select *, row_number() over (partition by gid order by crt_time desc) as rn from tbl_log) t where rn=1; gid | info | crt_time -----+----------------------------------+---------------------------- 0 | 144ccff07b812d0ca5252ae8cbc2ad50 | 2022-08-23 14:59:59.531316 1 | 22fb4e6bb2daa15fcb8b00358bb4f3ad | 2022-08-23 14:59:59.531342 2 | 43761591e939309f1bb9e2b94f642e6d | 2022-08-23 14:59:59.531356 3 | 1751a3a7884685ec2c16926b4e2ad607 | 2022-08-23 14:59:59.531341 4 | 5df93803d19bf3a6bd19b7d017757bed | 2022-08-23 14:59:59.531348 5 | c11384fa2434c67992d14da837f65ac0 | 2022-08-23 14:59:59.531352 6 | ea33278a5f8d75c75ddbcbf7d753367f | 2022-08-23 14:59:59.531355 7 | c98c67d0a08c2f6dc865a291997748d5 | 2022-08-23 14:59:59.531347 8 | 644215ca6c3f2ad0fc1c0387a8e5c4fb | 2022-08-23 14:59:59.53133 9 | d0b554588b4a1d3de9fddcac630234ea | 2022-08-23 14:59:59.531354 10 | 903c0dda9ddfbd241043b8d75b4eaf22 | 2022-08-23 14:59:59.531351 (11 rows) Time: 2230.696 ms (00:02.231)
查看資料結構
postgres=# \d tbl_log Table "public.tbl_log" Column | Type | Collation | Nullable | Default ----------+-----------------------------+-----------+----------+--------- gid | integer | | | info | text | | | crt_time | timestamp without time zone | | |
查看SQL執行計劃:
回傳11行記錄(rows=11), 但是掃描了將近20萬個資料塊(shared hit=16167 read=30562, temp read=72167 written=72315, 耗時707.021毫秒), 并且使用了外部排序(external merge Disk: 288672kB, 耗時4382.093-707.021毫秒).
explain (analyze,verbose,timing,costs,buffers) select gid,info,crt_Time from (select *, row_number() over (partition by gid order by crt_time desc) as rn from tbl_log) t where rn=1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan on t (cost=1342550.98..1505051.08 rows=25000 width=45) (actual time=4382.105..5406.218 rows=11 loops=1) Output: t.gid, t.info, t.crt_time Filter: (t.rn = 1) Buffers: shared hit=16167 read=30562, temp read=72167 written=72315 -> WindowAgg (cost=1342550.98..1442551.04 rows=5000003 width=53) (actual time=4382.103..5406.203 rows=11 loops=1) Output: tbl_log.gid, tbl_log.info, tbl_log.crt_time, row_number() OVER (?) Run Condition: (row_number() OVER (?) <= 1) Buffers: shared hit=16167 read=30562, temp read=72167 written=72315 -> Sort (cost=1342550.98..1355050.99 rows=5000003 width=45) (actual time=4382.093..4997.855 rows=5000000 loops=1) Output: tbl_log.gid, tbl_log.crt_time, tbl_log.info Sort Key: tbl_log.gid, tbl_log.crt_time DESC Sort Method: external merge Disk: 288672kB Buffers: shared hit=16167 read=30562, temp read=72167 written=72315 -> Seq Scan on public.tbl_log (cost=0.00..96729.03 rows=5000003 width=45) (actual time=0.026..707.021 rows=5000000 loops=1) Output: tbl_log.gid, tbl_log.crt_time, tbl_log.info Buffers: shared hit=16167 read=30562 Planning Time: 0.092 ms Execution Time: 5507.738 ms (18 rows) Time: 5508.182 ms (00:05.508)
優化1:
建gid, crt_time desc索引.
postgres=# create index idx_tbl_log_1 on tbl_log (gid,crt_time desc); CREATE INDEX Time: 3530.425 ms (00:03.530)
重新查詢后, 使用了索引, 但是性能并沒有提升多少. 避免了外部排序, 但是依舊有大量的掃描(shared hit=16266 read=517194 written=8941, 耗時2736.351毫秒).
explain (analyze,verbose,timing,costs,buffers) select gid,info,crt_Time from (select *, row_number() over (partition by gid order by crt_time desc) as rn from tbl_log) t where rn=1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan on t (cost=0.43..488005.99 rows=25000 width=45) (actual time=0.036..3116.007 rows=11 loops=1) Output: t.gid, t.info, t.crt_time Filter: (t.rn = 1) Buffers: shared hit=16266 read=517194 written=8941 -> WindowAgg (cost=0.43..425505.99 rows=5000000 width=53) (actual time=0.035..3115.996 rows=11 loops=1) Output: tbl_log.gid, tbl_log.info, tbl_log.crt_time, row_number() OVER (?) Run Condition: (row_number() OVER (?) <= 1) Buffers: shared hit=16266 read=517194 written=8941 -> Index Scan using idx_tbl_log_1 on public.tbl_log (cost=0.43..338005.99 rows=5000000 width=45) (actual time=0.026..2736.351 rows=5000000 loops=1) Output: tbl_log.gid, tbl_log.crt_time, tbl_log.info Buffers: shared hit=16266 read=517194 written=8941 Planning: Buffers: shared hit=18 read=1 dirtied=2 Planning Time: 0.630 ms Execution Time: 3116.041 ms (15 rows)
優化2:
為了解決掃描的問題, 引入遞回查詢, 需要修改SQL.
《重新發現PostgreSQL之美 - 6 index鏈表跳跳糖 (CTE recursive 遞回的詳細用例)》
with RECURSIVE tmp as ( (select tbl_log as t from tbl_log order by gid, crt_time desc limit 1) union all select (select tbl_log from tbl_log where tbl_log.gid > (tmp.t).gid order by tbl_log.gid, tbl_log.crt_time desc limit 1) as t from tmp where tmp.* is not null ) select (tmp.t).* from tmp where tmp.* is not null; gid | info | crt_time -----+----------------------------------+---------------------------- 0 | 144ccff07b812d0ca5252ae8cbc2ad50 | 2022-08-23 14:59:59.531316 1 | 22fb4e6bb2daa15fcb8b00358bb4f3ad | 2022-08-23 14:59:59.531342 2 | 43761591e939309f1bb9e2b94f642e6d | 2022-08-23 14:59:59.531356 3 | 1751a3a7884685ec2c16926b4e2ad607 | 2022-08-23 14:59:59.531341 4 | 5df93803d19bf3a6bd19b7d017757bed | 2022-08-23 14:59:59.531348 5 | c11384fa2434c67992d14da837f65ac0 | 2022-08-23 14:59:59.531352 6 | ea33278a5f8d75c75ddbcbf7d753367f | 2022-08-23 14:59:59.531355 7 | c98c67d0a08c2f6dc865a291997748d5 | 2022-08-23 14:59:59.531347 8 | 644215ca6c3f2ad0fc1c0387a8e5c4fb | 2022-08-23 14:59:59.53133 9 | d0b554588b4a1d3de9fddcac630234ea | 2022-08-23 14:59:59.531354 10 | 903c0dda9ddfbd241043b8d75b4eaf22 | 2022-08-23 14:59:59.531351 (11 rows) Time: 0.603 ms
掃描降低到了47個block, 同時避免了排序. 整體SQL耗時從5508.182毫秒降低到了0.6毫秒.
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ CTE Scan on tmp (cost=61.21..63.23 rows=100 width=44) (actual time=0.061..0.342 rows=11 loops=1) Output: (tmp.t).gid, (tmp.t).info, (tmp.t).crt_time Filter: (tmp.* IS NOT NULL) Rows Removed by Filter: 1 Buffers: shared hit=47 CTE tmp -> Recursive Union (cost=0.43..61.21 rows=101 width=69) (actual time=0.047..0.316 rows=12 loops=1) Buffers: shared hit=47 -> Subquery Scan on "*SELECT* 1" (cost=0.43..0.50 rows=1 width=69) (actual time=0.047..0.048 rows=1 loops=1) Output: "*SELECT* 1".t Buffers: shared hit=4 -> Limit (cost=0.43..0.50 rows=1 width=81) (actual time=0.046..0.047 rows=1 loops=1) Output: tbl_log_1.*, tbl_log_1.gid, tbl_log_1.crt_time Buffers: shared hit=4 -> Index Scan using idx_tbl_log_1 on public.tbl_log tbl_log_1 (cost=0.43..338005.99 rows=5000000 width=81) (actual time=0.045..0.046 rows=1 loops=1) Output: tbl_log_1.*, tbl_log_1.gid, tbl_log_1.crt_time Buffers: shared hit=4 -> WorkTable Scan on tmp tmp_1 (cost=0.00..5.97 rows=10 width=32) (actual time=0.019..0.019 rows=1 loops=12) Output: (SubPlan 1) Filter: (tmp_1.* IS NOT NULL) Rows Removed by Filter: 0 Buffers: shared hit=43 SubPlan 1 -> Limit (cost=0.43..0.58 rows=1 width=81) (actual time=0.019..0.019 rows=1 loops=11) Output: tbl_log.*, tbl_log.gid, tbl_log.crt_time Buffers: shared hit=43 -> Index Scan using idx_tbl_log_1 on public.tbl_log (cost=0.43..240899.23 rows=1666667 width=81) (actual time=0.018..0.018 rows=1 loops=11) Output: tbl_log.*, tbl_log.gid, tbl_log.crt_time Index Cond: (tbl_log.gid > (tmp_1.t).gid) Buffers: shared hit=43 Planning: Buffers: shared hit=48 Planning Time: 0.538 ms Execution Time: 0.391 ms (34 rows)
練習:
更多例子等你反饋, 歡迎聯系我.
甚至你要了解資料分布, 掃描方法; 掌握資料庫的基本原理(存盤結構、索引結構、掃描優化器演算法等)對優化是非常有幫助的, 可以幫助你從根源找問題并提出優化思路.
下面有個例子:
《PostgreSQL join+order by limit的優化例子 - 說明資料分布與掃描方法對優化的關鍵作用》
背景知識:
1 代碼分析
https://www.man7.org/linux/man-pages/man1/perf.1.html
2 計劃分析
https://www.postgresql.org/docs/devel/sql-explain.html
《PostgreSQL explain analyze 火山圖火焰圖 圖形化性能分析軟體 pg_flame》
《PostgreSQL explain, parser, execute 程序資源使用統計分析 - perf , debug , log_planner_stats , log_xxx_stats》
《跨云的K8S cloud native postgresql管理系統 誰在|會用? PG SaaS或工具或插件類產品 誰在|會用? (SQL規整、執行計劃解讀和優化建議、引數優化、AWR、索引推薦、錯誤日志解讀和應對策略)》
《PostgreSQL 查詢當前執行中sql的執行計劃 - pg_show_plans》
3 常用SQL
《PostgreSQL DBA最常用SQL》
《PostgreSQL dba常用擴展函式庫 - pg_cheat_funcs》
《PostgreSQL DBA 日常管理 SQL》
《PostgreSQL 實時健康監控 大屏 - 低頻指標 - 珍藏級》
《PostgreSQL 實時健康監控 大屏 - 高頻指標(服務器) - 珍藏級》
《PostgreSQL 實時健康監控 大屏 - 高頻指標 - 珍藏級》
4 鎖等待分析
《PostgreSQL 14 preview - 支持 lwlock blocking 診斷 - 增加 pg_lwlock_blocking_pid》
《PostgreSQL 誰堵塞了誰（鎖等待檢測）- pg_blocking_pids, pg_safe_snapshot_blocking_pids》
《PostgreSQL 誰堵塞了誰（鎖等待檢測）- pg_blocking_pids》
《PostgreSQL 鎖等待監控 珍藏級SQL - 誰堵塞了誰》
《PostgreSQL 鎖等待排查實踐 - 珍藏級 - process xxx1 acquired RowExclusiveLock on relation xxx2 of database xxx3 after xxx4 ms at xxx》
《PostgreSQL 鎖等待跟蹤》
5 索引推薦
《DB吐槽大會,第35期 - "富人"的煩惱?PG 不會自動選擇索引型別》
《PostgreSQL 自動化后臺并行創建 多索引, 加速匯入速度 - pg_parallizator》
《PostgreSQL 自動化索引 - auto-indexing-PostgreSQL》
《PostgreSQL SQL自動優化案例 - 極簡，自動推薦索引》
《自動選擇正確索引訪問介面(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》
《PostgreSQL 索引推薦 - HypoPG , pg_qualstats》
《powa4 PostreSQL Workload Analyzer - PostgreSQL監控工具、帶WEB展示 - 索引推薦,等待事件分析,命中率,配置變更跟蹤等》
《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) 索引推薦功能使用》
《PostgreSQL 9種索引的原理和應用場景》
《PostgreSQL 復雜SQL執行計劃優化修正插件: pg_plan_inspector , pg_plan_advsr , pg_hint_plan , pg_store_plans》
6 memory context 分析
《PostgreSQL 15 preview - pg_log_backend_memory_contexts 增強, 可列印輔助行程(vacuum, checkpointer等)的記憶體資訊》
《PostgreSQL 14 preview - 列印其他會話的memory context, 診斷記憶體消耗問題 - pg_log_backend_memory_contexts(pid)》
《PostgreSQL 14 preview - 查看backend process的memory context》
《PostgreSQL cheat functions - (記憶體背景關系\planner內容\memory context等常用函式)》
7 固定、篡改、保存執行計劃. (對于SQL不能修改的場景, 解決SQL因執行計劃不正確產生的問題.)
《PostgreSQL hint pg_hint_plan 的詳細用法》
《PostgreSQL Oracle 兼容性之 - SQL OUTLINE插件sr_plan (保存、篡改、固定 執行計劃)》
8 動態執行計劃
《[未完待續] PostgreSQL PRO 特性 - AQO(機器學習執行計劃優化器)》
9 膨脹檢查與清理
《PostgreSQL pgstattuple - 檢查表的膨脹情況、dead tuples、live tuples、freespace》
《解讀用戶最常問的PostgreSQL垃圾回收、膨脹、多版本管理、存盤引擎等疑惑 - 經典》
《PostgreSQL 收縮膨脹表或索引 - pg_squeeze or pg_repack》
《PostgreSQL 垃圾回收原理以及如何預防膨脹 - How to prevent object bloat in PostgreSQL》
《PostgreSQL 如何精確計算表膨脹(fsm,資料塊layout講解) - PostgreSQL table exactly bloat monitor use freespace map data》
《如何使用5why分析法發現資料庫膨脹現象背后的本質?》
《膨脹點解釋 - 全域catalog,庫級catalog,普通表,wal檔案 - 哪些垃圾(dead tuple), wal檔案不能被回收reuse - 什么情況下可能膨脹》
10 自動收集統計資訊配置
autovacuum
二、整體慢
資料庫整體變慢, 最核心的是定位罪魁禍首, 進行優化, 最后需要判斷是否在業務層面、資料庫SQL層面、資料庫內核層面 都已經無法優化(則可能需要升級硬體、或者使用分布式資料庫、擴容等),
常用分析工具與方法:
- pg_stat_statements, 找出最消耗資源的TOP SQL
- performance insight, 活躍會話、等待事件采樣快照記錄, 用于分析活躍會話數超過CPU 核數的時間段, 當時資料庫系統的等待情況. SQL的分布等.
- perf, 借助抓取系統呼叫、用戶行程函式呼叫等統計資訊, 生成資料庫高峰、或者問題時刻的代碼級耗時統計的火焰圖, 找到代碼級別的瓶頸. 通常用于分析表面上很難察覺的問題.
- pg_stat_ pg_statio_ 統計資訊, 找出CPU消耗、IO消耗不合理的表
- 找出膨脹索引與膨脹表, 垃圾清理不及時的原因分析
- 找出統計資訊偏差, 配置自動收集統計資訊
- 引數不正確、優化器校準因子不正確等問題
- 《DB吐槽大會,第12期 - 沒有自動成本校準器》
例子:
《PostgreSQL性能優化綜合案例講解 - 1》
《PostgreSQL性能優化綜合案例講解 - 2》
練習:
更多例子等你反饋, 歡迎聯系我.
背景知識:
1 找出最消耗資源的SQL
《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL優化內容) - 珍藏級 - 資料庫慢、卡死、連接爆增、慢查詢多、OOM、crash、in recovery、崩潰等怎么辦?怎么優化?怎么診斷?》
2 找出問題時間段, 找出問題時間段的等待事件瓶頸, 分析等待事件的SQL分布, 分析慢SQL的等待事件分布
《PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念與實作解讀 - 珍藏級》
《PostgreSQL pg_stat_statements AWR 插件 pg_stat_monitor , 過去任何時間段性能分析 [推薦、收藏]》
https://github.com/postgrespro/pg_wait_sampling
《PostgreSQL 13 preview - wait event sample - 等待事件統計(插件,hook) - ASH - performance insight》
《PostgreSQL 等待事件 及 等待采樣統計 (pg_wait_sampling) 發布新版本 1.1.2》
《PostgreSQL 等待事件 及 等待采樣統計(pg_wait_sampling)》
3 找出代碼瓶頸
《PostgreSQL 原始碼性能診斷(perf profiling)指南(含火焰圖生成分析FlameGraph) - 珍藏級》
《PostgreSQL explain, parser, execute 程序資源使用統計分析 - perf , debug , log_planner_stats , log_xxx_stats》
4 找出CPU消耗、IO消耗不合理的表
《PostgreSQL pg_stat_ pg_statio_ 統計資訊(scan,read,fetch,hit)原始碼解讀》
5 找出膨脹索引與膨脹表
《PostgreSQL pgstattuple - 檢查表的膨脹情況、dead tuples、live tuples、freespace》
《解讀用戶最常問的PostgreSQL垃圾回收、膨脹、多版本管理、存盤引擎等疑惑 - 經典》
《PostgreSQL 收縮膨脹表或索引 - pg_squeeze or pg_repack》
《PostgreSQL 垃圾回收原理以及如何預防膨脹 - How to prevent object bloat in PostgreSQL》
《PostgreSQL 如何精確計算表膨脹(fsm,資料塊layout講解) - PostgreSQL table exactly bloat monitor use freespace map data》
《如何使用5why分析法發現資料庫膨脹現象背后的本質?》
《膨脹點解釋 - 全域catalog,庫級catalog,普通表,wal檔案 - 哪些垃圾(dead tuple), wal檔案不能被回收reuse - 什么情況下可能膨脹》
三、偶爾慢
偶爾某些很快的SQL會抖動(變得很慢). 針對這個情況, 需要找到這條SQL變慢的時刻, 當時資料庫的整體資源消耗的情況, 以及當時這條SQL的執行計劃、鎖等待的情況.
- 比較典型的例如prepare, 輸入引數不同可能會有不一樣的資源消耗, 或者執行計劃不正確導致.
- 又或者遇到較長的鎖等待. (包括低級鎖lwlock、或者高級鎖lock)
常用分析工具與方法:
- auto_explain, 記錄執行時間超過閾值的SQL、函式的執行計劃, 執行程序的完整資料(buffer, hit, read, write, rows, time等)
- log_lock_waits, 記錄鎖等待時間超過lock_timeout的SQL, 以及堵塞它的PID.
- performance insight, 活躍會話、等待事件采樣快照記錄, 用于分析SQL抖動的對應時間段的資料庫實體整體情況、SQL當時的等待情況.
例子, 使用pg_stat_activity活躍會話快照, 分析過去抖動時刻的慢SQL等待事件:
《PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念與實作解讀 - 珍藏級》
練習:
更多例子等你反饋, 歡迎聯系我.
背景知識:
1 分析過去某個時刻的執行計劃抖動.
《PostgreSQL 函式除錯、診斷、優化 & auto_explain & plprofiler》
2 找出問題時間段, 找出問題時間段的等待事件瓶頸, 分析等待事件的SQL分布, 分析慢SQL的等待事件分布
《PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念與實作解讀 - 珍藏級》
《PostgreSQL pg_stat_statements AWR 插件 pg_stat_monitor , 過去任何時間段性能分析 [推薦、收藏]》
https://github.com/postgrespro/pg_wait_sampling
《PostgreSQL 13 preview - wait event sample - 等待事件統計(插件,hook) - ASH - performance insight》
《PostgreSQL 等待事件 及 等待采樣統計 (pg_wait_sampling) 發布新版本 1.1.2》
《PostgreSQL 等待事件 及 等待采樣統計(pg_wait_sampling)》
其他
如果是greenplum可以參考: 《Greenplum explain analyze 解讀 + 深度明細開關 - 珍藏級》
或者grep我的github readme頁面greenplum,性能,優化之類關鍵字.
除了有問題后再分析, 在問題發生前也可以做很多事情, 例如環境部署、引數配置都很重要. 可以在我的github里搜索相關文章.
期望 PostgreSQL 增加什么功能?
PolarDB for PostgreSQL云原生分布式開源資料庫
PostgreSQL 解決方案集合
作者丨digoal
本文來自博客園，作者：古道輕風，轉載請注明原文鏈接：https://www.cnblogs.com/88223100/p/PostgreSQL-Database-Performance-Analysis-and-Optimization-Method.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/519094.html
標籤：其他