PG資料庫伺服器的CPU使用率突然升高該如何分析

現在基於PG或者脫胎於PG的國產資料庫越來越多，再加上PG社群版使用者也在快速增長，因此多學點PG的知識對於DBA今後的轉型來說，還是挺有用的，因此這幾天我們多討論一些PG相關的問題。昨天我們討論了PG IO最佳化方面的問題，今天我們就來討論一個核CPU有關的問題。今天的議題是，如果PG資料庫伺服器的CPU使用率突然升高，我們應該從哪幾個方面去分析。

如果遇到資料庫伺服器CPU使用率突然大幅增高或者過高的問題，不論是哪種資料庫，我們都要首先檢視一下作業系統上有沒有非資料庫的程式使用了過高的CPU資源，這個使用TOP工具就可以實現了，不要因為SWAP、大規模CACHE DROP等操作引發的CPU使用率突增讓資料庫來背鍋了，搞的你分析了一大圈，發現完全和資料庫無關。幾年前我遇到過一個案例，一個使用者讓我幫助分析他們的CPU怎麼突然100%了，我也是沒注意這些問題，直接在資料庫裡找問題，最後分析了一大圈，發現負載啥的都和前一天沒啥區別，就是CPU使用率多了30%。後來實在沒招了，用TOP看了一下，發現了一個異常的程式，居然在做科學計算，這個程式正好消耗了30%多的CPU資源。最後客戶那邊確認了一下，十分不好意思的說，是一個幾年前設定的一個crontab任務忘了關閉了，才導致了今天的問題。幾年前，他們搞資料庫合庫，只要CPU使用率在一個月中沒有一天業務高峰期超過35%的資料庫，就必須合併到其他資料庫中，從而節約資源。他們幹了幾套系統後覺得太辛苦了，部門領導就想了個招，在月底業務高峰期的時候，讓一個科學計算的任務跑上幾個小時，讓符合合併的資料庫變少一點，大家也少乾點活。沒想到這臺伺服器上的任務忘了關了，而這些年這套系統的資料量越來越大，負載也越來越高了，沒想到這個月底業務很忙，居然把CPU跑爆掉了。

另外如果在一臺物理機上跑多個資料庫例項，我們就不能只看一個資料庫的情況了，而是要看多個資料庫的總體負載。

除此之外，如果排除了這些問題，單單來討論PG資料庫，如果真的是PG資料庫引發了CPU突然增加，我們應該如何去分析呢?今天我簡單的羅列了一些常用場景，遇到問題的時候，DBA可以一條條的進行排除。

首先，也是可能性最大的方面，出現大查詢或者較高併發量的SQL執行計劃變壞：如果資料庫中的某個查詢或某組查詢的複雜度增加，則可能導致CPU使用率的增加;一條常見SQL的執行計劃錯誤也會導致執行開銷增加，雖然單條SQL的延時仍然在業務能夠忍受的範圍內，但是總體CPU消耗會大幅增大，如果CPU資源出現瓶頸，那麼系統整體效能都會嚴重下降。

第二，出現嚴重的資源競爭：如果多個連線或會話同時請求大量的資料，則可能會產生資源競爭，甚至引發spinlock等自旋鎖爭用，從而導致CPU使用率的增加，分析這方面的原因透過PG的等待事件進行分析是比較有效的。

第三，索引缺失導致的SQL執行計劃不夠最佳化：如果資料庫表缺少索引，則查詢操作將需要掃描整個表，從而導致CPU使用率的增加。

第四，磁碟IO瓶頸：如果資料庫的磁碟IO不能滿足需求，則可能導致CPU使用率的增加。這一點可能會讓朋友們感到詫異，IO瓶頸的時候，會話不都在等待IO嗎?怎麼會引發CPU的問題呢?PostgreSQL 使用同步阻塞 IO(Buffered IO)，同步阻塞 IO 意味著在完成 IO 操作之前，PostgreSQL 會阻塞等待 IO 操作的完成。當資料庫伺服器需要讀寫磁碟資料時，它會阻塞其他操作，直到 IO 操作完成。在這種情況下，IO延時會比平時高很多，CPU使用率中的IOWAIT的比例也會比較高。

第五，資料庫維護任務：如果資料庫正在進行大型的維護任務(例如VACUUM，ANALYZE等)，則可能導致CPU使用率的增加。

第六，緩衝汙染：這種情況出現機率較低，而且出現後也很難被發現。當快取中的資料大多數是很少使用的資料時，就會出現快取汙染，導致頻繁的快取未命中，導致 CPU 利用率增加。當快取汙染髮生時，CPU 會花更多的時間從儲存中讀取資料，而花更少的時間從快取中執行指令。 這會導致整體系統效能下降和 CPU 使用率增加。對於PG這種shared_buffers配置佔OS比例較低，採用DOUBLE BUFFER機制的資料庫系統，出現緩衝汙染的機率遠大於Oracle等資料庫。緩衝汙染一旦產生，在SQL執行計劃不發生變化的情況下，也會產生較為嚴重的效能下降，因此需要避免。對於經常出現類似問題的資料庫，可以透過使用各種預熱外掛來不斷預熱熱資料，從而防止緩衝汙染。

第七，某張經常全表掃碼的小表因為膨脹而突然變大：這種情況出現機率較低，不過也比較容易出現，如果某張表關閉了VACUUM並且常有UPDATE操作，那麼經過一段時間積累，可能引發效能問題。

導致CPU使用率突然增高的可能性還有很多，不過對於PG資料庫來說，大部分此類問題都是大併發SQL或者SQL執行計劃變壞引發，加強SQL問題的監控一般來說能解決大多數PG的CPU問題。今天時間有限，僅僅討論了一下常見的故障場景。不斷積累這方面的知識庫是企業和DBA應該做的事情，如果能夠透過社群共同積累此類問題，那就會事半功倍。也希望大家有興趣加入我們的DBAIOPS社群，共同來做這項工作。這項工作的成果會發布在D-SMART社群版中，我們也會定期透過文章彙報彙總的情況。