当前位置:   article > 正文

postgresql_internals-14 学习笔记(三)冻结、rebuild_pg判断当前元组是否被冻结

pg判断当前元组是否被冻结

一、 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函数,查看更多信息

  1. CREATE FUNCTION heap_page(
  2. relname text, pageno_from integer, pageno_to integer
  3. )
  4. RETURNS TABLE(
  5. ctid tid, state text,
  6. xmin text, xmin_age integer, xmax text
  7. ) AS $$
  8. SELECT (pageno,lp)::text::tid AS ctid,
  9. CASE lp_flags
  10. WHEN 0 THEN 'unused'
  11. WHEN 1 THEN 'normal'
  12. WHEN 2 THEN 'redirect to '||lp_off
  13. WHEN 3 THEN 'dead'
  14. END AS state,
  15. t_xmin || CASE
  16. WHEN (t_infomask & 256+512) = 256+512 THEN ' f'
  17. WHEN (t_infomask & 256) > 0 THEN ' c'
  18. WHEN (t_infomask & 512) > 0 THEN ' a'
  19. ELSE ''
  20. END AS xmin,
  21. age(t_xmin) AS xmin_age,
  22. t_xmax || CASE
  23. WHEN (t_infomask & 1024) > 0 THEN ' c'
  24. WHEN (t_infomask & 2048) > 0 THEN ' a'
  25. ELSE ''
  26. END AS xmax
  27. FROM generate_series(pageno_from, pageno_to) p(pageno),
  28. heap_page_items(get_raw_page(relname, pageno))
  29. ORDER BY pageno, lp;
  30. $$ LANGUAGE sql;

       创建测试表,fillfactor = 10是为了使每页可容纳元组数变小(例如案例中仅为每页只能存2个元组)。

  1. CREATE TABLE tfreeze(id integer,s char(300)) WITH (fillfactor = 10, autovacuum_enabled = off);
  2. INSERT INTO tfreeze(id, s) SELECT id, 'FOO'||id FROM generate_series(1,100) id;
  3. VACUUM tfreeze;
  4. 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(测试完记得改回来)

  1. ALTER SYSTEM SET vacuum_freeze_min_age = 1;
  2. -- 反向操作
  3. -- alter system reset vacuum_freeze_min_age;
  4. SELECT pg_reload_conf();
  5. UPDATE tfreeze SET s = 'BAR' WHERE id = 1;
  6. 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触发冻结

  1. VACUUM tfreeze;
  2. 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数

  1. -- relpages只能被VACUUM、ANALYZE和几个DDL命令更新,如CREATE INDEX
  2. SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 't' or relname = 't_n';

查看表和索引死记录

  1. -- 依赖于统计信息的更新操作, 譬如系统自动定时执行 vacuum analyze relname
  2. 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的物理信息,而非查询的上面视图)。

  1. TRUNCATE table vac;
  2. INSERT INTO vac(id,s) SELECT id, id::text FROM generate_series(1,500000) id;
  3. CREATE EXTENSION pgstattuple;
  4. 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不释放空间)

  1. DELETE FROM vac WHERE id % 10 != 0;
  2. SELECT pg_size_pretty(pg_table_size('vac')) AS table_size,pg_size_pretty(pg_indexes_size('vac')) AS index_size;
  3. 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阶段)
  1. REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ];
  2. 注释:
  3. INDEX 重构指定的索引;
  4. TABLE 重构指定表的所有索引,包括下级TOAST表;
  5. DATABASE重构指定数据库的所有索引,系统共享索引也会被执行。
  6. SYSTEM 重构这个系统的索引包含当前的数据库。
  7. Name 按照不同级别索引的名称。

       rebuild表和索引类似于先删除再创建,此时会获取排他锁,影响业务。因此更常用的是以下插件,主要用于减少停机时间。

  • pg_repack:在线rebuild表和索引
  • pgcompacttable
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Li_阴宅/article/detail/741200
推荐阅读
相关标签
  

闽ICP备14008679号