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

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

2021-03-31 17:43磨叽SP PostgreSQL

这篇文章主要介绍了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：根据不同索引采用不同的建索引命令，例如：

普通索引

1 2	`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;