PostgreSQL : Tuples 被 freezing 的几种情况

根据 PostgreSQL 的 MVCC 机制,数据被插入时 PostgreSQL会分配给每行 tuples一个事务ID,即表上的隐含字段 Xmin, 而 PostgreSQL 的事务号由 32 bit 位 ( 40 亿) 组成,事务号分配完了后会循环,这样会造成过去的记录不可见的情况,为了解决这个问题,理论上在 20 亿事务之内需要 vacuum 每一个数据库的每一个表, 而vacuum 操作会替换某些老记录的xid 成 FrozenXID,这样即使事务号循环,这些被替换成 FrozenXID 的记录依然对当前事务可见,下面是模拟表数据的tuples 的xid 被替换成 FrozenXID 的几个例子。

Freezing 实验一 ( vacuum freeze )

1.1 创建测试表并插入测试数据

1
2
3
4
5
6
7
8
mydb=> create table test_vacuum1(id integer,name varchar(32));  
CREATE TABLE
mydb=> insert into test_vacuum1 values (1,'a');
INSERT 0 1
mydb=> insert into test_vacuum1 values (2,'b');
INSERT 0 1
mydb=> insert into test_vacuum1 values (3,'c');
INSERT 0 1

1.2 查询表及tuples 的年龄

1
2
3
4
5
6
7
8
9
10
11
12
mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum1';  
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum1 | 3613 | 4
(1 row)
mydb=> select xmin,age(xmin),* from test_vacuum1;
xmin | age | id | name
------+-----+----+------
3614 | 5 | 1 | a
3615 | 4 | 2 | b
3616 | 3 | 3 | c
(3 rows)

1.3 执行 vacuum freeze

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mydb=> vacuum freeze test_vacuum1;  
VACUUM

mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum1';
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum1 | 3620 | 0
(1 row)

mydb=> select xmin,age(xmin),* from test_vacuum1;
xmin | age | id | name
------+------------+----+------
2 | 2147483647 | 1 | a
2 | 2147483647 | 2 | b
2 | 2147483647 | 3 | c
(3 rows)

备注:对表 test_vacuum1 执行 vacuum freeze 后,test_vacuum1 上的所有记录被 frozen,即 xmin 值被修改,并且 tuples 的年龄为 2147483647,这个 XID 比较特殊,不参与普通 XID的比较,可以认为这个 XID 比 所有 XID 都老。

Freezing 实验二 ( vacuum )

–2.1 创建表并插入测试数据

1
2
3
4
5
6
7
8
mydb=> create table test_vacuum2 (id integer,name varchar(32));  
CREATE TABLE
mydb=> insert into test_vacuum2 values (1,'a');
INSERT 0 1
mydb=> insert into test_vacuum2 values (2,'b');
INSERT 0 1
mydb=> insert into test_vacuum2 values (3,'c');
INSERT 0 1

2.2 查看表和 tuples 年龄

1
2
3
4
5
6
7
8
9
10
11
12
13
mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum2';  
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum2 | 3623 | 4
(1 row)

mydb=> select xmin,age(xmin),* from test_vacuum2;
xmin | age | id | name
------+-----+----+------
3624 | 4 | 1 | a
3625 | 3 | 2 | b
3626 | 2 | 3 | c
(3 rows)

2.3 设置 session 级 vacuum_freeze_min_age 参数,并 vacuum

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
mydb=> set vacuum_freeze_min_age=10;  
SET
mydb=> vacuum test_vacuum2;
VACUUM
mydb=> select xmin,age(xmin),* from test_vacuum2;
xmin | age | id | name
------+-----+----+------
3624 | 5 | 1 | a
3625 | 4 | 2 | b
3626 | 3 | 3 | c
(3 rows)

mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum2';
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum2 | 3623 | 7
(1 row)

mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum2';
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum2 | 3623 | 8
(1 row)

mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum2';
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum2 | 3623 | 9
(1 row)

mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum2';
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum2 | 3623 | 10
(1 row)

mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum2';
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum2 | 3623 | 11
(1 row)

mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum2';
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum2 | 3623 | 12
(1 row)

mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum2';
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum2 | 3623 | 13
(1 row)

mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum2';
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum2 | 3623 | 14
(1 row)

mydb=> vacuum test_vacuum2;
VACUUM
mydb=> select xmin,age(xmin),* from test_vacuum2;

xmin | age | id | name
------+------------+----+------
2 | 2147483647 | 1 | a
2 | 2147483647 | 2 | b
2 | 2147483647 | 3 | c
(3 rows)

备注:发现 test_vaccum2 的记录 xmin 被替换成 frozenXID。

Freezing 实验三 ( autovacuum )

3.1 创建测试表并插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mydb=> create table test_vacuum3(id integer,name varchar(32));  
CREATE TABLE
mydb=> insert into test_vacuum3 select generate_series(1,1000),'francs';
INSERT 0 1000

mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum3';
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum3 | 3651 | 2
(1 row)

mydb=> select xmin,age(xmin) from test_vacuum3 order by id limit 10;
xmin | age
------+-----
3652 | 2
3652 | 2
3652 | 2
3652 | 2
3652 | 2
3652 | 2
3652 | 2
3652 | 2
3652 | 2
3652 | 2
(10 rows)

3.2 修改表的 autovacuum_freeze_min_age 参数

1
2
3
4
5
6
7
8
9
10
11
mydb=> alter table test_vacuum3 set (autovacuum_freeze_min_age = 0);  
ALTER TABLE

mydb=> /d+ test_vacuum3;
Table "mydb.test_vacuum3"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
id | integer | | plain |
name | character varying(32) | | extended |
Has OIDs: no
Options: autovacuum_freeze_min_age=0

3.3 删除 250 条记录,触发 autovacuum

1
2
mydb=> delete from test_vacuum3 where id < 251;  
DELETE 250

备注:关于删除多少条记录会触发 autovacuum,可以参考之前写的 blog: https://postgres.fun/20110314162516.html

3.4 观察 CSV 日志

在上步 delete 数据后,等一会,如果看到以下日志,说明表 test_vacuum3 被 autovacuum 进程 vacuum了。

1
2
3
4
5
2012-05-06 21:42:04.864 CST,,,32232,,4fa67fac.7de8,1,,2012-05-06 21:42:04 CST,3/397,3660,LOG,00000,"automatic analyze of table ""mydb.mydb.test_vacuum3"" system usage: CPU 0.00s/0.00u sec elapsed 0.01 sec",,,,,,,,,""  
2012-05-06 21:43:04.847 CST,,,32237,,4fa67fe8.7ded,1,,2012-05-06 21:43:04 CST,3/405,0,LOG,00000,"automatic vacuum of table ""mydb.mydb.test_vacuum3"": index scans: 0
pages: 0 removed, 5 remain
tuples: 0 removed, 750 remain
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec",,,,,,,,,""

备注:修改表的 autovacuum_freeze_min_age 参数,目标是让表的 tuples 在 autovacuum 时即被 frozenXID替换。

3.5 再次查看表和 tuples 的年龄

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mydb=> select relname,relfrozenxid,age(relfrozenxid) from pg_class where relname='test_vacuum3';  
relname | relfrozenxid | age
--------------+--------------+-----
test_vacuum3 |

mydb=> select xmin,age(xmin) from test_vacuum3 order by id limit 10;
xmin | age
------+------------
2 | 2147483647 2 | 2147483647
2 | 2147483647
2 | 2147483647
2 | 2147483647
2 | 2147483647
2 | 2147483647
2 | 2147483647
2 | 2147483647
2 | 2147483647
(10 rows)

备注:表 test_vacuum3 的 xmin 已经被 FrozenXID 替换,其中实验三需要触发两个条件,第一需要想办法触发 autovacuum,第二需设置事务时长,触发表上记录被 FrozenXID 替换。

参考

原创文章,作者:1402239773,如若转载,请注明出处:https://blog.ytso.com/tech/database/237849.html

(0)
上一篇 2022年1月29日 22:29
下一篇 2022年1月29日 22:29

相关推荐

发表回复

登录后才能评论