建议使用以下浏览器,以获得最佳体验。 IE 9.0+以上版本 Chrome 31+ 谷歌浏览器 Firefox 30+ 火狐浏览器
请选择 进入手机版 | 继续访问电脑版
设置昵称

在此一键设置昵称,即可参与社区互动!

确定
我再想想
选择版块
092947bevjlmiytnwwd8ky.jpg 【乘风破浪赋能学习季】2020华为云AI实战营,华为专家带你免费学AI! 2020年华为云AI实战营 华为云普惠AI
标签
您还可以添加5个标签
  • 没有搜索到和“关键字”相关的标签
  • 云产品
  • 解决方案
  • 技术领域
  • 通用技术
  • 平台功能
取消

彩虹上的水瓶座

发帖: 62粉丝: 21

级别 : 版主

发消息 + 关注

发表于2020年06月29日 21:18:50 1231 28
直达本楼层的链接
楼主
显示全部楼层
[博文鉴赏] PostgreSQL中导致表对象膨胀的常见原因有哪些

经常看到有人说表又膨胀了,那么导致对象膨胀的常见原因有哪些呢?

1. 未开启autovacuum

对于未开启autovacuum的用户,同时又没有合理的自定义vacuum调度的话,表的垃圾没有及时回收,新的数据又不断进来,膨胀是必然的。(新的数据包括插入和更新,更新产生新版本的记录)

2. 开启了autovacuum, 但是各种原因导致回收不及时,并且新的数据又不断产生,从而导致膨胀。

回收不及时的原因:

2.1. IO

当数据库非常繁忙时,如果IO比较差,会导致回收垃圾变慢,从而导致膨胀。

这种一般出现在数据库中存在非常巨大的表,并且这些表在执行whole table vacuum (prevent xid wrapped, 或当表的年龄大于vacuum_freeze_table_age时会全表扫),因此产生大量IO,这期间很容易导致自身或其他表膨胀。

2.2. autovacuum触发较迟

什么情况会触发autovacuum?

 * A table needs to be vacuumed if the number of dead tuples exceeds a 

 * threshold.  This threshold is calculated as 

 * 

 * threshold = vac_base_thresh + vac_scale_factor * reltuples 

如果没有设置表级别的autovacuum threshfactor,那么默认使用参数文件配置的值。如下:

int                     autovacuum_vac_thresh;  // 默认50 

double          autovacuum_vac_scale;  // 默认0.2 

也就是说dead tuple达到约为表的20%时,才触发autovacuum
然后回收又需要一定的时间,所以最终表的膨胀应该是超过20%的。

2.3. 所有worker繁忙,某些表产生的垃圾如果超过阈值,但是在此期间没有worker可以为它处理垃圾回收的事情。导致可能发生膨胀。

forkworker进程个数是参数autovacuum_max_workers决定的,初始化autovacuum共享内存时已固定了它的最大进程数。

如果数据库的表很多,而且都比较大,那么当需要vacuum的表超过了配置autovacuum_max_workers的数量,某些表就要等待空闲的worker。这个阶段就容易出现表的膨胀。

以前的PostgreSQL版本,一个数据库同一时间只会起一个worker进程,现在的版本已经没有这个限制了。

所以如果你的PostgreSQL集群有很多数据库的话,可能需要更多的worker进程来支撑。

另外需要注意一点,worker进程在工作时,每个worker最多会消耗的内存由以下参数决定:

#maintenance_work_mem = 64MB            # min 1MB 

#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem 

所以worker进程越多,内存需求量也越大。

2.4. 数据库中存在长SQL或带XID的长事务。

通过pg_stat_activity.backend_xidbackend_xmin来观察。

backend_xid表示已申请事务号的事务,例如有增删改,DLL等操作的事务。backend_xid从申请事务号开始持续到事务结束。

backend_xmin表示SQL执行时的snapshot,即可见的最大已提交事务。例如查询语句,查询游标。backend_xminSQL开始持续到SQL结束,如果是游标的话,持续到游标关闭。

PostgreSQL目前存在一个非常严重的缺陷,当数据库中存在未结束的SQL语句或者未结束的持有事务ID的事务,在此事务过程中,或在此SQL执行时间范围内产生垃圾的话,这些垃圾无法回收,导致数据库膨胀。

也即是判断当前数据库中backend_xidbackend_xmin最小的值,凡是超过这个最小值的事务产生的垃圾都不能回收。

后面通过测试来展示。

2.5. 开启了autovacuum_vacuum_cost_delay

在开启了autovacuum_vacuum_cost_delay后,会使用基于成本的垃圾回收,这个可以有利于降低VACUUM带来的IO影响,但是对于IO没有问题的系统,就没有必要开启autovacuum_vacuum_cost_delay,因为这会使得垃圾回收的时间变长。

autovacuum进程达到autovacuum_vacuum_cost_limit后,会延迟autovacuum_vacuum_cost_delay后继续。

限制计算方法由另外几个参数决定:

包括在SHARED BUFFER中命中的块,未命中的块,非脏块的额外成本。

对于IO没有问题的系统,不建议设置autovacuum_vacuum_cost_limit

2.6. autovacuum launcher process 唤醒时间太长

唤醒时间由参数autovacuum_naptime决定,autovacuum launcher进程负责告诉postmaster需要fork worker进程来进行垃圾回收,但是如果autovacuum launcher进程一直在睡觉的话,那完蛋了,有垃圾了它还在睡觉,那不就等着膨胀吗?

另外还有一个限制在代码中,也就是说不能小于MIN_AUTOVAC_SLEEPTIME 100毫秒:

2.7 批量删除或批量更新,

例如对于一个10GB的表,一条SQL或一个事务中删除或更新9GB的数据,这9GB的数据必须在事务结束后才能进行垃圾回收,无形中增加了膨胀的可能。

2.8 大量的非HOT更新,会导致索引膨胀,对于BTREE索引来说,整个索引页没有任何引用才能被回收利用,因此索引比较容易膨胀。


举报
分享

分享文章到朋友圈

分享文章到微博

找虫虫

发帖: 0粉丝: 0

级别 : 中级会员

发消息 + 关注

发表于2020年06月30日 07:47:04
直达本楼层的链接
沙发
显示全部楼层

感谢大神分享!

点赞 评论 引用 举报

carl@1234

发帖: 44粉丝: 2

级别 : 注册会员

发消息 + 关注

发表于2020年06月30日 07:56:20
直达本楼层的链接
板凳
显示全部楼层

学习了

点赞 评论 引用 举报

人生的旅途

发帖: 4粉丝: 0

级别 : 中级会员

发消息 + 关注

发表于2020年06月30日 08:02:28
直达本楼层的链接
地板
显示全部楼层

有用,感谢分享,赞!

点赞 评论 引用 举报

云天裕

发帖: 0粉丝: 0

级别 : 中级会员

发消息 + 关注

发表于2020年06月30日 08:21:10
直达本楼层的链接
5#
显示全部楼层

我去,这样也可以,收藏了

点赞 评论 引用 举报

编程小王子

发帖: 1粉丝: 0

级别 : 中级会员

发消息 + 关注

发表于2020年06月30日 08:58:44
直达本楼层的链接
6#
显示全部楼层

了解下。

点赞 评论 引用 举报

Ivan_2020

发帖: 68粉丝: 6

级别 : 版主

发消息 + 关注

发表于2020年06月30日 09:00:09
直达本楼层的链接
7#
显示全部楼层

感谢分享

点赞 评论 引用 举报

GoodStudy

发帖: 1粉丝: 0

级别 : 中级会员

发消息 + 关注

发表于2020年06月30日 09:40:52
直达本楼层的链接
8#
显示全部楼层

赞!干货,学习了!

点赞 评论 引用 举报

王建国

发帖: 1粉丝: 0

级别 : 中级会员

发消息 + 关注

发表于2020年07月01日 12:33:57
直达本楼层的链接
9#
显示全部楼层

厉害,学到了!

点赞 评论 引用 举报

AI从业者

发帖: 0粉丝: 0

级别 : 中级会员

发消息 + 关注

发表于2020年07月01日 13:21:05
直达本楼层的链接
10#
显示全部楼层

学习了~

点赞 评论 引用 举报

Richel

发帖: 0粉丝: 0

级别 : 中级会员

发消息 + 关注

发表于2020年07月01日 14:12:13
直达本楼层的链接
11#
显示全部楼层

三连,支持一下!

点赞 评论 引用 举报

游客

富文本
Markdown
您需要登录后才可以回帖 登录 | 立即注册