PostgreSQL数据库会出现表空间彭涨的原因和处理方法
一、通过SQL查表的空间大小
SELECT relname
, age( relfrozenxid) AS xid_age
, pg_size_pretty( pg_table_size( OID) ) AS table_size
, reltuples AS rowcounts
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_table_size( OID) DESC ;
二、回收膨胀的空间
2.1、清除全库的碎片
postgres@x - w- test- db001:~ $ vacuumdb
2.2、清除表的碎片(耗时非常长慎用!)
vacuum full TABLE_NAME;
2.3、不回收空间只标记
vacuum TABLE_NAME;
三、避免表膨胀的方法
3.1、尽早的、及时的提交事务
3.2、设计应用时,要使事务尽量短小
3.3、注意配置与应用规模相适应的硬件资源(IO能力、CPU、内存等),并调教好数据库,使其性能最优,避免有些事务因为资源或性能问题长时间无法完成
3.4、提交autovacuum,使其能按合理的周期运行。这方面的内容,我们今后专门介绍
3.5、定期监控系统中是否有长事务,可以使用下面的SQL监控持续时间超过一定时间的事务
3.5.1、监控持续时间超过一定时间的事务
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) ) > 7000