本文將介紹三種資料庫變慢場景的分析與優化方法. 1、已經定位出的特定慢SQL 2、整個資料庫實例(幾乎所有SQL)變慢, 或者某些時候整個資料庫實例大面積SQL變慢(大面積抖動) 3、某些正常情況下很快的SQL偶爾會變慢(抖動) ...
背景
本文將介紹三種資料庫變慢場景的分析與優化方法.
- 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》
《PostgreSQL 查詢當前執行中sql的執行計劃 - pg_show_plans》
3 常用SQL
《PostgreSQL dba常用擴展函數庫 - pg_cheat_funcs》
《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 - 誰堵塞了誰》
5 索引推薦
《DB吐槽大會,第35期 - "富人"的煩惱?PG 不會自動選擇索引類型》
