PostgreSQL表膨胀监控案例(精确计算)

膨胀率的精确计算

PostgreSQL自带了pgstattuple模块，可用于精确计算表的膨胀率。譬如这里的tuple_percent字段就是元组实际字节占关系总大小的百分比，用1减去该值即为膨胀率。

#插入1000W数据
postgres=#insertintotselectid,idfromgenerate_series(1,10000000)asid;
INSERT010000000

#表膨胀系数为0.097
postgres=#select*,1.0-tuple_len::numeric/table_lenasbloatfrompgstattuple('t');
table_len|tuple_count|tuple_len|tuple_percent|dead_tuple_count|dead_tuple_len|dead_tuple_percent|free_space|free_percent|bloat
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+------------------------
442818560|10000001|400000040|90.33|0|0|0|1304976|0.29|0.09669540499838127833
(1row)

#占用54055个page
postgres=#select*frompg_relpages('t');
pg_relpages
-------------
54055
(1row)

#删除数据
postgres=#deletefromtwhereid<>10000000;
DELETE9999999

#仍然占用54055个page
postgres=#select*frompg_relpages('t');
pg_relpages
-------------
54055
(1row)

#膨胀率已经为0.999999
postgres=#select*,1.0-tuple_len::numeric/table_lenasbloatfrompgstattuple('t');
table_len|tuple_count|tuple_len|tuple_percent|dead_tuple_count|dead_tuple_len|dead_tuple_percent|free_space|free_percent|bloat
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------+----------------------------
442818560|2|80|0|9999999|399999960|90.33|1304976|0.29|0.999999819339099065766349

#vacuum表
postgres=#vacuum(verbose,full,analyze)t;
INFO:vacuuming"public.t"
INFO:"t":found5372225removable,2nonremovablerowversionsin54055pages
DETAIL:0deadrowversionscannotberemovedyet.
CPU:user:0.89s,system:0.00s,elapsed:0.89s.
INFO:analyzing"public.t"
INFO:"t":scanned1of1pages,containing2liverowsand0deadrows;2rowsinsample,2estimatedtotalrows
VACUUM

补充：pg索引膨胀问题---重建索引

问题：

发现数据库中很多表的索引大小超过数据大小。经检查，生产CA、CZ、MU、HU、PSG、RIUE库都存在这个现象。

原因：据运行同事介绍索引膨胀问题无法避免，频繁更新就会带来这个问题。

解决方法：

对于大的索引可以采用重建的方式解决。以下两种方法推荐第一种。

方法一：停止应用（这个操作会锁表），重建索引（注：重建完索引名称不变）

sql：reindexindex索引名称

时间：速度较快。2G大小的表，基本上1分钟左右可以建完索引。

还可以针对表重建索引，这个操作会加排他锁：

reindextable表名

方法二：在线建新索引，再把旧索引删除

sql：根据不同索引采用不同的建索引命令，例如：

普通索引

createindexconcurrentlyidx_tbl_2ontbl(id);
dropindexidx_tbl_1;

唯一索引

createuniqueindexconcurrentlyuser_info_username_key_1onuser_info(username);
begin;
altertableuser_infodropconstraintuser_info_username_key;
altertableuser_infoaddconstraintuser_info_username_keyuniqueusingindexuser_info_username_key_1;
end;

主键索引

createuniqueindexconcurrentlyuser_info_pkey_1onuser_info(id);
begin;
altertableuser_infodropconstraintuser_info_pkey;
altertableuser_infoaddconstraintuser_info_pkeyprimarykeyusingindexuser_info_pkey_1;
end;

时间：不停应用的话，业务忙的时候可能会非常长的时间。

Tags：

转载：感谢您对杨青青个人博客网站平台的认可，以及对我们原创作品以及文章的青睐，非常欢迎各位朋友分享到个人站长或者朋友圈，但转载请说明文章出处“来源杨青个人博客”。http://www.yangqq.com/jstt/web/486.html

很赞哦！ ()