詳解PostgreSQL中的checkpoint概念

richardtc

checkpoint又名檢查點，在Oracle中checkpoint的發(fā)生意味著之前的臟數(shù)據(jù)全部寫回磁盤，數(shù)據(jù)庫實現(xiàn)了一致性與數(shù)據(jù)完整性。Oracle在實現(xiàn)介質(zhì)恢復時將以最近的checkpoint為參照點執(zhí)行事務前滾。在PostgreSQL中checkpoint起著相同的作用：寫臟數(shù)據(jù)；完成數(shù)據(jù)庫的完整性檢查。

checkpoints相關參數(shù)：

checkpoint_segments:

WAL log的最大數(shù)量，系統(tǒng)默認值是3。該值越大，在執(zhí)行介質(zhì)恢復時處理的數(shù)據(jù)量也越大，時間相對越長。

checkpoint_timeout:

系統(tǒng)自動執(zhí)行checkpoint之間的最大時間間隔，同樣間隔越大介質(zhì)恢復的時間越長。系統(tǒng)默認值是5分鐘。

checkpoint_completion_target:

該參數(shù)表示checkpoint的完成目標，系統(tǒng)默認值是0.5,也就是說每個checkpoint需要在checkpoints間隔時間的50%內(nèi)完成。

checkpoint_warning:

系統(tǒng)默認值是30秒，如果checkpoints的實際發(fā)生間隔小于該參數(shù)，將會在server log中寫入寫入一條相關信息。可以通過設置為0禁用信息寫入。

checkpoint執(zhí)行控制:

1,數(shù)據(jù)量達到checkpoint_segments*16M時，系統(tǒng)自動觸發(fā)；

2,時間間隔達到checkpoint_timeout參數(shù)值時；

3,用戶發(fā)出checkpoint命令時。

checkpoints參數(shù)調(diào)整：

正確合適的參數(shù)值總能夠給系統(tǒng)帶來益處，checkpoints參數(shù)合理的配置不僅能夠減少系統(tǒng)IO寫入的阻塞，同時還會減少高峰時IO給系統(tǒng)帶來的壓力。首先可以通過觀察checkpoint_warning參數(shù)寫入的日志，來估算系統(tǒng)寫入的數(shù)據(jù)量：一般情況下checkpoint_warning參數(shù)值小于checkpoint_timeout；

估算公式：checkpoint_segments*16M*(60s/m)/checkpoint_warning=大致每分鐘數(shù)據(jù)量,得到每分鐘寫入的數(shù)據(jù)量(這里全部是估算，建立在warning參數(shù)的合理設置上)。

合理配置情況：checkpoint_segments*16M*checkpoint_timeout(m)略大于上述值.

以上述公式為依據(jù)，配置checkpoint_segments與checkpoint_timeout，兩個參數(shù)應該盡量平衡為一個足夠大和足夠小的值。在數(shù)據(jù)量異常高的情況下應該考慮，磁盤帶寬與checkpoint時數(shù)據(jù)量的關系。

個人觀點：

假如以checkpoint_segments參數(shù)為閥值時，可以計算高峰時需要的帶寬(秒)：checkpoint_segments*16M/(checkpoint_warning*checkpoint_completion_target)。

假如以checkpoint_timeout參數(shù)為閥值時，checkpoint_warning值最好大于等于checkpoint_timeout，通過監(jiān)控系統(tǒng)數(shù)據(jù)字典統(tǒng)計寫入的數(shù)據(jù)量Total。
Total/(checkpoint_timeout*checkpoint_completion_target)得到IO的帶寬要求。

wang1352083

checkpoint_completion_target 這個參數(shù)的準確定義是什么呢?

osdba

回復 2# wang1352083
In 8.3, the checkpoint writes can be spread out over a much longer time period. A new parameter called checkpoint_completion_target suggests how far along the system should aim to have finished the current checkpoint relative to when the next one is expected.
If your checkpoints are being driven by the timeout, it's fairly easy to predict how this will work. Let's say you have checkpoint_timeout=300 seconds. With checkpoint_completion_target=0.5 (the default), checkpoints should be finished writing out all their data by 150 seconds after one starts. If you set it to 0.9 instead, the practical upper limit, the checkpoints will aim to be finished 270 seconds later, leaving only 30 seconds for the operating system to finish writing everything out before the next checkpoint should start.

The parameter works similarly if your system tends to hit the segments limit before the timeout. For example, let's say your current system has checkpoint_segments=10. Assume that you have checkpoint_timeout set to a large number such that the checkpoints are typically being driven by the number of segments being filled (so you get a checkpoint every 10 WAL segments, period). If checkpoint_completion_target was set to 0.5, the expectation is that the writes for the currently executing checkpoint would be finished about the time that 0.5*10=5 segments of new WAL data had been written. If you set it to 0.9 instead, you'd expect the checkpoint is finishing just about when the 9th WAL segment is being written out, which again is cutting things a bit tight.