我發(fā)現(xiàn)自己在與許多 PostgreSQL 用戶，尤其是新手用戶討論和解釋sychronous_commit。所以，我想把所有的關(guān)鍵點(diǎn)記下來作為一篇博文，這對(duì)更多的用戶會(huì)有用。最近我有機(jī)會(huì)在我們的 PostgreSQL Percona Tech Days 中談?wù)撘恍┫嚓P(guān)主題。
sychronous_commit 是什么？
這是我們可以決定何時(shí)可以向客戶端確認(rèn)事務(wù)提交成功的參數(shù)。
因此，此參數(shù)不僅與同步備用有關(guān)，而且具有更廣泛的含義和含義，這對(duì)于獨(dú)立的 PostgreSQL 實(shí)例也很有用。為了更好地理解，我們應(yīng)該查看整個(gè) WAL 記錄傳播以及可以接受提交確認(rèn)的各個(gè)階段。這使我們可以為每個(gè)事務(wù)選擇不同級(jí)別的持久性。持久性選擇越少，確認(rèn)越快，從而提高系統(tǒng)的整體吞吐量和性能。
WAL 傳播
PostgreSQL WAL（Write Ahead Log）是主端更改/活動(dòng)的記錄，可以被視為數(shù)據(jù)庫中發(fā)生的更改的日志/分類帳。下圖展示了本地主PostgreSQL實(shí)例和遠(yuǎn)程熱備實(shí)例中WAL傳播的流程。
PostgreSQL 使用內(nèi)部函數(shù) pg_pwrite() 寫入 WAL 段，該函數(shù)內(nèi)部使用write()系統(tǒng)調(diào)用，該系統(tǒng)調(diào)用不保證將數(shù)據(jù)刷新到磁盤。為了完成刷新，另一個(gè)函數(shù) issue_xlog_fsync() 被調(diào)用，它根據(jù)參數(shù) (GUC) 發(fā)出適當(dāng)類型的 fsync：wal_sync_method
上圖顯示了所有 5 個(gè)主要階段。
- WAL 記錄插入（本地）：WAL 記錄首先在 WAL 緩沖區(qū)中創(chuàng)建。由于多個(gè)后端進(jìn)程將同時(shí)創(chuàng)建 WAL 記錄，因此它受到鎖的適當(dāng)保護(hù)。wal_buffers 中 WAL 記錄的寫入被不同的后臺(tái)進(jìn)程連續(xù)寫入 WAL 段。如果 sychronous_commit 完全關(guān)閉，刷新將不會(huì)立即發(fā)生，而是依賴于 wal_writer_delay 設(shè)置，我們將在下一節(jié)中討論。
- WAL 寫入和 WAL 刷新（本地）：這種刷新到本地磁盤上的 WAL“段文件”被認(rèn)為是繁重的操作之一。PostgreSQL 在這方面做了很多優(yōu)化，以避免頻繁的flush。
- 遠(yuǎn)程寫入： WAL 記錄寫入遠(yuǎn)程備用數(shù)據(jù)庫（但尚未刷新）。數(shù)據(jù)可能會(huì)在頁面緩存中保留一段時(shí)間。除非我們想解決 Primary 和 Standby 實(shí)例同時(shí)崩潰的情況，否則可以考慮這種級(jí)別的持久性保護(hù)。
- Remote Flush：在這個(gè)階段，數(shù)據(jù)真正在遠(yuǎn)程備端寫入并刷新到磁盤。所以我們可以保證數(shù)據(jù)在備用端可用，即使它也會(huì)崩潰。
- 遠(yuǎn)程應(yīng)用：在此階段，WAL 記錄在遠(yuǎn)程/備用端重放，可供在那里運(yùn)行的會(huì)話使用。
synchronous_commit 對(duì)應(yīng)的接受值如下：
- off： 您可以使用值 off、0（零）、false 或 no 來關(guān)閉 synchronous_commit。顧名思義，提交確認(rèn)可以在將記錄刷新到磁盤之前出現(xiàn)。這通常稱為異步提交。如果 PostgreSQL 實(shí)例崩潰，最后幾次異步提交可能會(huì)丟失。
- 本地： WAL 記錄被寫入并刷新到本地磁盤。在這種情況下，將在本地 WAL 寫入和 WAL 刷新完成后確認(rèn)提交。
- remote_write： WAL 記錄成功地移交給遠(yuǎn)程實(shí)例，該實(shí)例確認(rèn)了寫入（不刷新）。
- on：這是默認(rèn)值，您可以使用 on、true、yes 或 1 將值設(shè)置為“on”。但是含義可能會(huì)根據(jù)您是否有同步待機(jī)而改變。如果有同步待機(jī)，將值設(shè)置為 on 將導(dǎo)致等待直到“遠(yuǎn)程刷新”。
- remote_apply： 這將導(dǎo)致提交等待，直到來自當(dāng)前同步備用數(shù)據(jù)庫的回復(fù)表明他們已收到事務(wù)的提交記錄并應(yīng)用它，以便它對(duì)備用數(shù)據(jù)庫上的查詢可見。
通過為參數(shù)選擇適當(dāng)?shù)闹?，我們可以選擇確認(rèn)何時(shí)返回。如果沒有同步備用（ synchronous_standby_names 為空）， synchronous_commit 到 on、remote_apply、remote_write 和 local 的設(shè)置都提供相同的同步級(jí)別：事務(wù)提交只等待本地刷新到磁盤。
該領(lǐng)域的常見問題之一是：
“如果我們選擇完全異步提交（synchronous_commit = off），我們會(huì)丟失多少數(shù)據(jù)？”
答案有點(diǎn)復(fù)雜，它取決于 wal_writer_delay 設(shè)置。默認(rèn)為 200 毫秒。這意味著WAL 將在每個(gè)wal_writer_delay 中 刷新到磁盤。WAL 編寫器會(huì)定期喚醒并調(diào)用 XLogBackgroundFlush()。這會(huì)檢查完全填充的 WAL 頁面。如果它們可用，它會(huì)寫入到該點(diǎn)的所有緩沖區(qū)。所以在良好的負(fù)載條件下，WAL 寫入器寫入整個(gè)緩沖區(qū)。在找不到完整頁面的低負(fù)載情況下，將刷新上次異步提交之前的所有內(nèi)容。
如果超過 wal_writer_delay 已經(jīng)過去，或者自上次刷新以來已經(jīng)寫入了超過 wal_writer_flush_after 塊，則 WAL 被刷新到當(dāng)前位置。這種安排保證異步提交記錄在事務(wù)完成后最多兩次 wal_writer_delay 后到達(dá)磁盤。但是，PostgreSQL 以靈活的方式寫入/刷新完整緩沖區(qū)，這是為了減少每個(gè) WAL 寫入器周期填充多個(gè) WAL 頁時(shí)在高負(fù)載下發(fā)出的寫入次數(shù)。從概念上講，這會(huì)使最壞情況的延遲達(dá)到三個(gè)wal_writer_delay 周期。
所以答案很簡單，就是：
在大多數(shù)情況下，損失將小于 wal_writer_delay 的兩倍。但在最壞的情況下，它可能高達(dá)三倍。
設(shè)置范圍
當(dāng)我們討論參數(shù)及其值時(shí)，許多用戶會(huì)考慮在實(shí)例級(jí)別進(jìn)行全局設(shè)置。但是，真正的力量和用法是在不同級(jí)別適當(dāng)?shù)卮_定范圍時(shí)才出現(xiàn)的。在實(shí)例級(jí)別更改它是不可取的。
除了各種值之外，PostgreSQL 還允許我們?cè)诓煌秶鷥?nèi)進(jìn)行此設(shè)置。
在每個(gè)事務(wù)級(jí)別在完美調(diào)整的應(yīng)用程序設(shè)計(jì)中，特定事務(wù)可以為每個(gè)事務(wù)選擇特定的 sychronous_commit 級(jí)別，例如：
SQL：
SET LOCAL synchronous_commit = 'remote_write';請(qǐng)注意“本地”規(guī)范。一旦事務(wù)塊完成（提交或回滾），設(shè)置將恢復(fù)到適用于會(huì)話級(jí)別的設(shè)置。這允許架構(gòu)師選擇加入特定關(guān)鍵事務(wù)的額外開銷。
在會(huì)話級(jí)別可以在每個(gè)會(huì)話級(jí)別指定設(shè)置，以便它適用于整個(gè)會(huì)話，除非被上述事務(wù)級(jí)別設(shè)置覆蓋。
SQL：
SET synchronous_commit = 'remote_write';此外，您可能會(huì)選擇將此作為應(yīng)用程序連接中的連接字符串選項(xiàng)的一部分傳遞給 PostgreSQL。例如： "host=hostname user=postgres ... options='-c synchronous_commit=off'" 。因此可以減少任何代碼修改的要求。
在用戶級(jí)別在一個(gè)理想的系統(tǒng)中，擁有良好管理的用戶帳戶，每個(gè)用戶帳戶都將處理特定的功能。它的范圍可以從關(guān)鍵交易系統(tǒng)到報(bào)告用戶帳戶。例如：
SQL：
ALTER USER trans_user SET synchronous_commit= ON;
ALTER USER report_user SET synchronous_commit=OFF;默認(rèn)情況下，這些用戶創(chuàng)建的會(huì)話將具有這些設(shè)置?？梢栽跁?huì)話級(jí)別或事務(wù)級(jí)別覆蓋此用戶級(jí)別設(shè)置。
在數(shù)據(jù)庫級(jí)別當(dāng)我們有專門的系統(tǒng)用于報(bào)告或臨時(shí)登臺(tái)信息時(shí)，在數(shù)據(jù)庫級(jí)別指定非常有用。
SQL：
ALTER DATABASE reporting SET synchronous_commit=OFF;在實(shí)例級(jí)別這是在 PostgreSQL 實(shí)例級(jí)別，如下所示：
SQL：
ALTER SYSTEM SET synchronous_commit=OFF;
SHOW synchronous_commit;常見用例
遷移：當(dāng)發(fā)生遷移時(shí)，跨系統(tǒng)移動(dòng)大量數(shù)據(jù)是很常見的，正確選擇 synchronous_commit 甚至關(guān)閉它對(duì)于減少整體遷移時(shí)間具有重要價(jià)值。
數(shù)據(jù)加載：在數(shù)據(jù)倉庫系統(tǒng)/報(bào)告系統(tǒng)中，可能會(huì)發(fā)生大量數(shù)據(jù)加載，關(guān)閉 synchronous_commit 將通過減少重復(fù)刷新的開銷來大幅提升。
審計(jì)和日志記錄：即使在具有關(guān)鍵事務(wù)的關(guān)鍵系統(tǒng)中，在確認(rèn)提交之前，在備用端上也只有事務(wù)的特定部分可能是非常關(guān)鍵的——這是企業(yè)希望可用的。但是會(huì)有關(guān)聯(lián)的日志和審計(jì)信息記錄。非常有選擇性地選擇同步備用提交可以產(chǎn)生非常高的好處。
最后一點(diǎn)
使用 pgbench 的快速測(cè)試可以幫助驗(yàn)證特定環(huán)境中不同級(jí)別的提交同步的開銷?？傮w而言，隨著同步要求級(jí)別的提高，我們應(yīng)該預(yù)期性能會(huì)下降。fsync 到本地磁盤的延遲、網(wǎng)絡(luò)延遲、備用服務(wù)器上的負(fù)載、備用服務(wù)器上的爭(zhēng)用、整體復(fù)制量、備用服務(wù)器上的磁盤性能等環(huán)境因素都會(huì)影響開銷和性能。
與 fsync 不同，即使完全關(guān)閉 synchronous_commit 也不會(huì)導(dǎo)致數(shù)據(jù)庫損壞。了解整體 WAL 傳播可以幫助您從pg_stat_replication視圖了解復(fù)制延遲和信息。
系統(tǒng)的性能就是在不犧牲真正重要的東西的情況下消除不需要的、可避免的開銷。我見過一些 PostgreSQL 的超級(jí)用戶，他們通過非常有效和有選擇地使用 synchronous_commit 功能，為PostgreSQL 數(shù)據(jù)庫提供了一個(gè)經(jīng)過良好調(diào)優(yōu)的系統(tǒng)。我希望這篇文章能幫助那些仍然沒有使用它并正在尋找更高性能或耐用性的微調(diào)機(jī)會(huì)的人
