pgsql单表查询超时问题(vacuum)

admin 发布时间:2020-05-28 分类:分享 阅读:841次 1 条评论

今天生产数据库遇到一个严重的问题,某一个PostgreSQL的业务表查询异常缓慢,导致该模块业务查询超时。甚至slect count(id) from table查询整表数据数量的时候都需要执行超过60秒。

最初怀疑是索引的问题,删除索引或者重建索引都无法解决问题。

后面查询各表的碎片化问题,查询到该表异常(这个命令是查询按照最老的XID排序,查看大于1G而且是排名前20的表):

SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 20;

才了解到PGSQL(未开启定期维护任务)需要定期进行垃圾回收,原因是当系统有更新操作(包括INSERT VALUES、UPDATE、DELETE、ALTER TABLE ADD COLUMN等),会在系统表和被更新的数据表中留存不再使用的垃圾数据,造成系统性能下降,并占用大量磁盘空间,因此需要定期进行垃圾回收。


vacuum的效果:

    1释放,再利用 更新/删除的行所占据的磁盘空间.

    2更新POSTGRESQL查询计划中使用的统计数据

    3防止因事务ID的重置而使非常老的数据丢失。

第一点的原因是PostgreSQL数据的插入,更新,删除操作并不是真正放到数据库空间.如果不定期释放空间的话,由于数据太多,查询速度会巨降.

第二点的原因是PostgreSQL在做查询处理的时候,为了是查询速度提高,会根据统计数据来确定执行计划.如果不及时更新的话,查询的效果可能不如预期.

第三点的原因是PostgreSQL中每一个事务都会产生一个事务ID,但这个数字是有上限的. 当事务ID达到最大值后,会重新从最小值开始循环.这样如果不及时把以前的数据释放掉的话,原来的老数据会因为事务ID的丢失而丢失掉.

执行释放解决(锁表,效率很低):

vacuum full <表名称>;


已有1条留言
  • gsvr
    发布于 2020-08-17 17:39:24  回复该评论
  • 空包单号网全国地址任意发、一单一用快递单号**

发表评论:

◎欢迎您的参与讨论。