PostgreSQL处理膨胀与事务回卷

IT那活儿发布于2023-01-11 13:20 / 2700人阅读

PostgreSQL处理膨胀与事务回卷

一、表膨胀查询与处理

1、创建扩展

create extension pgstattuple;

2、表膨胀查询

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

select *, 1.0 - tuple_len::numeric / table_len as bloat from pgstattuple(tab_brin1);

占用2414个page。

select * from pg_relpages(tab_brin1);

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);

表占用473个page。

select * from pg_relpages(tab_brin1);

二、数据库防止事务回卷

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，数据库和操作系统响应迟缓，如果又碰上业务高峰，会出现很不好的影响。

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

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

END

云服务器 GPU云服务器 postgresql 事务回滚事务处理 jsp事务处理 aspnet事务处理

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129950.html

PostgreSQL9.6：新增加“idle in transaction”超时空闲事务自动查杀功能

摘要：以上出自发行说明，这段指出版本支持自动查杀超过指定时间的空闲事务连接，下面演示下。修改以下参数备注参数单位为毫秒，这里设置超时空闲事务时间为秒。数据库日志备注数据库日志里清晰地记录了进程的连接由于空闲事务超时被断开连接。熟悉 PostgreSQL 的朋友应该知道 idle in transaction 进程，引发 idle in transaction 的原因很多，例如应用代码中忘记...

meislzhua 2019-07-25 12:34 评论0 收藏0
深入解析 PostgreSQL 系列之并发控制与事务机制

摘要：深入解析系列之并发控制与事务机制并发控制旨在针对数据库中对事务并行的场景，保证中的一致性与隔离。启动并执行第一个命令。事务管理器分配，并返回事务快照，因为正在进行中。意味着该行由另一个并发事务更新，并且其事务尚未终止。 showImg(https://segmentfault.com/img/remote/1460000018081793); 深入解析 PostgreSQL 系列整理...

JohnLui 2019-07-25 12:37 评论0 收藏0
深入解析 PostgreSQL 系列之并发控制与事务机制

摘要：深入解析系列之并发控制与事务机制并发控制旨在针对数据库中对事务并行的场景，保证中的一致性与隔离。启动并执行第一个命令。事务管理器分配，并返回事务快照，因为正在进行中。意味着该行由另一个并发事务更新，并且其事务尚未终止。 showImg(https://segmentfault.com/img/remote/1460000018081793); 深入解析 PostgreSQL 系列整理...

leone 2019-06-25 19:05 评论0 收藏0
构建可扩展的PostgreSQL解决方案

摘要：这可以通过负载平衡来实现数据分片当问题不是并发查询的数量，而是数据库的大小和单个查询的速度时，可以实现不同的方法。 showImg(https://segmentfault.com/img/remote/1460000018875091); 来源 | 愿码(ChainDesk.CN)内容编辑愿码Slogan | 连接每个程序员的故事网站 | http://chaindesk.cn...

jonh_felix 2019-07-25 12:38 评论0 收藏0