【数据库测试】PG数据库表膨胀及优化浅析


前言

\qquad 本文对Postgresql数据库关系表膨胀、vacuum、autovacuum以及怎么监控和处理表膨胀做简单说明。


一、表膨胀概述

  • 表膨胀如何产生
    表的数据和索引占用文件系统的空间,在有效数据未发生大的变化情况下,占用空间不断增大,造成磁盘空间浪费。实际上MB的数据可能变为GB级别。

  • 表膨胀危害
    1) 空间持续上涨
    空间无法回收,需要执行高额代价vaccum full,但是vacuum full会对表加8级锁AccessExclusiveLock(访问排它锁),阻塞对象访问
    2)扫描效率变低
    所有记录的都是dead状态(100条数据99条都是死元组),顺序扫描会扫描所有对象老版本,直到vaccum将dead记录删除

  • 为什么会表膨胀
    Postgresql数据库MVCC机制原因,写新数据时,旧数据不删除,而是把新数据插入,将旧数据标记未无效。新老数据存放在一起,在被清理前,会一直占用空间,导致膨胀。PG的MVCC优缺点如下:
    优点:快速回滚
    缺点:旧版本数据需要清理,空间持续上涨,存储没有被有效利用。

二、表膨胀产生的原因

\qquad 如果系统中含有很久之前开启而未提交的事务,并且这个事务由于执行过更新,创建了事务ID。那么计算出的OldestXmin会非常小,vaccum做判断时,结果通常未true,即HEAPTUPLE_RECENTLY_DEAD(元组是死元组,但不能删除)。这样就会保留旧版本tuple,导致回收无法完成,引发表膨胀。
在这里插入图片描述

元组状态(vaccumlazy.c)

TransactionIdPrecedesOrEquals(TransactionId id1,TransactionId id1)
{
int32 diff;
if (!TransactionIdIsNormal(id1) || !TransactionIdIsNormal(id2))
	return (id1<=id2);
	diff = (int32)(id1-id2);
	return(diff<=0)
}

//TransactionIdPrecedesOrEquals用于判断两个事务的大小。xmax为删除、更新时的事务ID(删除版本号),OldestXmin为活动事务中最小的事务ID。如果OldestXmin<xmax,则返回,意味着保留tuple,不去进行删除。
HTSV_Result
HeapTupleSatisfiesVacuum(Heap Tuple htup,TransacitonId OldestXmin, Buffer buffer)
...
  if (!TransactionPrecedes(xmax,OldestXmin))
  return HEAPTUPLE_RECENTLY_DEAD;
...

typedef enum
{
HEAPTUPLE_DEAD						/*元组是死元组和可删除的*/
HEAPTUPLE_LIVE						/*元组处于活动状态(已提交、未删除)*/
HEAPTUPLE_RECENTLY_DEAD				/*元组是死元组,但不能删除*/
HEAPTUPLE_INSERT_IN_PROGRESS		/*插入xact仍在进行中(状态正被更新到事务文件中)*/
HEAPTUPLE_DELETE_IN_PROGRESS		/*正在删除xact(事务状态)*/
}HTSV_Result

表膨胀实验例子如下:

--步骤1:session1创建测试数据
create table test(id int,info text);
insert into test(id, info) values(1,'test');
insert into test(id, info) values(2,'aaa');
--开启事务
begin;
   select pg_backend_pid();
   selext txid_current();   --保持事务不提交
--步骤2:session2对测试表进行持续更新
--查询表大小
\dt+ test
--0.01s持续更新test表
update test set info='a1b2';\watch 0.01
--步骤3:session3查询test表所占页面空间
select ctid,xmin,xmax,cmin,cmax,id from test;
ctid  |	xmin | xmax | cmin | cmax | id
------------------------------------------
(0,1) |	738	|0		|5	   |5	  |1
(0,2) | 738	|0		|6	   |6	  |2

--步骤4:session3清理test表
vaccum VERBOSE test;

--步骤5:session1结束事务
commit;
--步骤6:查询表大小,清理表
\dt+ test
vacuum full test
\dt+ test --表恢复原始大小

三、vacuum

\qquad 为了对旧数据进行清理,引入了vaccum,vacuum分为普通vaccum和vacuum full。vaccum full会物理删除dead tuple,并释放空间重新交还操作系统。在vaccum full后,表的大小会减小为实际的空间大小。vaccum主要作用如下:

  • 清除update或delete操作后留下的“死元组”
  • 跟踪表块中可用空间,更新空闲空间映射表FSM(Free Space Map)
  • 更新visibility Map,index only scan以及后续vaccum都会利用
  • “冻结”表中的行,防止事务ID回卷
  • 配合Analyze,定期更新统计信息

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

四、autovacuum

1.业务逻辑

\qquad autovacuum是Postgresql数据库后台自动触发vaccum的一套逻辑,达到一定条件后,自动触发vaccum。后台常驻线程AVClauncher,这个线程每隔一定的间隔会同PostMaster线程唤起一些AVCWorker线程,由AVCWorker进行实际的vaccum与analyz任务。
\qquad 涉及到的时间间隔,唤起worker的数量等,都有相关的GUC参数配置。autovacuum通过后台进程定期扫描数据库中的表,可以有效避免表膨胀问题。
在这里插入图片描述
在这里插入图片描述

2.触发条件

\qquad autovacuum除了完成cleanup dead tuples,同时它还需要考虑如何减少工作期间对系统的影响,那么什么时间和什么频率autovacuum来进行工作需要考虑这两个因素。
\qquad 如果查询出dead tuple数据量特别大,说明autovacuum做的不够多(也可能有长事务导致)。autovacuum的频率主要由两个参数控制,autovacuum_vacuum_scale_factorautovacuum_vacuum_threshold。如果表符合这两个参数条件,被autovacuum扫描到就开始整理,计算公式如下:

修改表的行数+修改表的百分比*总的表行数 < 实际修改表的行数
--查询用户表自动回收和统计信息更新情况
select schemaname,relname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_autovacuum,vacuum_count,autovacuum_count,analyze_count,autoanalyze_count
from sys_stat_all_tables
where relname like '%inst_%' or relname like '%sql_';

在这里插入图片描述

如示例所示,表目前行数为:12706180,autovacuum_vacuum_scale_factor=0.2,autovacuum_vacuum_threshold=50,则触发门限为:

12706180*0.2 + 50 = 2541286 //当表修改行数超过2541286行,才能触发autovacuum

在这里插入图片描述

表随着数据行数越来越多,则会触发autovacuum越来越少,最终导致表膨胀

3.优化策略

  • 调整autovacuum_max_workers从默认3调整为6或更多
  • 调整autovacuum_vacuum_cost_limit,以3个为200默认值,调整为6个worker时600
  • 调整autovacuum_vacuum_cost_delay从2ms调整为1ms,减少delay时间
  • 调整autovacuum_mem和maintenance_work_mem,减少刷脏频率
类型参数名参数作用说明
autovaccumautovacuum_max_workersautovaccum周期内冰箱workers数量,与数据库中表的数量和表大小由关适当调整该参数,可以避免在一个周期内,部分表无法被轮询到workers
autovaccumautovacuum_vacuum_cost_limit控制预期autovacuum的陈本,达到该陈本后,就停止autovaccum工作,默认200该值与autovaccum的worker数量相关,如果有3个worker需要将该值除以3
此参数可以针对单表进行设置
autovaccumautovacuum_vacuum_cost_delay控制autovaccum不工作需要等待多长时间然后再次工作,默认2s此参数可以针对单表进行设置
vacuumvacuum_cost_delay控制vaccum不工作需要等待多长时间然后再次工作,默认2ms
vacuumvacuum_cost_page_hint从shared_buffer中找到数据的成本,默认为1
vacuumvacuum_cost_page_miss从shared_buffer中找不到数据的情况下,需要从磁盘读取数据的成本,默认为10
vacuumvacuum_cost_page_dirty从shared_buffer中找不到数据的情况下,也无法将数据加载到share buffer中。需要将脏页刷到磁盘,再将磁盘信息读入到share buffer成本,默认为20可以调整autovacuum_mem和maintenance_work_mem减少刷脏频率
vacuumvacuum_cost_limit控制预期vacuum的成本,达到该成本后,就停止vacuum,默认200
vacuumvacuum_cost_delay控制vaccum不工作需要等待多长时间然后再次工作,默认2ms

在这里插入图片描述

4.autovacuum监控

  • 死元组监控
    监控表中dead tuple数量,以及占整体表中行的百分比和最近一次表进行autovacuum的时间,对长时间未进行清理回收的表需要进行告警。

  • autovacuum进程监控
    通过如下语句可以了解数据库系统中是否有正在工作的autovacuum进程,以及进程状态

select psa.pid,granted,now() - psa.xact_start as running_time,psa.state,psa.usename,query
from pg_locks pl
left join
pg_stat_activity psa on pl.pid=psa.pid
where locktype='relation' and query like '%vacuum%' and query not like '%select%';

在这里插入图片描述
单表死元组及空间占用查询

create extension kbstattuple ;
select * from pgstattuple('mixture');

在这里插入图片描述
针对索引也提供了对应的函数进行检查

select * from pgstatindex('idx_1');

在这里插入图片描述

  • 大表监控
    通过获取数据存储容量最大的表排序,来获得需要监控表的列表
select relname as tab_name,pg_size_pretty(pg_table_size(C.oid)) as table_size
from
pg_class C
left join pg_namespace N on (N.oid=C.relnamespace)
where N.nspname not in ('pg_catalog','information_schema') and nspname !~ '^pg_toast' and relkind in ('r')
order by pg_table_size(C.oid)
desc limit 10;

在这里插入图片描述

五、表膨胀优化建议

应尽量避免使用大批量更新、删除操作(长事务),可以切分为多个事务进行。

  • 一定要开启autovaccum:autovacuum=on;track_counts=on
  • 提高系统IO能力
  • 设置idle_in_transaction_session_timeout,控制长事务活动时间
  • 对大表(超过5~10GB),建议使用分区,可以加快vacuum速度。对表单独设置相关参数提高回收效率,减小对系统IO影响
  • 适当调整vaccum与autovacuum相关代价参数

文章参考:http://www.interdb.jp/pg/pgsql06.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值