資料庫年齡告警

作者簡介

PostgreSQL（後簡稱PG）中，每張表都是有年齡的。那麼就像人一樣，上了年紀是容易出事的，近期我們就在生產環境中遇到了這麼個事兒。

1

現象

2017年12月25號18:06:11，收到告警簡訊，告警內容為「Database max age is over 500000000」。

2

資料庫年齡的含義

PG資料庫年齡其實指的是資料庫的事務ID（後面簡稱xid），PG的MVCC事務依賴於比較xid值的大小來實現：如果一個行版本的插入xid大於當前事務的xid，它就是"屬於未來的"，那麼就不應該對當前事務可見。

如上圖，xid類型是uint32，可以理解為一個沒有端點的環。一個長時間（超過40億個事務）運行的集簇會遭受到事務ID回卷問題，即xid計數器回卷到0（注）。

一旦一個行版本創建時被分配了一個特定的普通 XID，該行版本將成為接下來20億個事務的"過去"（與我們談論的具體哪個普通 XID 無關）。如果在20億個事務之後該行版本仍然存在，它將突然變得好像在未來。

為了避免發生這種情況，PostgreSQL會在資料庫年齡離20億還差100w的時候就會自動變成只讀，只允許超級用戶以單用戶模式登錄進來執行清理操作。所以，有必要提前清理每個資料庫中的表。

這個清理動作由autovacuum進程來周期性地實施。VACUUM會把行標記為凍結，這表示它們是被一個在足夠遠的過去提交的事務所插入，從MVCC的角度來看，效果就是該插入事務對所有當前和未來事務來說當然都是可見的。

註：事實上，0,1和2是PG保留的3個xid，普通事務ID是從3開始的，回卷也是回到3，為了便於理解，PG手冊中沒有過多說明。

3

初步分析

告警的意思是當前業務庫的年齡已經超過了5億。這個不合理，PG的autovacuum_freeze_max_age參數，控制需要被凍結的行的最大年齡，默認值是2億，即對於年齡超過2億的行，PG的autovacuum進程（自動回收清理垃圾的進程）會自動掃描全表，把老的行給凍結掉。所以生產庫運行的半年來，年齡一直維持在2億附近，那麼為什麼突然autovacuum就沒有效果了呢？繼續分析。

●定位高齡表

登錄生產環境的業務庫，從系統表pg_class中查看當前年齡最大的表有哪些。從下面的結果來看，年齡超過5億的表有3張，而且名字一樣，從命名方式來看，應該是臨時表。進一步查看錶定義，通過schema確認，這3張表都是臨時表。

●確認異常發生時點

找到問題點之後，回到監控，查看數據年齡是從什麼時間開始出現異常的。查看zabbix中資料庫年齡的監控，發現資料庫的年齡是從12月21號凌晨3點左右開始逐漸升高，到告警發生已經持續升高了4天。照這個速度，如果不解決問題，20天左右就會耗盡xid，導致PG強制關閉。

4

臨時表問題

那麼問題就集中在這個臨時表上了，臨時表比較特殊，PG的autovacuum進程並不會回收臨時表的垃圾，也不會收集臨時表的統計信息。

正常情況下，臨時表用完就會隨著連接的斷開而被銷毀掉。而這3張表應該是3個不同的連接創建的，且已經保留了4天，那麼有可能是應用使用的連接池，或者連接泄漏了。在資料庫確認了下，確實有一些比較古老的連接存在。

這些臨時表應該是其中某幾個連接創建的，與項目組確認之後，他們沒有使用連接池，並且這些臨時表已經沒人使用了，於是決定drop掉這些臨時表。從監控看到，臨時表drop掉之後，資料庫年齡就恢復正常了。

5

根本原因

我們分析，有下面2個可能的原因：

1

應用SQL中使用完臨時表沒有及時清理

2

應用程序存在連接泄露的bug

6

解決方案

1

創建臨時表時加上on commit drop，即臨時表在事務提交後就會被drop掉（注）

2

應用程序需要檢查有無連接泄漏的問題

註：PG臨時表的生命周期默認情況下，是會話級的，即連接斷開時才會清理表定義。

臨時表在一個事務塊結束時的行為有三種選項：

●PRESERVE ROWS在事務結束時不採取特殊的動作。這是默認行為。

●DELETE ROWS在每一個事務塊結束時將刪除臨時表中的所有行。實質上，在每一次提交時會完成一次自動的TRUNCATE。

●PRESERVE ROWS在事務結束時不採取特殊的動作。這是默認行為。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！