说米网

linux服务器系统胀大率的准确核算

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;

时刻：不断使用的话，事务忙的时分可能会十分长的时刻。