postgres 表膨胀处理2（转）

一，关于vacuum

进行delete操作之后，表和索引的大小没有发生变化

vacuum 表名字 不能回收空间,运行时可以正常访问数据表（共享锁）。执行效率高 作用是把表中的dead tuples进行删除标记变成可以使用的状态，并没有真正的物理删除，并且会更新统计信息和相关系统表

vacuum full 表名字 可以回收空间，运行时不允许访问数据表（排他锁）。执行效率低 作用是物理删除dead tuples，并且把释放的空间重新交给操作系统。

本质时生成一个新的数据文件，然后把原来的表的live tuples 存放到新的数据文件中

autovacuum进程　只是个监控作用是用来启动自动清理进程的，autovacuum然后autovacuum worker然后vacuum+analyze，可以同时启动多个

表中不能被回收的dead tuple在索引页里是作为正常tuple而不是dead tuple记录的。考虑到这一点，索引的实际膨胀要乘以对应表的膨胀率

二，表膨胀的原因

1 autovacuum清理速度赶不上dead元组产生速度

2 由于以下因素导致dead元组无法被回收

◦ 主库或备库存在长事务（长时间的更新操作或者只读操作都会造成表膨胀)

◦ 主库或备库存在未处理的未决事务

◦ 主库或备库存在断开的复制槽

3 表膨胀和索引膨胀的对比

三，表膨胀的预防

１适当调整autovacuum参数，使得事务或者表，索引等能得到相对及时的清理

select *
from pg_settings ps
where 1=1
and lower(ps.name) like '%autovacuum%'
 order by ps.name;

比如：autovacuum_vacuum_cost_limit参数

２关注长时间运行的事务

select extract(epoch FROM (clock_timestamp() - xact_start )) as longtrans,  ----xact_start 这个进程的当前事务被启动的时间
extract(epoch FROM (clock_timestamp() - query_start )) as longquery  ----query_start当前活动查询被开始的时间
from pg_stat_activity  -----pg_stat_activity视图将为每一个服务器进程有一行，显示与该进程的当前活动相关的信息
 where 1=1　and state <> 'idle';

或者

select * from pg_stat_activity where state<>'idle' and pg_backend_pid() != pid and (backend_xid is not null or backend_xmin is not null ) and extract(epoch from (now() - xact_start))  > <时间阈值，单位秒> ;

３，关注表的膨胀率

select schemaname||'.'||relname,
n_dead_tup,  ----n_dead_tup的数据量是未回收的空间
n_live_tup,------n_live_tup的数量是当前表的数据量
coalesce(round(n_dead_tup * 100 / (case when n_live_tup + n_dead_tup = 0 then null else n_live_tup + n_dead_tup end ),2),0.00) as dead_tup_ratio   ----dead_tup占所有有数据的行的百分比
from pg_stat_all_tables
where 1=1 
and n_dead_tup >= 10000
order by dead_tup_ratio desc
 limit 10;

４这些表要警惕，进行清理

SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size
FROM pg_class WHERE relkind = 'r' and pg_table_size(oid) > 1073741824 ORDER BY age(relfrozenxid) DESC LIMIT 20;

四，查看清理后的效果

语句一:表及的大小

select pt.schemaname||'.'||pt.tablename as  table_name,  ----表名字
pg_relation_filepath(pt.schemaname||'.'||pt.tablename) as table_path,   ----指定表的文件路径名 
pg_table_size(pt.schemaname||'.'||pt.tablename) as table_size,  -----指定表的大小（不包括索引）
pg_relation_size(pt.schemaname||'.'||pt.tablename) as relation_size,  ----指定表使用的磁盘空间（包括索引） pg_relation_size不加参数，默认就是查的表的数据文件大小，单位字节
pg_total_relation_size(pt.schemaname||'.'||pt.tablename) as totalrelation_size, ---指定表所用的总磁盘空间，包括所有的索引和TOAST数据
pi.schemaname||'.'||pi.indexname as index_name,  ---指定索引的名称
pg_relation_filepath(pi.schemaname||'.'||pi.indexname) as index_path,  ----指定索引的文件路径名 
pg_relation_size(pi.schemaname||'.'||pi.indexname) as index_size,--指定的单个索引
pg_indexes_size(pi.schemaname||'.'||pi.tablename) as index_total_size,--表上的所有索引
pi.indexdef   ---索引的定义
from pg_tables pt
left outer join pg_indexes pi 
on pt.schemaname||'.'||pt.tablename = pi.schemaname||'.'||pi.tablename
where 1=1
and pt.schemaname='public'
  and pt.tablename='tmp_t0';

语句二:看表的统计信息

select * from pg_stat_all_tables where relname='tmp_t0';

五，一些额外的了解

psql的\watch　１　作用反复查看语句执行结果（每一秒钟一次）；

fsm结尾的文件是主文件的空闲空间映射文件－－－只跟踪可用空间的页面

vm结尾的文件是主文件的可见性映射文件－－－只跟踪包含冻结元组（死元组）的页面