【DB吐槽大會】第48期 - PG 性能問題發現和分析能力較弱

背景

1、産品的問題點

• PG 性能問題發現和分析能力較弱 1

• • 缺乏基準

• • • 為什麼要定義基準? 有了基準才有度量标準, 不會出現為了優化而優化的情況, 走火入魔.
    • 《産品經理：依存度、規模化、可度量、周期性》

• • 宏觀分析較弱

• • • pg_stat_statements

      分析出來TOP SQL, 它合理不合理都需要深入分析.
    • 缺乏

      p99, p90

      這類名額

• • 微觀分析較弱

• • • 例如連接配接數激增, 是業務行為還是RT抖動、鎖等待導緻?
    • SQL性能問題如何分析? 怎麼優化?

• • 缺乏如何優化的報告
  • 缺乏可提升多少的預測

• • • 為什麼需要可預期? 隻有可預期的目标才不是盲盒, 才不會有驚訝, 才能用于決策到底要不要實施優化. 預期目标需要有資料支撐、邏輯支撐.

• PG 性能問題發現和分析能力較弱 2

• • 沒有内置的 AWR, 較難發現宏觀問題 (等待事件)
  • 沒有内置的 性能洞察, 較難發現指定時間段的問題
  • 缺少 trace功能, 類似Oracle (10046, 10053) , 較難診斷SQL問題. trace dev para, auto explain, rsq, lock, query, iotiming, ...

• • • 很多診斷需要編譯時定義宏

      TRACE_SORT、LOCK_DEBUG、BTREE_BUILD_STATS、WAL_DEBUG

    • https://www.postgresql.org/docs/14/runtime-config-developer.html

• • 内置的probe使用門檻非常高, 需要開啟debug 、需要使用dtrace或者stap 設定探針進行分析
  • 隻有重量鎖等待日志列印, 缺少LW鎖等待統計, 并且沒有視圖分析SQL級别的等待事件, 等待事件需要到log中查詢,

• • • 開啟

      log_lock_waits

      , 并且隻 記錄超過

      deadlock_timeout

      的SQL.

• • 對SQL的單點問題分析較弱, SQL在過去發生的問題很難分析. (是執行計劃的問題、鎖等待的問題、還是資源競争的問題?)

• • • 隻有鎖等待可能被記錄下來, 執行計劃不會被記錄, 每個執行node花費的時間、掃描的blocks、傳回的記錄數, OP耗費的時間等都需要記錄分析 :
    • 執行計劃的記錄需要開啟auto explain , 設定執行逾時門檻值, 并且需要等待問題再次發生, 而且不能針對單個sql的逾時時間進行門檻值設定, 隻能設定全局門檻值. 每條SQL的執行時長需求不一樣, 單個門檻值無法滿足需求. (例如有些SQL就是分析型的, 本身就慢. )

2、問題點背後涉及的技術原理

• 1 基準是什麼? 如何定義?

• • 如何定義标準 1 業務角度: SQL ID, QPS, RT

• • • QPS 業務相關, 請求量
    • RT 資料庫相關, 響應速度

• • 如何定義标準 2 DB角度: CPU使用率, IO使用率, 網絡使用率, 空間使用率

• 2 資源水位如何?

• • CPU, IO, 網絡, 空間

• 3 資料庫有性能問題嗎?

• • 宏觀

• 4 什麼問題?

• • 微觀

• 5 影響哪些業務(SQL ID次元)? 比正常情況(标準)差了多少?
• 6 什麼時間發生的?
• 7 為什麼會有這個問題?
• 8 如何解決?
• 9 預期優化後的SQL RT和QPS能提升多少? 能降低多少資源使用率(水位)?
• 10 如何規避同類問題?
• 11 如何提前發現問題?

3、這個問題将影響哪些行業以及業務場景

• 通用

4、會導緻什麼問題?

• PG提供的性能問題發現和分析手段有限, 發現問題的門檻較高, 需要專業DBA

5、業務上應該如何避免這個坑

• 宏觀上監測資源有沒有達到瓶頸: CPU使用率, IO使用率, 網絡使用率, 空間使用率.

• • 《Use PostgreSQL collect and analyze Operation System statistics》

• 根據業務給出的測試模型、測試資料、并發等資料, 壓測資料庫性能基準: sql id, qps, rt等名額

• • pgbench + 《PostgreSQL 如何快速建構 海量 逼真 測試資料》

• SQL層面監測top SQL, 按TOP SQL逐條分析有沒有優化空間.

• • 《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL優化内容) - 珍藏級 - 資料庫慢、卡死、連接配接爆增、慢查詢多、OOM、crash、in recovery、崩潰等怎麼辦?怎麼優化?怎麼診斷?》

• 對于過去的問題, 開啟

  io timing, auto_explain, log_lock_waits

  . 等待問題再次發生, 從日志中分析性能抖動的原因.

• • 《PostgreSQL 函數調試、診斷、優化 &

    auto_explain

    & plprofiler》

• 現場分析SQL問題, 開啟各個宏、開啟debug、開啟各個跟蹤參數, 分析SQL問題所在.

• • 《PostgreSQL 相容Oracle

    sql_trace

    10046 10053 方法 - SQL診斷跟蹤》

• AWR插件

• • 《PostgreSQL

    pg_stat_statements

    AWR 插件

    pg_stat_monitor

    , 過去任何時間段性能分析 [推薦、收藏]》

• 性能洞察功能

• • 《PostgreSQL Oracle 相容性之 - performance insight - AWS performance insight 理念與實作解讀 - 珍藏級》

• postgrespro的插件pgpro-pwr

• • https://postgrespro.com/docs/enterprise/13/pgpro-pwr

• systemtap , dtrace

• • 《Systemtap EXP: PostgreSQL IN-BUILD mark Class 5 - read|write relation》

6、業務上避免這個坑犧牲了什麼, 會引入什麼新的問題

• 門檻非常高, 而且有些需求不能很好的解決:

• • 基準
  • 問題優化後的提升預測

  • auto_explain

    單一門檻值問題
  • 等待事件無法統計
  • 不支援自動化推薦解決方案

• debug編譯、宏、

  auto_explain

  、iotiming都會引入開銷

7、資料庫未來産品疊代如何修複這個坑

• 希望全方位内置基準、性能洞察、分析、自動化推薦優化方法、優化效果預測等能力.

https://github.com/digoal/blog/blob/master/202109/20210922_03.md#postgresql-%E8%AE%B8%E6%84%BF%E9%93%BE%E6%8E%A5 https://github.com/digoal/blog/issues/76