一、 Freezing 冻结
1. 引入原因
简单说来就是目前pg事务id只有32位，大业务量下很可能用完，触发事务id回卷（循环使用）。而pg是根据事务id大小判断可见性的，如果新事务却使用了小id，旧事务将可以看到新事务数据，新事务又看不到旧事务数据，打破数据一致性。
详情参考：pg事务篇（二）—— 事务ID回卷与事务冻结（freeze）_Hehuyi_In的博客-CSDN博客_vacuum_freeze_min_age
2. 冻结原理
pg将超出database horizond的元组做一个特殊标记，即进行冻结（9.4前是将其元组t_xmin设为2，9.4开始只将t_infomask设为HEAP_XMIN_FROZEN）。被冻结的事务被认为比所有事务都要旧，这样即便事务id回卷，只要旧事务id已被冻结，小的事务id也可以看到它（正常事务id从3开始），符合数据一致性。
3. 测试案例
先更新下老朋友heap_page函数，查看更多信息
CREATE FUNCTION heap_page(
relname text, pageno_from integer, pageno_to integer
)
RETURNS TABLE(
ctid tid, state text,
xmin text, xmin_age integer, xmax text
) AS $$
SELECT (pageno,lp)::text::tid AS ctid,
CASE lp_flags
WHEN 0 THEN 'unused'
WHEN 1 THEN 'normal'
WHEN 2 THEN 'redirect to '||lp_off
WHEN 3 THEN 'dead'
END AS state,
t_xmin || CASE
WHEN (t_infomask & 256+512) = 256+512 THEN ' f'
WHEN (t_infomask & 256) > 0 THEN ' c'
WHEN (t_infomask & 512) > 0 THEN ' a'
ELSE ''
END AS xmin,
age(t_xmin) AS xmin_age,
t_xmax || CASE
WHEN (t_infomask & 1024) > 0 THEN ' c'
WHEN (t_infomask & 2048) > 0 THEN ' a'
ELSE ''
END AS xmax
FROM generate_series(pageno_from, pageno_to) p(pageno),
heap_page_items(get_raw_page(relname, pageno))
ORDER BY pageno, lp;
$$ LANGUAGE sql;创建测试表，fillfactor = 10是为了使每页可容纳元组数变小（例如案例中仅为每页只能存2个元组）。
CREATE TABLE tfreeze(id integer,s char(300)) WITH (fillfactor = 10, autovacuum_enabled = off);
INSERT INTO tfreeze(id, s) SELECT id, 'FOO'||id FROM generate_series(1,100) id;
VACUUM tfreeze;
SELECT * FROM generate_series(0,1) g(blkno),pg_visibility_map('tfreeze',g.blkno) ORDER BY g.blkno;
SELECT * FROM heap_page('tfreeze',0,1);
xmin_age为1因为我们是当前db中最新的事务
4. 一些术语
-
表年龄：当前事务号距上一次表执行freeze操作的事务id的差值（表中最新xid- pg_class.relfrozenxid）
-
元组年龄：当前元组的xmin距上一次执行freeze操作的事务id的差值（t_xmin-pg_class.relfrozenxid）
-
惰性冻结（lazy freeze）：不扫描VM文件中标记为可见的页（页中元组对所有事务均可见），会跳过可见但需要冻结的元组。
-
迫切冻结（aggressive freeze）：
- 9.6版本前，不参考VM文件，扫描表中所有数据页并冻结需要冻结的元组，即使页中元组已经全部冻结，也会被扫描。
- 9.6版本开始，在VM文件中引入了all-frozen标志位，表示页中元组已全部冻结。扫描时会参考VM文件，跳过all-frozen的页面，并在完成其他页面冻结后设置该标记位。
- 手动执行vacuum freeze属于迫切冻结。
5. 冻结相关参数
- vacuum_freeze_min_age：自上次冻结后，经过多少元组年龄会触发下次惰性冻结，默认值为5000万。
- vacuum_freeze_table_age：自上次冻结后，经过多少表年龄会触发表迫切冻结，默认值为1.5亿。
- autovacuum_freeze_max_age：自上次冻结后，经过多少元组年龄，元组对应表会强制触发autovacuum（即便设置为off），默认值为2亿，最大值为20亿。
- vacuum_failsafe_age：pg 14新参数，当出现事务id回卷风险（元组年龄大于vacuum_failsafe_age）时，以最快速度完成冻结操作。默认值为16亿，pg假设此变量值大于autovacuum_freeze_max_age。
具体介绍参考下文：
1）vacuum_freeze_min_age
为了方便观察，我们把vacuum_freeze_min_age改为1（测试完记得改回来）
ALTER SYSTEM SET vacuum_freeze_min_age = 1;
-- 反向操作
-- alter system reset vacuum_freeze_min_age;
SELECT pg_reload_conf();
UPDATE tfreeze SET s = 'BAR' WHERE id = 1;
SELECT * FROM heap_page('tfreeze',0,1);
可以看到xmin_age已经大于1，但并不会自动触发冻结
SELECT * FROM generate_series(0,1) g(blkno),pg_visibility_map('tfreeze',g.blkno) ORDER BY g.blkno;
update后0号页在vm映射中并非完全可见，会被vacuum处理。
执行vacuum触发冻结
VACUUM tfreeze;
SELECT * FROM heap_page('tfreeze',0,1);
(0,1)是第一个元组，被保留并重定向；(0,2) xmin_age=2，被冻结；(0,3) xmin_age=1，不需要处理。1号页的元组因为vm中完全可见，会被vacuum跳过。
SELECT * FROM generate_series(0,1) g(blkno),pg_visibility_map('tfreeze',g.blkno) ORDER BY g.blkno;
冻结后0号页的元组也变为vm中完全可见
2）vacuum_freeze_table_age
如上面实验，vacuum_freeze_min_age会考虑vm文件，如果这些页一直可见，其中元组事务id就一直不会被冻结。
因此，我们引入一个新参数vacuum_freeze_table_age，它无视vm文件，只要表年龄达到一定值，就会触发表迫切冻结。
3）autovacuum_freeze_max_age
前面两个参数针对的都是vacuum操作，如果关闭了表的autovacuum设置而又不手动执行vacuum，长此以往会遇到问题，另外一些系统库例如template0也不能手动vacuum。因此pg引入了本参数，当元组年龄达到时强制触发autovacuum（即便设置为off）。
默认值为2亿，最大值为20亿，这考虑了事务id最大为42亿，保证在两次freeze之间，txid的增长肯定不会超过20亿。
autovacuum_freeze_max_age参数值也会影响CLOG，因为没有必要记录已冻结事务的状态，小于datfrozenxid的事务对应的CLOG将会被autovacuum删除。
对于上面提到的3个参数，表级也有对应autovacuum参数
- autovacuum_freeze_min_age 与 toast.autovacuum_freeze_min_age
- autovacuum_freeze_table_age 与 toast.autovacuum_freeze_table_age
- autovacuum_freeze_max_age 与 toast.autovacuum_freeze_max_age
4）vacuum_failsafe_age
pg 14新参数，当出现事务id回卷风险（元组年龄大于vacuum_failsafe_age）时，触发全速vacuum模式，忽略autovacuum_vacuum_cost_delay 和vacuum_cost_delay参数，跳过索引vacuum，以最快速度完成冻结操作。
6. 手动冻结
- vacuum freeze命令：手动触发迫切冻结，冻结所有元组而不管它们的age，相当于vacuum_freeze_min_age = 0
- 导入数据时冻结：COPY tfreeze FROM stdin WITH FREEZE;
二、 表和索引rebuild
1. 需要rebuild的原因
- 大量删除过数据，数据/索引页中包含许多空的或者近似于空的页
- 修改了存储参数，需要重建否则会失效（如修改fillfactor）
- 软件bug或者硬件原因导致的索引不可用
- concurrently创建索引时失败，遗留了一个失效的索引
其中最常见还是第一种，下面以第一种为例
2. 估算数据密度（原生视图）
查看表和索引占用的page数
-- relpages只能被VACUUM、ANALYZE和几个DDL命令更新，如CREATE INDEX
SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 't' or relname = 't_n';查看表和索引死记录
-- 依赖于统计信息的更新操作, 譬如系统自动定时执行 vacuum analyze relname
select relname,last_vacuum,n_live_tup,n_dead_tup from pg_stat_all_tables where relname = 't'; 3. 估算数据密度（pgstattuple 插件）
上面的视图虽然可以看，但还需要自己转换单位、计算比例等，不太方便，更简便的方法可以使用pgstattuple插件。
pgstattuple模块提供了pgstatetuple()和pgstatindex()两个统计表和索引的方法，可以直接看到上面的信息。另外，在表未收集统计信息前pg_class和pg_stat_all_tables中没有数据，但该模块能精确查询（说明查询的是表和索引 page的物理信息，而非查询的上面视图）。
TRUNCATE table vac;
INSERT INTO vac(id,s) SELECT id, id::text FROM generate_series(1,500000) id;
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('vac') \gx
如果表比较大，可以用pgstattuple_approx函数粗略预估
SELECT * FROM pgstattuple_approx ('vac') \gx
索引密度估算
SELECT * FROM pgstatindex('vac_s') \gx
查看表和索引大小
SELECT pg_size_pretty(pg_table_size('vac')) AS table_size,pg_size_pretty(pg_indexes_size('vac')) AS index_size;
删除90%的数据（delete不释放空间）
DELETE FROM vac WHERE id % 10 != 0;
SELECT pg_size_pretty(pg_table_size('vac')) AS table_size,pg_size_pretty(pg_indexes_size('vac')) AS index_size;
SELECT vac.tuple_percent, vac_s.avg_leaf_density FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s;
VACUUM FULL vac;可以看到占用空间大幅下降
2. Rebuild方法
常规方法
- vacuum full
- cluster（类似SqlServer的聚集索引？）
- truncate
- reindex（vacuum full和cluster命令都包含rebuild index阶段）
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ];
注释：
INDEX 重构指定的索引；
TABLE 重构指定表的所有索引，包括下级TOAST表；
DATABASE重构指定数据库的所有索引，系统共享索引也会被执行。
SYSTEM 重构这个系统的索引包含当前的数据库。
Name 按照不同级别索引的名称。rebuild表和索引类似于先删除再创建，此时会获取排他锁，影响业务。因此更常用的是以下插件，主要用于减少停机时间。
- pg_repack：在线rebuild表和索引
- pgcompacttable
扫一扫
727
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
