膨脹率的精確計(jì)算
PostgreSQL自帶了pgstattuple模塊,可用于精確計(jì)算表的膨脹率。譬如這里的tuple_percent字段就是元組實(shí)際字節(jié)占關(guān)系總大小的百分比,用1減去該值即為膨脹率。
#插入1000W數(shù)據(jù)
postgres=# insert into t select id,id from generate_series(1,10000000) as id;
INSERT 0 10000000
#表膨脹系數(shù)為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個(gè)page
postgres=# select * from pg_relpages('t');
pg_relpages
-------------
54055
(1 row)
#刪除數(shù)據(jù)
postgres=# delete from t where id<>10000000;
DELETE 9999999
#仍然占用54055個(gè)page
postgres=# select * from pg_relpages('t');
pg_relpages
-------------
54055
(1 row)
#膨脹率已經(jīng)為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
補(bǔ)充:pg索引膨脹問題---重建索引
問題:
發(fā)現(xiàn)數(shù)據(jù)庫中很多表的索引大小超過數(shù)據(jù)大小。經(jīng)檢查,生產(chǎn)CA、CZ、MU、HU、PSG、RIUE庫都存在這個(gè)現(xiàn)象。
原因:據(jù)運(yùn)行同事介紹索引膨脹問題無法避免,頻繁更新就會帶來這個(gè)問題。
解決方法:
對于大的索引可以采用重建的方式解決。以下兩種方法推薦第一種。
方法一:停止應(yīng)用(這個(gè)操作會鎖表),重建索引(注:重建完索引名稱不變)
sql:reindex index 索引名稱
時(shí)間:速度較快。2G大小的表,基本上1分鐘左右可以建完索引。
還可以針對表重建索引,這個(gè)操作會加排他鎖 :
reindex table 表名
方法二:在線建新索引,再把舊索引刪除
sql:根據(jù)不同索引采用不同的建索引命令,例如:
普通索引
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;
時(shí)間:不停應(yīng)用的話,業(yè)務(wù)忙的時(shí)候可能會非常長的時(shí)間。
文章來源:腳本之家
來源地址:https://www.jb51.net/article/204334.htm
申請創(chuàng)業(yè)報(bào)道,分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處,共同探討創(chuàng)業(yè)新機(jī)遇!