穩定PG SQL的執行速度

大家用 Oracle資料庫的時候，根本不需要過多的去考慮資料庫物理結構以及O S 物理結構方面的最佳化。因為 Oracle把一切都幫我們做好了，只需要交給Oracle足夠容量，足夠能力的物理資源就一切O K 了。不過作為近幾十年前就開始使用 Oracle資料庫的我們這一代D BA ，還是經歷過很多這樣的最佳化案例的。在那個年代，曾經透過底層儲存的重新條帶化設計，讓一套 I O 負載極高的資料庫系統的批處理作業效能提高數倍。

現在我們如果不使用 Oracle資料庫了，我們要把應用從Oracle遷移到國產資料庫上了，那麼這些老手藝可能又有些作用了。不過與那個時代不同的是，現在硬體的能力已經百倍的提升了，儲存系統的全域性I O 打散能力也成為了標配，因此再也不需要像我在二十多年前那樣為了一點點的 I OPS 去精打細算了。

今天這個話題雖然我已經把討論範圍縮小到了物理結構最佳化上，不過還是太大，我不大可能在一個一千多字的文章裡把所有的問題都講清楚，因此我們今天還是縮小到一個比較小的話題上吧，那就是如何透過底層物理結構的最佳化讓關鍵業務系統在 P G 類資料庫上平穩執行。這裡講的是 P G 類資料，除了社群版的 P G 外，還有大量基於 P G 的開源、國產資料庫產品，包括華為的 G AUSSDB 系列，瀚高、金倉、神通、優炫、亞信 A NTDB 、中國移動磐維等，都是 P G 類資料庫。甚至今天我討論的部分內容對於 M YSQL 、達夢、 S QL SERVER 等沒有使用 D I O的資料庫也是有一定作用的。

最近我經常聽說某些客戶把資料庫從 Oracle遷移到國產資料庫上後，一些關鍵業務忽急忽緩，檢查執行計劃也沒啥問題，作業系統層面也沒有明顯的瓶頸或者慢的因素。實際上這與資料庫沒有使用D IO 有很大的關係。對於沒有使用 D IO 的系統，當資料沒有在 DB CACHE 中命中的時候，會產生物理讀。不過這個物理讀並不一定真正的從物理儲存介質上讀取，如果資料在 L INUX 的 O S CACHE 裡，那麼可以直接從 C ACHE 中獲取，這種物理讀是很迅速的，如果 O S CACHE 沒有命中，那麼就只能真正的從物理儲存介質中去讀取了，相對於直接從記憶體中讀取，這種物理讀是較慢的。因為 O S CACHE 沒辦法按照我們的資料庫訪問需求去緩衝資料，因此 O S CACHE 的目的性並不強。這種 忽急忽緩大多數集中在讀操作上的，不過有時候寫也會出現抖動，那是因為 O S 層面的的後臺程式、檢查點以及 W AL 或者檔案寫的 F SYNC 都會讓 O S 產生一個抖動，從而對事務提交或者索引更新產生效能影響。在併發量較大、存在熱點寫資料的應用中，就很可能會產生一定的影響了。

我們無法去改變上面所說的 D OUBLE BUFFERING 的問題，這隻能透過國產資料庫廠商的努力在自己的資料庫產品裡全面引入 D IO 來解決了。實際上目前為止只有 Oracle能夠比較好的把控D IO ，除此之外，哪怕是 S QL SERVER 這種老牌的商用資料庫，雖然可以支援 DIO ，但是也不建議啟用 D IO 。

可能有些朋友覺得偶爾出現幾條 S QL 執行變慢沒啥所謂，這可能和你看到的應用場景有關。如果是股票交易或者銀行交易出現了某個時段幾百筆交易延時異常，那麼對於這個企業來說就是運維事故了。如果一條核心交易的 S QL ，平時執行只需要 5毫秒，抖動時就可能變成四十毫秒，現在網聯對金融機構的交易超時監控十分嚴格，一次這樣的抖動足以產生一次告警了。

要想避免此類抖動，必須在物理結構上做好最佳化。我今天提出幾點最佳化的建議。首先是對底層儲存的最佳化，如果不是使用集中式儲存，那麼還需要考慮 512E 和 4 KN 等現代磁碟扇區大小的問題以及相關的對齊問題。今天篇幅有限，就不展開討論了，有興趣的朋友可以參考我以前寫過的一篇文章《原來 A DVANCED FORMAT HDD 已經普及了》（ https://mp.weixin.qq.com/s?__biz=MzA5MzQxNjk1NQ==&mid=2647848163&idx=1&sn=0f27f69e5d337a3873d6f3cdaa841a24&chksm=88786877bf0fe161178bed2cc05c2db84265ae3356ade0847e81b2e331e299d580c89dc31c9f&token=1445168299&lang=zh_CN#rd ）。

除此之外，怕抖動的核心交易系統儘可能使用比較迅速的儲存介質，比如 NVME SSD ，儘可能降低平均 I O 延時是從根本上降低 I O 抖動帶來的問題的便宜的方法，不要覺得 S SD 很貴，實際上在這裡多花點錢可以在其他地方少花更多的錢。

 

第二個需要關注的就是儲存的規劃，透過使用獨立的表空間和檔案系統，讓相互影響，可能導致執行毛刺的應用之間的衝突儘可能地減少。如果系統不重要，或者抖動無所謂，那麼不需要做這方面的最佳化，而如果這個系統是十分關鍵的核心繫統，那麼在這方面做再多的工作都是值得的。在二十多年前，搞 Oracle   7的時候，這些手段我也都用過。

對於寫入十分重的系統， W AL 儘可能獨立儲存於高效能的獨立磁碟上，避免與其他讀寫 I O 衝突。小型熱表也可以儲存於獨立的表空間裡。相對靜態，但是會大規模掃描的冷資料獨立儲存，訪問十分頻繁的表的索引與資料分開等都是可以採取的 I O 隔離手段。具體要根據業務的特點去做規劃。前面討論的都是從本地磁碟的角度去考慮的，實際上目前大多數系統使用集中式儲存，很多 I O 延時穩定性的問題可能要考慮後端儲存的規劃與最佳化，大家要注意這個問題。後端儲存分過來的盤和實際的物理盤是不同的。

第叄個需要關注的問題是 O S 層面針對性的最佳化，這一點大家可以參考我的另外一篇文章《 Postgresql資料庫最佳化上該考慮些什麼》（ https://mp.weixin.qq.com/s?__biz=MzA5MzQxNjk1NQ==&mid=2647849429&idx=1&sn=2776253a04b780d090bfe7a03784345c&chksm=88786541bf0fec573182c704e41c0adddc9d96ee0af694cea263d685ce51fb3f9b4aa7700f52&token=1445168299&lang=zh_CN#rd   ），這裡我就不重複介紹了。

第四個要考慮的是 S HARED_BUFFERS 的設定，如果我們在執行一個十分關鍵的核心交易系統，那麼建議還是配置大一點的實體記憶體，並將 S HARED BUFFFERS 設定的足夠大，儘可能地讓熱點資料都能夠長期緩衝在資料庫緩衝區裡，從而穩定關鍵業務地執行效率。但是業務系統十分複雜，是不是也會產生一些對大型冷資料的掃描操作。這些操作儘可能地放到只讀備機上去做，而不要影響核心業務系統的資料庫緩衝。一種更好的設計是將這些可能對核心交易產生影響的資料從核心資料庫中剝離出去，另外建個庫，那樣的話對核心交易的影響就小多了。