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

 

膨胀率的精确计算

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

#插入1000W数据
postgres=# insert into t select id,id from generate_series(1,10000000) as id;
INSERT 0 10000000
 
#表膨胀系数为0.097
postgres=# select *, 1.0 – tuple_len::numeric / table_len as bloat from pgstattuple(‘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
(1 row)
 
#占用54055个page
postgres=# select * from pg_relpages(‘t’);
 pg_relpages
————-
  54055
(1 row)
 
#删除数据
postgres=# delete from t where id<>10000000;
DELETE 9999999
 
#仍然占用54055个page
postgres=# select * from pg_relpages(‘t’);
 pg_relpages
————-
  54055
(1 row)
 
#膨胀率已经为0.999999
postgres=# select *, 1.0 – tuple_len::numeric / table_len as bloat from pgstattuple(‘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”: found 5372225 removable, 2 nonremovable row versions in 54055 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.89 s, system: 0.00 s, elapsed: 0.89 s.
INFO: analyzing “public.t”
INFO: “t”: scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
VACUUM

 

补充:pg索引膨胀问题—重建索引

问题:

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

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

解决方法:

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

方法一:停止应用(这个操作会锁表),重建索引(注:重建完索引名称不变)

sql:reindex index 索引名称

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

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

reindex table 表名

方法二:在线建新索引,再把旧索引删除

sql:根据不同索引采用不同的建索引命令,例如:

普通索引

create index concurrently idx_tbl_2 on tbl(id);
drop index idx_tbl_1;

 

唯一索引

create unique index concurrently user_info_username_key_1 on user_info(username);
begin;
alter table user_info drop constraint user_info_username_key;
alter table user_info add constraint user_info_username_key unique using index user_info_username_key_1;
end;

 

主键索引

create unique index concurrently user_info_pkey_1 on user_info(id);
begin;
alter table user_info drop constraint user_info_pkey;
alter table user_info add constraint user_info_pkey primary key using index user_info_pkey_1;
end;

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

文章

①本站所有CMS源码、杰奇CMS模板、PTCMS源码模板、YGBOOK源码模板、帝国CMS源码模板等仅用于学习和交流,勿用于商业。
②本站资源有安装及使用文档,安装使用请自行探索,如您对购买的程序或是模板无法胜任安装工作,请点击付费安装。
③本站资源来源网络或者用户投稿,切勿私自传播于网络,否则将追究法律责任。且仅供学习交流之用,如有侵权请联系删除。
④如果资源失效或下载链接错误请联系站长。
蓝大富博客 » PostgreSQL表膨胀监控案例(精确计算)

发表评论