經過客戶的同意,我可以分享一個去年 PostgreSQL 優化的案例。

Query (查詢) 是資料庫最常處理的環節,也通常是效能瓶頸之處。PostgreSQL 在處理 Multi-table JOIN 時,會有兩種觸發機制。一是 exhaustive query planning (全面查詢規劃),能保證獲得最佳的查詢執行計畫。另一是 genetic probabilistic search (遺傳機率搜尋),不能保證最佳的查詢執行計畫。

1. 為什麼會分成兩種?

因為若 JOIN 的 Table 過多時,採用 exhaustive query planning 會非常耗時,例如 16 個 Table 就有 16! 的排列可能。此時為了避免計算查詢執行計畫過久,所以 PostgreSQL 引入了 genetic probabilistic search,將基因演算法帶入查詢執行計畫的計算,以減少計算時間,但也因為演算法的特性,所以產出的最終執行計畫不一定是最佳的。

2. 客戶遇到的問題

客戶的資料量 30 TB 以上,需要處理比較複雜的查詢。在處理某些 Query 時速度不夠快,而且查詢時間快慢不一,非常難以掌控,用戶抱怨連連。

原則上,遇到 Query 問題時,正規方式應該是 (1) 修改有問題的 Database Schema (2) 修改有問題的 Query。但因為客戶的工程師對於資料庫瞭解程度不一 (沒辦法要求所有工程師都懂),再加上有第三方廠商開發的系統。故以正規方式處理會較耗時,想知道有什麼其它方式可以有效處理。

3. 解決方式

我分析系統架構、資料庫配置及有問題的 Query 後,猜測是遇到 genetic probabilistic search 的問題。因為 genetic probabilistic search 需要演化,所以每次的查詢執行計畫不一,導致整體查詢時間時快時慢,而且若有多台 PostgreSQL 的情況下會更嚴重,因為每台都有各自的基因演化,平均查詢時間更不穩定。

所以我建議將 geqo_threshold 調整至 16 (預設 12),join_collapse_limit 調整至 14 (預設 8),而調整後的查詢時間平均快了 10 倍以上,每次查詢時間也變穩定了,客戶表示非常滿意。( 有興趣的還有另個可調整參數 from_collapse_limit ) 其中 geqo_threshold 指的是,當 JOIN 的數量等於或大於設定值時,PostgreSQL 才會啟用 genetic probabilistic search。把值調大,意謂著讓資料庫盡量採行 exhaustive query planning,以獲得最佳查詢執行計畫。而 join_collapse_limit 指的是,當 JOIN 的數量低於設定值時,就會將 FULL JOIN 以外的 JOIN 抹平到 FROM 中。

現代資料庫硬體的效能已經夠好,理論上調大沒有問題 (實際仍建議先分析)。再者,genetic probabilistic search 雖然比較快能算出查詢執行計畫,但可能不是最佳的,且若每次的 Query 條件都不一定時,會更難找出最佳查詢。

例如 JOIN 的 Table 雖然不變,但 JOIN 或 WHERE 的條件可能隨時應需求改變時 (比如查詢近一年的資料與查詢一年前的資料就是 JOIN 的 Table 一樣,但條件不同),會導致 genetic probabilistic search 更難得出最好的結果。

以上經驗分享,若後續有更多客戶願意,我會分享更多。