如何分析POSTGRESQL FULL PAGE優(yōu)化與CHECKPOINT的矛盾

如何分析POSTGRESQL  FULL PAGE優(yōu)化與CHECKPOINT的矛盾，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

在說完mysql 不要關(guān)DW 后，祭出 POSTGRESQL FULL PAGE 的確是有點不厚道，所以必然會引出 FULL PAGE 也存在性能問題的話題。到底是大公雞和大馬猴的問題，還是小綿羊的牧羊犬的故事。

首先FULL PAGE 和 MYSQL的 DW 一樣，都是為了解決數(shù)據(jù)庫CRASH 時數(shù)據(jù)的那一刻存在的缺失問題。我看來看看相關(guān)的解釋

PostgreSQL 在 checkpoint 之后在對數(shù)據(jù)頁面的第一次寫的時候會將整個數(shù)據(jù)頁面寫到 xlog 里面。當出現(xiàn)主機斷電或者OS崩潰時，redo操作時通過checksum發(fā)現(xiàn)“部分寫”的數(shù)據(jù)頁，并將xlog中保存的這個完整數(shù)據(jù)頁覆蓋當前損壞的數(shù)據(jù)頁，然后再繼續(xù)redo就可以恢復(fù)整個數(shù)據(jù)庫了。

看完以后會突然冒出一個想法

1 checkpoint 越密集，那FULL PAGE 也就越多咯

2 FULL PAGE 第一個頁面的大小會影響寫性能

那我關(guān)掉FULL PAGE 不就好了

當然不是，F(xiàn)ULL  PAGE 在某些時候，不是你要開就開，要關(guān)就關(guān)的，尤其你在操作  pg_basebackup（或者一連串利用這命令的偽裝者們）的操作時 FULL PAGE 是強制打開的，到底為什么這就不解釋了，和 備份的原理有關(guān)。

其實這個想法是比較巧妙的，他僅僅針對CHECK POINT 的第一個頁面進行檢測并寫入到 XLOG 中

而提出異議或否定的意見是，full_page_write需要在xlog（wal）中記錄數(shù)據(jù)頁，會進行更多寫操作，不僅數(shù)據(jù)變，還有數(shù)據(jù)頁的信息，這會增加的IO和磁盤消耗，引起主備延遲變大。

另外具體相關(guān)的FULL PAGE 的解釋和講解，參見下邊的圖，如有需要（可以入群，免費下載相關(guān)書籍）

于此同時，很多的優(yōu)化的方法就來了 ，有一種就很像 MYSQL的 DW 的方式來優(yōu)化PG 的FULL PAGE 方式， 但需要在PG上打PATCH。（這里不詳細展開）

另外降低CHECKPOINT 的頻率也可以降低 FULL PAGE 所帶來的性能影響，也有提出將PG的數(shù)據(jù)頁面在編譯的時候變?。▊€人覺得有點過了）, 當然這還不是終極的優(yōu)化方法，還有的文章中提出了，使用序列，與 UUID 對FULL PAGE 的不同的性能影響，其中指出，如果使用序列的方式，則在插入同樣行數(shù)的數(shù)據(jù)時，對比 UUID ，產(chǎn)生的WAL 的大小相差20倍。

其中關(guān)鍵的一段話在上，提出如果使用序列的方式作為主鍵，則插入到btree索引中的相同葉頁面，只有對頁面的第一次修改才會觸發(fā)整個頁面的寫入。UUID是完全不同的情況，UUID值完全不是連續(xù)的，實際上每次插入都可能接觸到全新的葉索引葉頁面，造成寫入日志的量大影響I/O的性能。

上文中也提出可以調(diào)整 CHECK POINT 的間距，來降低FULL PAGE 造成的影響，具體怎么調(diào)整CHECK POINT 的間距。

上面是我們在每個PG 中都能看到的與CHECKPOINT 有關(guān)的東西，在一個負載很重的系統(tǒng)中，我們是否可以將MAX_WAL_SIZE 調(diào)大，（有人可能會問這個數(shù)字初期是怎么來的，那我覺得你可能對PG的安裝方面還可以在了解一下），調(diào)整到多大，8 - 10G 是沒有問題的。增加檢查點之間的距離可以減少WAL -但是為什么會發(fā)生這種情況呢?請記住:將事務(wù)日志放在首位的目的是確保系統(tǒng)在崩潰后仍然能夠正常運行。將WAL中的這些更改應(yīng)用到數(shù)據(jù)文件將修復(fù)數(shù)據(jù)文件并在啟動時恢復(fù)系統(tǒng)。為了安全起見，PostgreSQL不能簡單地記錄對一個塊所做的更改—如果一個塊在通過檢查點后第一次被更改，那么整個頁面都必須被發(fā)送到WAL。

兩個檢查點之間的距離不僅由于檢查點的減少而提高了速度，更少的檢查點還會影響所寫事務(wù)日志的數(shù)量。所以在優(yōu)化FULL PAGE 的時候是要考慮，降低檢查點，其實這和 MYSQL 里面調(diào)整innodb log file size 是一個意思。

在 Postgresql 11 Administratoration Cookbook, 這本書中的427頁也中對這兩個參數(shù)也有相關(guān)的講解，

兩個參數(shù)控制CHECKPOINT發(fā)生的頻率與預(yù)期，首先checkpoint_timout 這個參數(shù)會觸發(fā)在上一次CHECKPOINT 發(fā)生后多少秒后，進行HECKPOINT，這直接影響有多少量的 WAL data 將要寫入目標文件，實際上控制WAL寫入的數(shù)量的參數(shù)有兩個地方 

MAX_WAL_SIZE 

CHECKPOINT_TIMEOUT

然而當你調(diào)大參數(shù)，你應(yīng)該考慮系統(tǒng)如果CRASH 后的恢復(fù)時間，與多長時間做一次CHECKPOINT 之間做一個衡量，并且也要考慮WAL 日志的空間在磁盤上是否有充足的空間。

同時除2個參數(shù)可以優(yōu)化FULL PAGE 的性能的同時，是否還有其他的方法，我們知道 WAL 日志的也是有緩沖，默認是同步提交，只要是有COMMIT 日志信息就會刷入到磁盤， wal_buffers可以調(diào)整wal_buffer 的大小，并且調(diào)整 synchronous_commit  不在同步提交，減少磁盤交互，但這會打破系統(tǒng)CRASH 后的數(shù)據(jù)庫安全，有數(shù)據(jù)庫丟失的風險。

那怎么有的放矢的更大利用wal_buffers ，可以根據(jù)業(yè)務(wù)，在某些情況下，通過調(diào)整下面的參數(shù)在session 的級別，來對某些可以容忍 1秒內(nèi)數(shù)據(jù)庫丟失的情況中，將syschronous_commit = off.

看完上述內(nèi)容是否對您有幫助呢？如果還想對相關(guān)知識有進一步的了解或閱讀更多相關(guān)文章，請關(guān)注億速云行業(yè)資訊頻道，感謝您對億速云的支持。