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