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