前言

PostgreSQL在寫入頻繁的場景中，可能會產生大量的WAL日誌，而且WAL日誌量遠遠超過實際更新的資料量。 我們可以把這種現象起個名字,叫做“WAL寫放大”，造成WAL寫放大的主要原因有2點。

1. 在checkpoint之後第一次修改頁面，需要在WAL中輸出整個page，即全頁寫(full page writes)。全頁寫的目的是防止在意外當機時出現的資料塊部分寫導致資料庫無法恢復。
2. 更新記錄時如果新記錄位置(ctid)發生變更，索引記錄也要相應變更，這個變更也要記入WAL。更嚴重的是索引記錄的變更又有可能導致索引頁的全頁寫，進一步加劇了WAL寫放大。

過量的WAL輸出會對系統資源造成很大的消耗，因此需要進行適當的優化。

1. 磁碟IO
   WAL寫入是順序寫,通常情況下硬碟對付WAL的順序寫入是綽綽有餘的。所以一般可以忽略。
2. 網路IO
   對區域網內的複製估計還不算問題，遠端複製就難說了。
3. 磁碟空間
   如果做WAL歸檔，需要的磁碟空間也是巨大的。

WAL記錄的構成

每條WAL記錄的構成大致如下：

src/include/access/xlogrecord.h:

主要佔空間是上面的”block data”，再往上的XLogRecordBlockHeader是”block data”的後設資料。 一條WAL記錄可能不涉及資料塊，也可能涉及多個資料塊，因此WAL記錄中可能沒有”block data”也可能有多個”block data”。

“block data”的內容可能是下面幾種情況之一

• full page image
  如果是checkpoint之後第一次修改頁面，則輸出整個page的內容(即full page image，簡稱FPI)。但是page中沒有資料的hole部分會被排除，如果設定了wal_compression = on還會對這page上的資料進行壓縮。
• buffer data
  不需要輸出FPI時，就只輸出page中指定的資料。
• full page image + buffer data
  邏輯複製時，即使輸出了FPI，也要輸出指定的資料。

究竟”block data”中存的是什麼內容，通過前面的XLogRecordBlockHeader中的fork_flags進行描述。這裡的XLogRecordBlockHeader其實也只是個概括的說法，實際上後面還跟了一些其它的Header。完整的結構如下:

下面以insert作為例子說明。

src/backend/access/heap/heapam.c:

WAL的解析

PostgreSQL的安裝目錄下有個叫做pg_xlogdump的命令可以解析WAL檔案，下面看一個例子。

這條WAL記錄的解釋如下:

• rmgr: Heap
  PostgreSQL內部將WAL日誌歸類到20多種不同的資源管理器。這條WAL記錄所屬資源管理器為Heap,即堆表。除了Heap還有Btree,Transaction等。
• len (rec/tot): 14/ 171
  WAL記錄的總長度是171位元組，其中main data部分是14位元組(只計數main data可能並不合理，本文的後面會有說明)。
• tx: 301170263
  事務號
• lsn: 555/D5005080
  本WAL記錄的LSN
• prev 555/D50030A0
  上條WAL記錄的LSN
• desc: UPDATE off 30 xmax 301170263 ; new off 20 xmax 0
  這是一條UPDATE型別的記錄(每個資源管理器最多包含16種不同的WAL記錄型別，)，舊tuple在page中的位置為30(即ctid的後半部分)，新tuple在page中的位置為20。
• blkref #0: rel 1663/13269/54349226 fork main blk 1640350
  引用的第一個page(新tuple所在page)所屬的堆表檔案為1663/13269/54349226,塊號為1640350(即ctid的前半部分)。通過oid2name可以查到是哪個堆表。
  
  -bash-4.1$ oid2name -f 54349226 From database "postgres": Filenode Table Name ---------------------------- 54349226 pgbench_accounts

  1 2 3 4 5

  -bash-4.1$ oid2name -f 54349226   From database "postgres":     Filenode        Table Name   ----------------------------     54349226  pgbench_accounts

• blkref #1: rel 1663/13269/54349226 fork main blk 1174199
  引用的第二個page(舊tuple所在page)所屬的堆表檔案及塊號

UPDATE語句除了產生UPDATE型別的WAL記錄，實際上還會在前面產生一條LOCK記錄，可選的還可能在後面產生若干索引更新的WAL記錄。

上面的LOCK記錄的例子中，第一個引用page裡有PFW標識，表示包含FPI，這也是這條WAL記錄長度很大的原因。 後面的hole: offset: 268, length: 116表示page中包含hole,以及這個hole的偏移位置和長度。 可以算出FPI的大小為8196-116=8080, WAL記錄中除FPI以外的資料長度8135-8080=55。

WAL的統計

PostgreSQL 9.5以後的pg_xlogdump都帶有統計功能，可以檢視不同型別的WAL記錄的數量，大小以及FPI的比例。例子如下:

postgres.conf配置

下面是一個未經特別優化的配置

測試

先手動執行checkpoint，再利用pgbench做一個10秒鐘的壓測

日誌統計

統計壓測期間產生的WAL

這個統計結果顯示FPI的比例佔到了98.10%。但是這個資料並不準確，因為上面的Record size只包含了WAL記錄中”main data”的大小，Combined size則是”main data”與FPI的合計，漏掉了FPI以外的”block data”。 這是一個Bug，社群正在進行修復，參考BUG #14687

作為臨時對策，可以在pg_xlogdump.c中新增了一行程式碼，重新計算Record size使之等於WAL總記錄長度減去FPI的大小。為便於區分，修改後編譯的二進位制檔案改名為pg_xlogdump_ex。

src/bin/pg_xlogdump/pg_xlogdump.c：

修改後，重新統計WAL的結果如下:

這上面可以看出，有95.62%的WAL空間都被FPI佔據了（也就是說WAL至少被放大了20倍)，這個比例是相當高的。

如果不修改pg_xlogdump的程式碼,也可以通過計算WAL距離的方式，算出準確的FPI比例。

WAL的優化

在應用的寫負載不變的情況下，減少WAL生成量主要有下面幾種辦法。

1. 延長checkpoint時間間隔
   FPI產生於checkpoint之後第一次變髒的page，在下次checkpoint到來之前，已經輸出過PFI的page是不需要再次輸出FPI的。因此checkpoint時間間隔越長，FPI產生的頻度會越低。增大checkpoint_timeout和max_wal_size可以延長checkpoint時間間隔。
2. 增加HOT_UPDATE比例
   普通的UPDATE經常需要更新2個資料塊，並且可能還要更新索引page，這些又都有可能產生FPI。而HOT_UPDATE只修改1個資料塊，需要寫的WAL量也會相應減少。
3. 壓縮
   PostgreSQL9.5新增加了一個wal_compression引數，設為on可以對FPI進行壓縮，削減WAL的大小。另外還可以在外部通過SSL/SSH的壓縮功能減少主備間的通訊流量，以及自定義歸檔指令碼對歸檔的WAL進行壓縮。
4. 關閉全頁寫
   這是一個立竿見影但也很危險的辦法，如果底層的檔案系統或儲存支援原子寫可以考慮。因為很多部署環境都不具備安全的關閉全頁寫的條件，下文不對該方法做展開。

延長checkpoint時間

首先優化checkpoint相關引數

postgres.conf:

然後，手工發起一次checkpoint

再壓測10w個事務，並連續測試10次

測試結果如下

第1次執行

第5次執行

第10次執行

彙總如下:

No	tps	非FPI大小	WAL總量(位元組)	FPI比例(%)	每事務產生的WAL(位元組)
1	12896	64645794	1502006526	95.70	15020
5	12896	63348407	827996387	92.35	8279
10	12896	66042176	547726196	87.94	5477

不難看出非FPI大小是相對固定的，FPI的大小越來越小，這也證實了延長checkpoint間隔對削減WAL大小的作用。

增加HOT_UPDATE比例

HOT_UPDATE比例過低的一個很常見的原因是更新頻繁的表的fillfactor設定不恰當。fillfactor的預設值為100%，可以先將其調整為90%。

對於寬表，要進一步減小fillfactor使得至少可以保留一個tuple的空閒空間。可以查詢pg_class系統表估算平均tuple大小，並算出合理的fillfactor值。

再上面估算出的69%的基礎上，可以把fillfactor再稍微設小一點，比如設成65% 。

在前面優化過的引數的基礎上，先保持fillfactor=100不變，執行100w事務的壓測

生成的WAL統計如下：

設定fillfactor=90

再次測試

生成的WAL統計如下：

設定fillfactor=90後，生成的WAL量從8914890641減少到6413034966。

設定WAL壓縮

修改postgres.conf，開啟WAL壓縮

再次測試

生成的WAL統計如下：

設定`wal_compression = on後，生成的WAL量從6413034966減少到1063185497。

優化結果彙總

wal_compression	fillfactor	tps	非FPI大小	WAL總量(位元組)	FPI比例(%)	HOT_UPDATE比例(%)	每事務產生的WAL(位元組)
off	100	12592	659677445	8255213196	92.60	44	8255
off	90	13212	421342894	6413034966	93.43	81	6413
on	90	12059	426603035	1063185497	59.88	78	1063

僅僅調整wal_compression和fillfactor就削減了87%的WAL，這還沒有算上延長checkpoint間隔帶來的收益。

總結

PostgreSQL在未經優化的情況下，20倍甚至更高的WAL寫放大是很常見的，適當的優化之後應該可以減少到3倍以下。引入SSL/SSH壓縮或歸檔壓縮等外部手段還可以進一步減少WAL的生成量。

如何判斷是否需要優化WAL？

關於如何判斷是否需要優化WAL，可以通過分析WAL，然後檢查下面的條件，做一個粗略的判斷：

• FPI比例高於70%
• HOT_UPDATE比例低於70%

以上僅僅是粗略的經驗值，僅供參考。並且這個FPI比例可能不適用於低寫負載的系統，低寫負載的系統FPI比例一定非常高，但是，低寫負載系統由於寫操作少，因此FPI比例即使高一點也沒太大影響。

優化WAL的副作用

前面用到了3種優化手段，如果設定不當，也會產生副作用，具體如下：

1. 延長checkpoint時間間隔
   導致crash恢復時間變長。crash恢復時需要回放的WAL日誌量一般小於max_wal_size的一半，WAL回放速度(wal_compression=on時)一般是50MB/s~150MB/s之間。可以根據可容忍的最大crash恢復時間（有備機時，切備機可能比等待crash恢復更快），估算出允許的max_wal_size的最大值。
2. 調整fillfactor
   過小的設定會浪費儲存空間，這個不難理解。另外，對於頻繁更新的表，即使把fillfactor設成100%，每個page裡還是要有一部分空間被dead tuple佔據，不會比設定一個合適的稍小的fillfactor更節省空間。
3. 設定wal_compression=on
   需要額外佔用CPU資源進行壓縮，但根據實測的結果影響不大。

其他

去年Uber放出了一篇把PostgreSQL說得一無是處的文章為什麼Uber宣佈從PostgreSQL切換到MySQL?給PostgreSQL帶來了很大負面影響。Uber文章中提到了PG的幾個問題，每一個都被描述成無法逾越的“巨坑”。但實際上這些問題中，除了“寫放大”，其它幾個問題要麼是無關痛癢要麼是隨著PG的版本升級早就不存在了。至於“寫放大”，也是有解的。Uber的文章裡沒有提到他們在優化WAL寫入量上做過什麼有益的嘗試，並且他們使用的PostgreSQL 9.2也是不支援wal_compression的，因此推斷他們PG資料庫很可能一直執行在20倍以上WAL寫放大的完全未優化狀態下。

參考

• WAL Reduction
• 也許 MySQL 適合 Uber，但它不一定適合你
• 為PostgreSQL討說法 – 淺析《UBER ENGINEERING SWITCHED FROM POSTGRES TO MYSQL》