PgSQL · 答疑解惑 · 表膨脹 · 數據庫內核月報

## 背景最近處理了幾起線上實例表膨脹的問題。表膨脹是指表的數據和索引所占文件系統的空間，在有效數據量并未發生大的變化的情況下，不斷增大。PG使用過程中需要特別關注這方面，我們來給大家解析一下表膨脹的原因。表膨脹的直接觸發因素是表上的大量更新，如全表的update操作、大量的insert+delete操作等。而我們知道，PG在更新數據時，是不直接刪除老數據的。一個update操作執行后，被更改的數據的舊版本也被保留下來，直到對表做vacuum操作的時候，才考慮回收舊版本。做數據更新時，這些舊版本不及時回收就會造成表膨脹。線上實例都配置了autovacuum，有了autovacuum，PG會定期自動啟動autovacuum worker進程，執行vacuum回收舊版本，防止表膨脹。但在我們看到的幾起表膨脹問題里面，autovacuum幾乎每分鐘運行一次，仍然沒有避免表膨脹，這是為什么呢？ ## 表膨脹問題的重現從問題實例的`pg_stat_activity`視圖里面，可以發現它們有一個特點，就是有長時間未提交或終止的事務。我們用下面的例子簡單模擬一下。先創建一張表，插入一條數據。再建立兩個連接，其中一個開啟一個事務，執行插入和查詢，但不提交；另一個不斷執行update操作。控制臺A： ~~~ postgres=# begin; BEGIN postgres=# insert into test_bloat values(1); INSERT 0 1 postgres=# select * from test_bloat; a --- 1 1 (2 rows) postgres=# ~~~ 控制臺B： ~~~ postgres=# update test_bloat set a = 1; UPDATE 1 postgres=# \watch 1 Watch every 1s Wed Nov 11 17:04:31 2015 .... ~~~ 過不多久，查看表的大小就會發現它不斷增大： ~~~ postgres=# \dt+ test_bloat List of relations Schema | Name | Type | Owner | Size | Description --------+------------+-------+---------------+------------+------------- public | test_bloat | table | guangzhou.zgz | 8192 bytes | (1 row) .... postgres=# \dt+ test_bloat List of relations Schema | Name | Type | Owner | Size | Description --------+------------+-------+---------------+-------+------------- public | test_bloat | table | guangzhou.zgz | 40 kB | ~~~ 查看第一個連接中的事務狀態如下： ~~~ postgres=# select * from pg_stat_activity; -[ RECORD 1 ]----+-------------------------------- datid | 13003 datname | postgres pid | 113306 usesysid | 10 usename | guangzhou.zgz application_name | psql client_addr | client_hostname | client_port | -1 backend_start | 2015-11-11 16:45:38.452728+08 xact_start | 2015-11-11 16:47:00.998929+08 query_start | 2015-11-11 16:47:43.657035+08 state_change | 2015-11-11 16:47:43.658001+08 waiting | f state | idle in transaction backend_xid | 2431157 backend_xmin | query | select * from test_bloat; ~~~ 可以發現，它有兩個特點： 1. 它的狀態為“idle in transaction”，這是因為它未提交，又沒有正在進行的查詢； 2. 它的backend_xid不為空，即它有事務號，這是因為它執行了更新操作，插入了一條數據。注意，PG只在發生更新的時候才分配事務ID，沒有執行更新操作的事務（即只讀事務）是沒有backend_xid的。測試發現，如果及時將此種事務提交，并不會造成表膨脹。可見正是這些事務導致了舊版本無法回收！ ## 為什么舊版本沒有被回收我們從代碼里面看看，為什么長事務阻止了版本回收？先看看vacuum回收舊版本的代碼。vacuum操作由autovacuum worker執行，其調用順序如下： ~~~ vacuum->vacuum_rel->lazy_vacuum_rel->lazy_scan_heap->heap_page_prune -> heap_prune_chain ~~~ vacuum每次處理一個表；每個表同時只有一個進程進行vacuuum。其中，`lazy_scan_heap`掃描一個表的所有頁面，對每個頁面調用`heap_page_prune`進行處理。`heap_page_prune`調用`heap_prune_chain`函數來判斷，一個舊版本是否可以被回收（即刪除）。這里要考慮的一個重要因素是，舊版本是否可能被當前系統里正在進行的事務（活躍事務，即已開始但未提交或終止的事務）需要。`heap_prune_chain`調用了HeapTupleSatisfiesVacuum來對一個數據記錄的舊版本（tuple）做這個判斷。HeapTupleSatisfiesVacuum里面最重要的一個判斷如下： ~~~ if (!TransactionIdPrecedes(HeapTupleHeaderGetRawXmax(tuple), OldestXmin)) return HEAPTUPLE_RECENTLY_DEAD; ~~~ 這個判斷實際上是計算當前tuple的xmax是否大于或等于OldestXmin。我們知道，xmax是刪除這個tuple的事務ID，而OldestXmin由GetOldestXmin函數計算，是所有活躍事務的ID，以及所有事務的xmin 組成的集合中最小的事務ID。所有ID大于這個OldestXmin的事務，都是“新近”開啟的事務，其他事務可能需要讀取這個舊版本用于查詢，所以不能物理刪除，則返回`HEAPTUPLE_RECENTLY_DEAD`，保留此tuple（即不回收）。但如果系統中含有很久之前開啟而未提交的事務，并且這個事務由于執行過更新，創建了事務ID（成為“長事務”），那么OldestXmin會非常小，vacuum做上述這個判斷時，結果通常為true，即返回`HEAPTUPLE_RECENTLY_DEAD`，這樣`heap_prune_chain`將會保留此tuple（舊版本），導致回收無法完成，表膨脹由此發生。需要注意的是，并不是只有更新過數據的事務，長時間不提交會造成表膨脹，只讀的事務也是同樣的！看下面的case：控制臺A： ~~~ postgres=# begin; BEGIN postgres=# declare c1 cursor for select * from test_bloat for read only; DECLARE CURSOR postgres=# ~~~ 控制臺B: ~~~ postgres=# select * from pg_stat_activity; -[ RECORD 1 ]----+-------------------------------------------------------------- datid | 13003 datname | postgres pid | 110811 usesysid | 10 usename | guangzhou.zgz application_name | psql client_addr | client_hostname | client_port | -1 backend_start | 2015-11-12 11:01:00.880549+08 xact_start | 2015-11-12 11:01:32.843927x+08 query_start | 2015-11-12 11:01:33.87293+08 state_change | 2015-11-12 11:01:33.873547+08 waiting | f state | idle in transaction backend_xid | backend_xmin | 2436509 query | declare c1 cursor for select * from test_bloat for read only; ~~~ 我們看到，這個只讀事務的xmin是非空的，會被用來做OldestXmin，如果它長時間不完成操作（即cursore不close），就會造成整個數據庫的表膨脹！因此，即便是只讀事務，也要及時提交；另外，避免在存在大量更新操作的實例上，跑運行時間很長的查詢語句。 ## 回收膨脹的空間如何回收膨脹的空間？長事務結束后，vacuum會回收一部分舊版本。但它回收數據頁內的舊版本后，一般情況下并不能把空間還給操作系統。就是說，表所占的空間沒有變化。只有一種情況下，即回收的頁處于存儲數據的文件（一張表對應一個或多個文件）尾部，并且頁內沒有事務可見的tuple（即整個頁都可以刪除）時，會做truncate操作，把尾部的這些頁統一從文件中刪除，文件大小和表所占空間隨之減少。另一種回收膨脹空間的方法是，執行vacuum full 操作。vacuum full命令實際上重建了整張表和上面的索引。它的缺點是，需要長時間鎖住整張表，并耗費大量的IO，對應用影響很大。要減少vacuum full鎖住表的時間，可以使用社區提供的pg_repack工具。它的原理是基于原表創建一張新表，同時利用觸發器，將原表上當前的增量更新不斷記錄下來。新表建好后，將所記錄的增量更新應用到新表，直到新舊表數據完全一致。最后將新舊表表名互換，刪除舊表，即完成了表的空間整理操作，回收了空間。 ## 避免表膨脹的方法上面看到，表一旦膨脹，空間很難回收回來，所以要盡可能的避免表膨脹。要避免表膨脹，需要注意： 1. 盡早的、及時的提交事務； 2. 設計應用時，要使事務盡量短小； 3. 注意配置與應用規模相適應的硬件資源（IO能力、CPU、內存等），并調教好數據庫，使其性能最優，避免有些事務因為資源或性能問題長時間無法完成； 4. 提交autovacuum，使其能按合理的周期運行。這方面的內容，我們今后專門介紹； 5. 定期監控系統中是否有長事務，可以使用下面的SQL監控持續時間超過一定時間的事務： ~~~ select * from pg_stat_activity where state<>'idle' and pg_backend_pid() != pid and (backend_xid is not null or backend_xmin is not null ) and extract(epoch from (now() - xact_start)) > <時間閾值，單位秒> ; ~~~ 只要運用好上述方法，表膨脹是可以有效控制的。正常的表膨脹不會超過原來數據量的20%。