【DB吐槽大會】第55期 - PG SQL無法穿越

背景

1、産品的問題點

• PG SQL無法穿越

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

• PG SQL 的執行計劃是怎麼生成的?

• • 通過SQL統計資訊、結合PG的一些代價系數參數設定、通過公式計算cost, 最後選擇cost最低的plan作為plan tree. (多表JOIN觸發geqo的除外)

• PG SQL 是按什麼執行計劃執行的?

• • 如果時generic plan, 則按cached plan執行.
  • 如果cached plan算出來的代價大于custom plan的avg(cost), 則使用custom plan(相當于硬解析).

• • • 《執行計劃選擇算法 與 綁定變量 - PostgreSQL prepared statement: SPI_prepare, prepare|execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》

• 使用綁定變量時就一定會用會話中已經緩存的執行計劃嗎?

• • 不一定, 參考如上. 如果cached plan算出來的代價大于custom plan的avg(cost), 則使用custom plan(相當于硬解析).

• 怎麼知道過去某個時刻當時SQL的執行計劃?

• • 不知道, 除非列印出來. 例如, 使用auto_explain插件

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

• 通用

4、會導緻什麼問題?

• 無法輕松了解SQL在過去某個時刻的執行計劃, 排查因為執行計劃問題導緻的性能抖動非常困難.

• • 例如: 統計資訊未及時更新, 導緻的執行計劃問題
  • 例如: 資料傾斜, 并且産生plan cache的前幾次導緻執行計劃運算有問題.

• • • 《PostgreSQL plan cache 源碼淺析 - 如何確定不會計劃傾斜》

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

• 可以開啟auto_explain插件, 記錄抖動SQL的執行計劃以及每個NODE的開銷, 耗時, IO, 命中, 等.
• 避免plan錯誤的一些手段

• • 縮短長連接配接使用生命周期, 避免錯誤的plan cache造成長時間的影響.
  • 加快統計資訊收集頻率, 避免統計資訊不及時造成的query plan不正确.
  • 分析型的業務設定plan_cache_mode為force_custom_plan, 避免大量資料的變化統計資訊頻繁變化, 導緻cache plan不争氣的問題. force_custom_plan要求每次執行SQL時都重新生成query plan.

• • • 《PostgreSQL 11 preview - 增加強制custom plan GUC開關(plan_cache_mode)，對付傾斜》

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

• auto explain 開啟後, 會打開時間計數器, 影響全局. 導緻性能下降.

• • 《Linux 時鐘精度 與 PostgreSQL auto_explain (explain timing on Linux時鐘開銷估算)》

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

• 希望核心可以支援統計資訊、中繼資料資訊快照功能，用于回放SQL，得到過去的執行計劃資訊。
• 當query執行計劃發生變化時, 通過參數控制, 例如SQL執行時間抖動超過多少時, 可以将前後的plan tree列印到日志中, 同時輸出類似auto_explain的詳細内容.

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