投稿
  1. 云图网首页
  2. 技术专区
  3. 数据库

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/237849.html

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

相关推荐

发表回复

登录后才能评论