小案例，大智慧 —— 深入浅出堆截断
xiongcc约 7 分钟前言
众所周知，vacuum 在某一阶段会执行截断的操作，也就是将文件末尾的页也 truncate，然后返还给操作系统，表的大小也会相应缩小，因此我们便可以利用这一技巧，尤其是在空间磁盘吃紧快要撑爆的时候 (因为 vacuum full，包括 pg_repack 都要求最多 2 倍的磁盘空间)。是的，pgcompacttable 便是利用这一原理。
今天，一位学员在群里问了这样一个问题："为什么要第二次执行 vacuum 才会执行截断这个操作？"，也就是第一次执行 vacuum 没有截断，第二次才截断了。
这位学员参照我的课件进行了如下实验 👇🏻
让我们分析一下！
复现
这位学员提供的复现步骤如下
create table test(id int,info text);
alter table test set (autovacuum_enabled=off);
truncate table test;
insert into test select n,md5(random()::text) from generate_series(1,10000) as n;
\dt+ test
delete from test where id <500;
vacuum test;
WITH a AS
(DELETE FROM test
WHERE ctid = ANY(SELECT ctid FROM test ORDER BY ctid DESC LIMIT 100)
RETURNING *)
INSERT INTO test SELECT * FROM a ;
vacuum verbose test;
当按照这个步骤来操作的话，确实可以发现，第一次没有截断 pages: 0 removed (源自学员的图)
第二次才进行了截断：pages: 1 removed
让我们简化一下操作序列：
postgres=# create table test(id int,info text) with (autovacuum_enabled=off);
CREATE TABLE
postgres=# insert into test select n,md5(random()::text) from generate_series(1,10000) as n;
INSERT 0 10000
postgres=# delete from test where id > 9500;
DELETE 500
postgres=# vacuum verbose test;
INFO: vacuuming "postgres.myschema.test"
INFO: finished vacuuming "postgres.myschema.test": index scans: 0
pages: 0 removed, 84 remain, 84 scanned (100.00% of total)
...postgres=# vacuum verbose test; ---第二次执行，才发生了截断
INFO: vacuuming "postgres.myschema.test"
INFO: table "test": truncated 84 to 80 pages
INFO: finished vacuuming "postgres.myschema.test": index scans: 0
pages: 4 removed, 80 remain, 1 scanned (1.19% of total) ---👈🏻
...
那么为什么会这样？这就要唠一唠堆截断的原理了，既然是 truncate，那么必然也是 AccessExclusiveLock，会造成不必要的锁冲突，甚至死锁，如果还有备库的话，还可能与备库上的一些查询操作产生冲突 (回放)，因此这个截断操作肯定要择机进行，满足一定条件才行，没错，核心代码便是 should_attempt_truncation，逻辑很简单 (这是 16 的代码，所以还可以看到 old_snapshot_threshold 参数)
/*
* should_attempt_truncation - should we attempt to truncate the heap?
*
* Don't even think about it unless we have a shot at releasing a goodly
* number of pages. Otherwise, the time taken isn't worth it, mainly because
* an AccessExclusive lock must be replayed on any hot standby, where it can
* be particularly disruptive.
*
* Also don't attempt it if wraparound failsafe is in effect. The entire
* system might be refusing to allocate new XIDs at this point. The system
* definitely won't return to normal unless and until VACUUM actually advances
* the oldest relfrozenxid -- which hasn't happened for target rel just yet.
* If lazy_truncate_heap attempted to acquire an AccessExclusiveLock to
* truncate the table under these circumstances, an XID exhaustion error might
* make it impossible for VACUUM to fix the underlying XID exhaustion problem.
* There is very little chance of truncation working out when the failsafe is
* in effect in any case. lazy_scan_prune makes the optimistic assumption
* that any LP_DEAD items it encounters will always be LP_UNUSED by the time
* we're called.
*
* Also don't attempt it if we are doing early pruning/vacuuming, because a
* scan which cannot find a truncated heap page cannot determine that the
* snapshot is too old to read that page.
*/
static bool
should_attempt_truncation(LVRelState *vacrel)
{
BlockNumber possibly_freeable; if (!vacrel->do_rel_truncate || VacuumFailsafeActive ||
old_snapshot_threshold >= 0)
return false;
possibly_freeable = vacrel->rel_pages - vacrel->nonempty_pages;
if (possibly_freeable > 0 &&
(possibly_freeable >= REL_TRUNCATE_MINIMUM ||
possibly_freeable >= vacrel->rel_pages / REL_TRUNCATE_FRACTION))
return true;
return false;
}
仅当尾部空闲空间至少占表大小的 1/16 或达到 1000 页的长度时，才会执行截断。
/*
* Space/time tradeoff parameters: do these need to be user-tunable?
*
* To consider truncating the relation, we want there to be at least
* REL_TRUNCATE_MINIMUM or (relsize / REL_TRUNCATE_FRACTION) (whichever
* is less) potentially-freeable pages.
*/
#define REL_TRUNCATE_MINIMUM 1000
#define REL_TRUNCATE_FRACTION 16/*
* Timing parameters for truncate locking heuristics.
*
* These were not exposed as user tunable GUC values because it didn't seem
* that the potential for improvement was great enough to merit the cost of
* supporting them.
*/
#define VACUUM_TRUNCATE_LOCK_CHECK_INTERVAL 20 /* ms */
#define VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL 50 /* ms */
#define VACUUM_TRUNCATE_LOCK_TIMEOUT 5000 /* ms */
因此，问题的关键就在于是否满足了这个条件，很简单，Debug 一下！
Breakpoint 1, should_attempt_truncation (vacrel=0x1680ed0) at vacuumlazy.c:2530
(gdb) n
(gdb) p vacrel->rel_pages
$1 = 84
(gdb) p vacrel->nonempty_pages
$2 = 80
所以第一次 possibly_freeable 为 4，并不满足 > 84/16 =5.25 (另外当然没有 1000 页)，所以返回了 false，因此对应着第一次跳过了，没有进行截断。
再看看第二次
(gdb) p vacrel->rel_pages
$3 = 84
(gdb) p vacrel->nonempty_pages
$4 = 0
为什么非空的页面为 0？？？其实这里利用到了 vm 可见性映射，跳过了 (页面压根没有扫描，自然 nonempty_pages 这个计数器没有增加)，并且始终会扫描最后一个页面，具体细节参照👇🏻
else
{
/* Last page always scanned (may need to set nonempty_pages) */
Assert(blkno < rel_pages - 1); if (skipping_current_range)
continue;
/* Current range is too small to skip -- just scan the page */
all_visible_according_to_vm = true;
}
vacrel->scanned_pages++; ---👈🏻
因此 possibly_freeable 肯定满足 84/16 了，所以第二次进行了截断。
从第二次打印的日志中也可以窥见一二
postgres=# vacuum verbose test;
INFO: vacuuming "postgres.myschema.test"
INFO: finished vacuuming "postgres.myschema.test": index scans: 0
pages: 0 removed, 84 remain, 84 scanned (100.00% of total) ---第一次postgres=# vacuum verbose test;
INFO: vacuuming "postgres.myschema.test"
INFO: table "test": truncated 84 to 80 pages
INFO: finished vacuuming "postgres.myschema.test": index scans: 0
pages: 4 removed, 80 remain, 1 scanned (1.19% of total) ---第二次
至此，这位学员的问题便已分析明白。
小结
另外，截断也是有次数限制的，各位打开 DEBUG2 就可以看到了，最多 5 秒，每 50 毫秒重试一次。
if (++lock_retry > (VACUUM_TRUNCATE_LOCK_TIMEOUT /
VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL))
{
/*
* We failed to establish the lock in the specified number of
* retries. This means we give up truncating.
*/
ereport(vacrel->verbose ? INFO : DEBUG2,
(errmsg("\"%s\": stopping truncate due to conflicting lock request",
vacrel->relname)));
return;
}/*
* Timing parameters for truncate locking heuristics.
*
* These were not exposed as user tunable GUC values because it didn't seem
* that the potential for improvement was great enough to merit the cost of
* supporting them.
*/
#define VACUUM_TRUNCATE_LOCK_CHECK_INTERVAL 20 /* ms */
#define VACUUM_TRUNCATE_LOCK_WAIT_INTERVAL 50 /* ms */
#define VACUUM_TRUNCATE_LOCK_TIMEOUT 5000 /* ms */
为了加深印象，各位读者可以试一下这个例子，然后照着 DEBUG 一下
postgres=# create table test1(id int) with (autovacuum_enabled=off);
CREATE TABLE
postgres=# insert into test1 values(generate_series(1,10000));
INSERT 0 10000
postgres=# delete from test1 where id > 9500;
DELETE 500
postgres=# vacuum verbose test1;postgres=# create table test(id int,info text) with (autovacuum_enabled=off);
CREATE TABLE
postgres=# insert into test select n,md5(random()::text) from generate_series(1,10000) as n;
INSERT 0 10000
postgres=# delete from test where id > 9500;
DELETE 500
postgres=# vacuum verbose test;
观察一下二者的差异，并分析一下为什么。
答案：第一次例子第一次就会截断，第二个例子第二次才会截断。