postgresql：记录表膨胀引起的io问题的处理

1. io异常

iostat -x 1 20
Device            r/s     w/s     rkB/s     wkB/s   rrqm/s   wrqm/s  %rrqm  %wrqm r_await w_await aqu-sz rareq-sz wareq-sz  svctm  %util
loop0            0.00    0.00      0.00      0.00     0.00     0.00   0.00   0.00    0.00    0.00   0.00     0.00     0.00   0.00   0.00
loop1            0.00    0.00      0.00      0.00     0.00     0.00   0.00   0.00    0.00    0.00   0.00     0.00     0.00   0.00   0.00
loop2            0.00    0.00      0.00      0.00     0.00     0.00   0.00   0.00    0.00    0.00   0.00     0.00     0.00   0.00   0.00
sdb            526.00   62.00   8896.00    976.00     2.00     0.00   0.38   0.00    5.18    5.68   3.04    16.91    15.74   1.70 100.00
sda              0.00    3.00      0.00     16.00     0.00     1.00   0.00  25.00    0.00    0.00   0.00     0.00     5.33   0.00   0.00

2.查看profile报告

2.1 生成事发时间段的pgprofile

参阅:使用 pg_profile 在 Postgres 中生成性能报告

2.2 查看报告

这里主要查看产生physical read最多的sql:
a8da96b7d2的read占用了66.78%,且命中率仅12.33%

点击a8da96b7d2可获得sql内容：

select l.factoryno, l.buildno, l.opgroupno, l.arbpl, a.prodbatch as podr_no, a.zzmdmark as artic_no, a.mold_no as mold_no, l.deptno, a.stepname, a.part, a.componentdescen, a."action", a.sizeno as sizeno, substring(max(a.createon),$5,$6) as in_time, max(a.createon) as createon, coalesce(b.targetqty,$7) as t_qty, coalesce(sum(a.primaryquantity),$8) as a_qty from (select a.*, mda.mold_no from pp_outboundshoeprod a inner join (select distinct a.matnr, a.zzgendr, a.mold_no from pp_mda_data a) mda on substring(a.productname,$9,$10) = mda.matnr) a left join pp_mes_outboundaopgroupctrl l on a.opgroup = l.opgroupno left join pp_daily_target b on a.werks = b.fact_id and a.opgroup = b.dept_id and a.mold_no = b.mold_code and a.stepname = b.stepname and a.create_date = b.targetdate where a.vbeln <> $11 and a.stepname in ($12,$13,$14,$15,$16) and a."action" = $17 and a.create_date = $1 and a.stepname = $2 and l.factoryno = $3 and l.buildno = $4 group by l.factoryno, l.buildno, l.opgroupno, l.arbpl, a.prodbatch, a.zzmdmark, a.mold_no, l.deptno, a.stepname, a.part, a.componentdescen, a."action", a.sizeno, b.targetqty order by l.deptno

3.检查table是否膨胀

查看每個頁的freespace率，作為是否執行vacuum full的依據

select
schema,relname,count(1) as num_pages,
pg_relation_size(schema||'.'||relname::text) as bytes_in_table,
pg_size_pretty(pg_relation_size(schema||'.'||relname::text)) as mbytes_in_table,
round(avg(avail::bigint),2) as "avg.freespace_size",
round(avg(avail::bigint)/8192,2) as "av.freespace_ratio"
from
(
select 
	relnamespace::regnamespace as schema,
	relname,
	split_part(substring(pg_freespace(relnamespace::regnamespace::text||'.'||relname::text)::text,'\d+,\d+'),',',1) blkno,
	split_part(substring(pg_freespace(relnamespace::regnamespace::text||'.'||relname::text)::text,'\d+,\d+'),',',2) avail
from pg_class where relkind='r' and relnamespace::regnamespace::text='pcnidss' --注意替换为自己的schema name
	) freespace
	group by schema,relname
	order by  bytes_in_table desc

output:

4.执行vacuum full

postgres=#vacuum full pcnidss.pp_mda_data

再次檢查，size已经由原来的2994M下降到771M

select pg_size_pretty(pg_relation_size('pcnidss.pp_mda_data')

5.总结

1.pg中，dml操作会产生所谓的dead rows，平常的auto vacuum仅仅将dead rows删除，但是不会释放这部分空间，这将会造成无效scan,特别在full table scan时，会scan所有的空间，包含这部分已删除但未release的空间
2.要真正的释放已删除空间，必须作vacuum full，但这不是一个自动的作业（在pg10版本），另外需要注意的是vacuum full会消耗双倍的空间，实际上它是将表内的行insert到一个临时表，然后将原表清空后再insert回去，所以做这个动作时要查看表所在的磁盘是否有足够的空间