# PostgreSQL处理膨胀与事务回卷

> 作者/来源: UCloud 运营管理员
> 发布时间: 2023-01-11T05:20:00.000Z
> 分类: 其他
> 标签: 数据库
> 原文链接: http://117.50.162.249:3000/yun/articles/892

---

# PostgreSQL处理膨胀与事务回卷

> 来源: https://www.ucloud.cn/yun/129950.html
> 作者: IT那活儿
> 发布日期: 发布于2023-01-11 13:20

PostgreSQL处理膨胀与事务回卷

**一、表膨胀查询与处理**

**1、创建扩展**

|  |
| --- |
| create extension pgstattuple; |

**2、表膨胀查询**

如下查询出来表的怕膨胀系数为81%。

|  |
| --- |
| select \*, 1.0 - tuple\_len::numeric / table\_len as bloat from pgstattuple(tab\_brin1); |

![](https://ucloud-blog.cn-bj.ufileos.com/articles/129950/images/129950_000.png)

占用2414个page。

|  |
| --- |
| select \* from pg\_relpages(tab\_brin1); |

![](https://ucloud-blog.cn-bj.ufileos.com/articles/129950/images/129950_001.png)

![](https://ucloud-blog.cn-bj.ufileos.com/articles/129950/images/129950_002.png)

**3、表膨胀处理**

|  |
| --- |
| vacuum (verbose,full,analyze) tab\_brin1; |

**Vacuum**

它将进行普通的垃圾收集，将垃圾空间标识为可用的状态。它不会影响其它事务发出的表上的读操作和写操作，因为普通的垃圾收集不会在表上加一个互斥锁。

**VacuumFull**

启动完全垃圾收集，完全垃圾收集会在表上加一个互斥锁，对表进行垃圾回收期间，其它的事务不能对表进行读操作和写操作。VACUUMFULL比VACUUM的执行时间要长一些，执行的操作也多一些，它在进行垃圾收集的过程中，可能会将一个记录从一个数据块转移到另一个数据块。

**Vacuumanalyze**

除了回收垃圾空间还收集优化器统计数据

**Vacuumverbose**

输出垃圾收集的详细数据。

回收完后，膨胀系数降到3%。

|  |
| --- |
| select \*, 1.0 - tuple\_len::numeric / table\_len as bloat from pgstattuple(tab\_brin1); |

![](https://ucloud-blog.cn-bj.ufileos.com/articles/129950/images/129950_003.png)

表占用473个page。

|  |
| --- |
| select \* from pg\_relpages(tab\_brin1); |

![](https://ucloud-blog.cn-bj.ufileos.com/articles/129950/images/129950_004.png)

二、数据库防止事务回卷

**VacuumFreeze**

为了保证同一个数据库中的最新和最旧的两个事务之间的年龄不超过2^31，postgresql引入了冻结(freeze)功能。

涉及到的术语：

1、表年龄：当前事务号距上一次执行freeze操作的事务id的差值

2、元组年龄：当前元组的xmin距上一次执行freeze操作的事务id的差值

如果发生当新老事务id差超过21亿的时候，事务号会发生回卷，此时数据库会报出如下错误并且拒绝接受所有连接，必须进入单用户模式执行vacuumfreeze操作。

事务冻结操作：

|  |
| --- |
| vacuum freeze tab\_brin1; |

查看指定表的年龄

|  |
| --- |
| SELECT relname, age(relfrozenxid) as xid\_age,pg\_size\_pretty(pg\_table\_size(oid)) as table\_size FROM pg\_class WHERE relname = tab\_brin1; |

查询所有数据库的年龄：

|  |
| --- |
| select datname, age(datfrozenxid) from pg\_database; |

通常报错如下：

error：database is not accepting commands to avoid wraparound data loss indatabase “mydb”

hint：stop the postmaster and vacuum that database in single-user mode

参数设置：

在postgresql中，vacuum是一个比较耗费io的过程，而vacuumfreeze更是被称为“冻结炸弹”，因为涉及到了大量的读写io，读io(datafile)和写io(datafile以及写wal)。对于业务繁忙的库，可能会出现如下情况：

可能有很多大表的年龄会先后到达2亿，数据库的autovacuum会开始对这些表依次进行vacuumfreeze，从而集中式的爆发大量的读写io，数据库和操作系统响应迟缓，如果又碰上业务高峰，会出现很不好的影响。

所以设置好参数尤为重要：

1. 设置vacuum\_cost\_delay为一个比较高的数值（例如50ms），这样可以减少普通vacuum对正常数据查询的影响。
2. autovacuum\_freeze\_max\_age和vacuum\_freeze\_table\_age的值也不适合设置过大，因为过大会造成pg\_clog中的日志文件堆积，来不及清理。我们把autovacuum\_freeze\_max\_age设置为最大值20亿。
3. vacuum\_freeze\_table\_age设置为0.95\* autovacuum\_freeze\_max\_age。
4. vacuum\_freeze\_min\_age不宜设置过小，比如我们freeze某个元组后，这个元组马上又被更新，那么之前的freeze操作其实是无用功，freeze真正应该针对的是那些长时间不被更新的元组。
5. 生产环境中做好pg\_database.frozenxid的监控，当快达到触发值时，我们应该选择一个业务低峰期窗口主动执行vacuumfreeze操作，而不是等待数据库被动触发。
6. 分区，把大表分成小表。每个表的数据量取决于系统的io能力，前面说了vacuumfreeze是扫全表的，现代的硬件每个表建议不超过32gb，单表数据不要超过3000w。
7. 对大表设置不同的vacuum年龄
8. 用户自己调度 freeze，如在业务低谷的时间窗口，对年龄较大，数据量较大的表进行vacuumfreeze。
9. 年龄只能降到系统存在的最早的长事务即 min(pg\_stat\_activity.(backend\_xid,backend\_xmin))。因此也需要密切关注长事务。

![](https://ucloud-blog.cn-bj.ufileos.com/articles/129950/images/129950_005.png)

END

![](https://ucloud-blog.cn-bj.ufileos.com/articles/129950/images/129950_006.png)