1. 背景很多初學(xué)者會對WAL日志占用多少空間比較疑惑聽網(wǎng)上的一些文章說是由max_wal_size來控制的但發(fā)現(xiàn)很多時候WAL日志空間會超過這個設(shè)置的值不知道為什么? 同時有時會發(fā)現(xiàn)WAL日志不清理了占用空間在不停的增長然后不知道為什么看一些網(wǎng)上的文章發(fā)現(xiàn)情況不是網(wǎng)上說的那種情況。中啟乘數(shù)科技工程師在服務(wù)客戶的工程師遇到了導(dǎo)致WAL日志空間膨脹不清理的各種用分期并進行了深入全面的分析基本囊括了所有的導(dǎo)致WAL日志膨脹的各種原因。所以對于初學(xué)者來說不需要再看網(wǎng)上那些不全面的文章了只看這篇文章就夠了。2. 決定WAL日志占用空間大小因素控制WAL日志的數(shù)量由以下這三個參數(shù)控制max_wal_sizemin_wal_sizewal_keep_segments或wal_keep_size注意PostgreSQL13版本后wal_keep_segments參數(shù)以及廢棄了由wal_keep_size替代此參數(shù)很多人認為WAL占用的空間是由max_wal_size來控制的這種認識是不全面的下面我們詳細講解這幾個參數(shù)的意思。假設(shè)pg_wal下的文件為000000A7000000040000005A 000000A7000000040000005B 000000A7000000040000005C 000000A7000000040000005D 000000A7000000040000005E 000000A7000000040000005F 000000A70000000400000060 000000A70000000400000061 000000A70000000400000062 000000A70000000400000063 000000A70000000400000064假設(shè)當前正在寫的WAL文件為000000A70000000400000060則wal_keep_segments控制000000A7000000040000005A到000000A70000000400000060的個數(shù)而min_wal_size控制000000A70000000400000060到000000A70000000400000064即這一段至少要保留min_wal_size的WAL日志。如果min_wal_size wal_keep_segments 大于了max_wal_size那么WAL日志空間至少也會占用min_wal_size wal_keep_segments。所以從這里可以看出WAL占用的空間大小并不是完全由max_wal_size控制的只有在min_wal_size wal_keep_segments的值小于max_wal_size時PostgreSQL才盡量保值WAL的空間不超過這個值。注意這里說的是盡量原因是PostgreSQL是在做checkpoint時把不需要的WAL日志給清理掉但是如果數(shù)據(jù)庫由很大的寫導(dǎo)致還沒有來得及做checkpoint時這時WAL日志占用的空間會超過max_wal_size設(shè)置的值。如果min_wal_size wal_keep_segments小于max_wal_size那么WAL日志空間盡量保持不超過max_wal_size參數(shù)設(shè)置的值當然每次checkpoint清理時會保持WAL的日志空間不會低于min_wal_size wal_keep_segments的值。所以從這個原理來說min_wal_size不需要設(shè)置太大生產(chǎn)庫只需要為1G左右大小時就夠用了不需要太大。而為了防止備庫同步失敗應(yīng)該設(shè)置一個較大的wal_keep_segmentsWAL文件為16M大小可把wal_keep_segments設(shè)置為500或更大。max_wal_size比 min_wal_size wal_keep_segments略大一點就可以了。實際上參數(shù)max_wal_size主要時為了控制checkpoint發(fā)生的頻繁程度target (double) ConvertToXSegs(max_wal_size_mb) / (2.0 CheckPointCompletionTarget);如果checkpoint_completion_target設(shè)置為0.5時則每寫了 max_wal_size/2.5 的WAL日志時就會發(fā)送一次checkpoint。checkpoint_completion_target的范圍為0~1那么結(jié)果就是寫的WAL的日志量超過: max_wal_size的1/31/2時就會發(fā)生一次checkpoint。3. 導(dǎo)致WAL日志空間膨脹的原因3.1 長事務(wù)數(shù)據(jù)庫中如果有長事務(wù)PostgreSQL數(shù)據(jù)庫對于這個長事務(wù)開始后產(chǎn)生的所有WAL日志都不會清理。select pid,usename, xact_start from pg_stat_activity where now() - xact_start interval ‘8 hours’;下面時監(jiān)控超過8個小時的長事務(wù)的SQL:select pid,usename, xact_start from pg_stat_activity where now() - xact_start interval 8 hours;更甚的情況是用戶有“Idle in transaction”的連接即一個連接開啟了事務(wù)然后什么事情也不干一直空閑著用下面的SQL查詢“Idle in transaction”的連接select pid,client_addr,usename,datname, xact_start,state from pg_stat_activity where state not in (active,idle) order by xact_start;如果有長時間的“idle in transaction”的連接需要kill掉kill的方法是select pg_terminate_backend(3415)其中3415是這個連接的pid。當然kill掉之前需要調(diào)查這中長時間的“idle in transaction”的連接是如何產(chǎn)生的。對于一些應(yīng)用產(chǎn)生的“idle in transaction”隨便kill掉可能會導(dǎo)致應(yīng)用出現(xiàn)問題需要注意。3.2 廢棄的復(fù)制槽(replication slots)復(fù)制槽是用來保證邏輯復(fù)制或物理復(fù)制需要的WAL日志不會被清理掉。如果使用了邏輯復(fù)制或物理復(fù)制使用的復(fù)制槽而這些邏輯復(fù)制或物理因為某些原因停掉了那么會導(dǎo)致這些復(fù)制槽會把WAL的日志保留著。如果是邏輯復(fù)制或物理復(fù)制停掉了則需要盡快把這些邏輯復(fù)制或物理復(fù)制啟動起來否則很容易把主庫的空間撐滿。用下面的SQL查詢復(fù)制槽SELECT slot_name, slot_type, database, xmin,active,active_pid FROM pg_replication_slots ORDER BY age(xmin) DESC;如果上面結(jié)果某一行中active為空說明復(fù)制停掉了需要檢查。如果邏輯復(fù)制或物理復(fù)制停掉了但一時半會還啟動不起來而主庫的空間又要慢了這時可以強制把復(fù)制槽給刪除掉注意刪除掉邏輯復(fù)制的復(fù)制槽后邏輯復(fù)制的同步就廢棄了,后續(xù)的恢復(fù)需要做全量的數(shù)據(jù)恢復(fù)。所以這是邏輯復(fù)制的一個大缺點。邏輯復(fù)制還有一個大缺點是主備庫切換后邏輯復(fù)制槽也廢掉了。如果想避免這個問題可以使用中啟乘數(shù)科技的產(chǎn)品CMiner具體請見CMiner介紹頁面。3.3 廢棄的未提交兩階段事務(wù)(prepared transactions)未提交的兩階段事務(wù)(prepared transactions)會讓數(shù)據(jù)庫保留從這個事務(wù)開始時WAL日志導(dǎo)致WAL日志空間膨脹。如果應(yīng)用使用了兩階段事務(wù)理論上兩階段事務(wù)的提交和回滾時需要由這個應(yīng)用來提交或回滾的而如果這個應(yīng)用出現(xiàn)的問題一直沒有對其創(chuàng)建的兩階段事務(wù)進行提交或回滾則會產(chǎn)生此問題。查詢兩階段事務(wù)的語句SELECT gid, prepared, owner, database, transaction AS xmin FROM pg_prepared_xacts ORDER BY age(transaction) DESC;如果發(fā)現(xiàn)某個兩階段事務(wù)長期存在如數(shù)個小時則可能出現(xiàn)了這個問題如下所示postgres# SELECT gid, prepared, owner, database, transaction AS xmin FROM pg_prepared_xacts ORDER BY age(transaction) DESC; gid | prepared | owner | database | xmin ---------------------------------------------------------------------- osdba_pxid | 2019-01-10 10:27:15.44151308 | codetest | postgres | 13843 (1 row)如果發(fā)現(xiàn)prepared列的時間是一個之前很久的時間基本可以斷定這是一個廢棄的兩階端事務(wù)。這時我們可以手工提交或回滾這個事務(wù)提交的方法commit prepared osdba_pxid;回滾的方法roback prepared osdba_pxid;注意需要調(diào)查兩階端事務(wù)產(chǎn)生的原因以及確定應(yīng)該是提交還是回滾否則可能造出數(shù)據(jù)的丟失。3.4 主庫的WAL日志的歸檔未成功主庫不會清理未歸檔的WAL日志從而導(dǎo)致了主庫的WAL日志膨脹。主庫開啟了歸檔但是歸檔命令一直沒有執(zhí)行成功或歸檔命令hang住也可能是歸檔命令執(zhí)行的太慢來不及歸檔。檢查主庫的日志看看釋放又歸檔失敗的日志。也可以到pg_wal/archive_status目錄下看看是否大量的WAL日志未歸檔成功。3.5 備庫開啟的HOT_STANDBY_FEEDBACK如果只讀備庫開啟了HOT_STANDBY_FEEDBACK備庫上如果有個長時間運行的查詢正在執(zhí)行備庫會通知主庫這個備庫上長時間查詢開始啟動后的WAL日志都不能被清理掉從而導(dǎo)致主庫的WAL日志膨脹。這種情況導(dǎo)致主庫WAL日志膨脹出現(xiàn)的概率很低。有人問為什么要有HOT_STANDBY_FEEDBACK這種機制呢原因是如果沒有這種機制主庫執(zhí)行UPDATE并VACUUM了由于主庫上已經(jīng)不存在使用被更新元組的事務(wù)VACUUM 會將這些元組清理掉當 備庫回放到 VACUUM 對應(yīng)的日志時檢測到當前 VACUUM 清理的元組仍然被這個長時間的查詢使用則會阻塞備庫的WAL日志應(yīng)用導(dǎo)致備庫有很大的延遲。為了避免備庫的延遲PostgreSQL又提供了參數(shù)max_standby_streaming_delay(默認30s)讓應(yīng)用WAL的進程在等待此參數(shù)指定的時間后后若長時間SQL還沒有執(zhí)行完則直接取消長時間SQL的運行并在日志種打印如下異常信息FATAL: terminating connection due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. HINT: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.那么這樣就導(dǎo)致了備庫上無法運行長時間的SQL。為了解決此問題備庫把參數(shù)HOT_STANDBY_FEEDBACK設(shè)置為on后就將 備庫種長時間運行的SQL的最小活躍事務(wù)ID定期告知主庫使得主庫在執(zhí)行 VACUUM時對這些事務(wù)還需要的數(shù)據(jù)手下留情不進行清理。