PostgreSQL 为什么怕听到FREEZEN 这个词的解释与怕什么有解决办法(2)

上期是抛出问题,并给出了监控的一些方案,本期就的论论,到底怎么解决这个问题,

1 先在快速回顾一下问题,当表的xmin达到2亿,数据库的autovacuum开始对达到的表开始进行vacuum freeze的操作,而如果此时大多数的表都在这个状态则I/O会........

那么POSTGRESQL的灵活的参数和经验的设置就会对这个问题起到很重要的缓解和帮助.

autovacuum_freeze_max_age

这个参数是POSTGRESQL 强制进行冻结操作的参数设置,这个参数两个功能

1 方式行事务ID 内卷

2 清理pg_xact 的中过期的文件.

在下面的解释中需要读者和写作的者先达成一些观点的一致性.

freeze的工作是一件消耗I/O的事情,并且我们希望每个表开始进行freeze process 的时间是不一致的,就如同北京每天的早上7点多的地铁站,

如果每个单位上班的时间是不一致的,有8点 ,有8点半 有9点 有9点半,还有10点的, 那北京的地铁还会那么拥挤吗?

所以我们也不希望一个数据库的表在同一时间时间都达到要被FREEZE 的LIMIT ,听上去很简单,让他们在不同的时间去做就好了.

一个数据库里面的多个表尤其在POSTGRESQL 他们必然有很多的关联性,也就是说,他们的很可能在差不多的时间会发生FREEZE,因为都在9点上班. 就会产生autovacuum freeze.

那么下面就是体现POSTGRESQL 的灵活性和管理的经验的地方了,POSTGRESQL 对于每个表也是可以进行autovacuum_freeze_max_age

,对于HOT 表自然可以进行进行一些特殊的处理,让这些"不听话的大表",让他们设置不同得autovacuum_freeze_max_age ,让他们错开autovacuum 时的freeze的可能性.

那么还有什么好的方法,分区表,把大表分成小表,对于大表频繁更新的表建立自己的autovacuum_freeze_max_age时间, 在业务不繁忙期,进行vacuum freeze的操作,降低长事务的数量,短小的事务应该被提倡.

另外还有两个参数

vacuum_freeze_min_age = 50000000

vacuum_freeze_table_age = 150000000

vacuum_freeze_min_age 主要的功能设置针对在进行vacuum 最小的需要开始进行freeze 的事务数. 这个值主要考虑中等的繁忙的表,提前进行标记freeze的活动.

vacuum_freeze_table_age 与上面的参数不同,这个参数是主动扫描,他会对表中所有的数据进行一个扫描,将超过设置期限的进行FREEZE的活动.

所以如何避免这个问题其实还是需要从两个部分

1 从数据库的层面来进行处理

2 从程序的设计和使用数据库的角度来进行

从数据库的层面就是调整一些关于vacuum freeze 的参数,可以挺过一些大表来单独调整这些参数,个性化的处理.同时对于大表可以采用分区表的方式来处理数据. 对于AUTOVACUUM 的操作进行优化, 调整autovacuum workers的数量,调整 maintenance workmem 的内存大小,尽量可以调整为内存总体的10%, 越繁忙的系统可以调整的越高, 并且替换成SSD 的磁盘系统,尤其是存储数据磁盘的方面,都有利于,并且时刻监控一些大的,耗时的SQL, 降低他们的耗时,实时监控数据库级别 ,表级别的(大表)的 relfrozenxid.

select relname,relfrozenxid from pg_class where relname ='test';

从程序的方向去看,尽量对于表的操作中,如果多次UPDATE 可以合并进行,通过程序或者REDIS 等其他架构的方式,让落地的数据减少短时间频繁的数据的更新的设计, 根据业务的逻辑,将大表在初期就根据某些逻辑,例如时间,设计成分区表.(PG的版本尽量在12及以上) 可以充分发挥PG的特性和强大的功能.