手動(dòng)執(zhí)行VACUUM，無(wú)法清理表中的dead tuple

1、問(wèn)題描述

在業(yè)務(wù)并不繁忙的情況下發(fā)現(xiàn)服務(wù)器負(fù)載較高，執(zhí)行SQL的速度明顯緩慢。檢查數(shù)據(jù)庫(kù)TOP SQL，發(fā)現(xiàn)與表test有關(guān)，查看該表的統(tǒng)計(jì)值，live_tuple只有91966條，dead tuple為702346條。

 
highgo=# select n_live_tup,n_dead_tup from pg_stat_all_tables where relname = 'test';
 n_live_tup | n_dead_tup
------------+------------
    91966 |   702346
(1 row)

手動(dòng)對(duì)該表執(zhí)行VACUUM同樣無(wú)法回收dead tuple，報(bào)錯(cuò)如下：

highgo=# vacuum(verbose,analyze) test;
INFO:  00000: vacuuming "public.test"
INFO:  00000: index "index_test_content_id" now contains 146076 row versions in 698 pages
DETAIL:  0 index row versions were removed.
175 index pages have been deleted, 126 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  00000: index "test_pkey" now contains 146076 row versions in 568 pages
DETAIL:  0 index row versions were removed.
77 index pages have been deleted, 68 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  00000: "test": found 0 removable, 789485 nonremovable row versions in 10178 out of 10276 pages
DETAIL:  702346 dead row versions cannot be removed yet.
There were 59707 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.04s/0.15u sec elapsed 0.20 sec.
INFO:  00000: vacuuming "pg_toast.pg_toast_16737"
INFO:  00000: index "pg_toast_16737_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.13 sec.
INFO:  00000: "pg_toast_16737": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.59 sec.
INFO:  00000: analyzing "public.test"
INFO:  00000: "test": scanned 10276 of 10276 pages, containing 90959 live rows and 702346 dead rows; 30000 rows in sample, 90959 estimated total row

2、問(wèn)題原因

當(dāng)我們開(kāi)啟一個(gè)事務(wù)后，為了實(shí)現(xiàn)多版本并發(fā)控制，數(shù)據(jù)庫(kù)會(huì)阻止vacuum清理未結(jié)束的長(zhǎng)事務(wù)之后產(chǎn)生的所有dead tuple,即使與該事務(wù)無(wú)關(guān)的表也是如此。對(duì)于未結(jié)束的長(zhǎng)事務(wù)而言，之后產(chǎn)生的所有dead tuple對(duì)于該事務(wù)都是可見(jiàn)的，所以即使手動(dòng)執(zhí)行vacuum也無(wú)法進(jìn)行清理。

• Live transactions performing a write operation in any table will prevent vacuuming dead rows
  generated by commited transactions that started after first live transaction in any other table.

3、解決方案

檢查數(shù)據(jù)庫(kù)中存在的長(zhǎng)事務(wù)：

highgo=#select        
  usename,
  datname,
  state,
  waiting,
  xact_start,
  now(),query,pid,
  now()-xact_start as time
from
  pg_stat_activity
where
  now()-xact_start>interval '5 sec'
  and query !~ '^COPY' and state<>'idle'
order by xact_start;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------
usename    | highgo
datname    | highgo
state      | idle in transaction
waiting    | f
xact_start  | 2018-07-02 11:09:22.607884+08
now       | 2018-07-04 16:59:43.212717+08
query      | select  xxxxxxxx
pid       | 17924
time       | 2 day 05:50:26.909971

檢查該SQL執(zhí)行未結(jié)束的原因，如需盡快恢復(fù)前臺(tái)應(yīng)用，可以在確認(rèn)安全的情況下使用如下命令，將該長(zhǎng)事務(wù)終止。

SELECT pg_terminate_backend(17924);

再次對(duì)該表手動(dòng)VACUUM：

highgo=# vacuum(verbose,analyze) test;
INFO:  00000: vacuuming "public.test"
INFO:  00000: scanned index "index_test_content_id" to remove 62482 row versions
DETAIL:  CPU 0.01s/0.09u sec elapsed 0.26 sec
INFO:  00000: scanned index "test_pkey" to remove 62482 row versions
DETAIL:  CPU 0.01s/0.08u sec elapsed 0.19 sec
INFO:  00000: "test": removed 62482 row versions in 11041 pages
DETAIL:  CPU 0.11s/1.30u sec elapsed 1.52 sec
INFO:  00000: index "index_test_content_id" now contains 91106 row versions in 698 pages
DETAIL:  62482 index row versions were removed.
346 index pages have been deleted, 152 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  00000: index "test_pkey" now contains 91106 row versions in 568 pages
DETAIL:  62482 index row versions were removed.
244 index pages have been deleted, 50 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  00000: "test": found 781909 removable, 87305 nonremovable row versions in 11179 out of 11215 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 777839 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.32s/2.97u sec elapsed 3.96 sec.
INFO:  00000: vacuuming "pg_toast.pg_toast_16737"
INFO:  00000: index "pg_toast_16737_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  00000: "pg_toast_16737": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  00000: analyzing "public.test"
INFO:  00000: "test": scanned 11215 of 11215 pages, containing 91106 live rows and 0 dead rows; 30000 rows in sample, 91106 estimated total rows
VACUUM

測(cè)試問(wèn)題SQL，確保該問(wèn)題不會(huì)再次發(fā)生。