postgresql之vacuum

作者：瀚高PG实验室 （Highgo PG Lab）- 波罗

数据库总是不断地在执行删除，更新等操作。良好的空间管理非常重要，能够对性能带来大幅提高。

在postgresql中用于维护数据库磁盘空间的工具是VACUUM，其重要的作用是删除那些已经标示为删除的数据并释放空间。

定期vacuum是一个重要的工作，原因在于以下3点：

1. 释放,再利用 更新/删除的行所占据的磁盘空间；

2. 更新PostgreSQL查询计划中使用的统计数据；

3. 防止因事务ID的重置而使非常老的数据丢失。

VACUUM语法结构：

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

postgresql中执行delete操作后，表中的记录只是被标示为删除状态，并没有释放空间，在以后的update或insert操作中该部分的空间是不能够被重用的。

经过vacuum清理后，空间才能得到释放。

可惜的是vacuum工具不能够对相应的索引进行清理，唯一的办法就是手动去重建相应索引

Full Vacuum

full vacuum与单纯的vacuum还是有很大的区别的。vacuum只是将删除状态的空间释放掉，转换到能够重新使用的状态，但是对于系统来说该数据块的空闲空间并没有反应到系统的元数据中。类似oracle中高水位标记并没有下降。Full vacuum将会使空间释放的信息表现在系统级别，其实质是将当前删除记录后面的数据进行移动，使得整体的记录连贯起来，降低了“高水位标记”。

Vacuum analyze

analyze的功能是更新统计信息，使得优化器能够选择更好的方案执行sql。

oracle中同样作用也相同，目前更多的使用也有analyze，的是dbms_stats包。统计信息收集和更新对于系统性能来说非常重要，与oracle维护类似，通常可以通过采用手动或者定制任务的方式。也有不同，oracle在进行imp后自动的对相应数据对象进行统计信息的收集和更新，而postgresql的恢复过程还没有集成到里面，需要手动去执行。

具体如何如何执行！

autovacuum相关参数

autovacuum：默认为on，表示是否开起autovacuum。默认开起。特别的，当需要冻结xid时，尽管此值为off，PG也会进行vacuum。 

autovacuum_naptime：下一次vacuum的时间，默认10min。 这个naptime会被vacuum launcher分配到每个DB上。autovacuum_naptime/num of db。 

log_autovacuum_min_duration：记录autovacuum动作到日志文件，当vacuum动作超过此值时。 “-1”表示不记录。“0”表示每次都记录。 

autovacuum_max_workers：最大同时运行的worker数量，不包含launcher本身。 

autovacuum_work_mem：每个worker可使用的最大内存数。

autovacuum_vacuum_threshold:默认50。与autovacuum_vacuum_scale_factor配合使用， autovacuum_vacuum_scale_factor默认值为20%。当update,delete的tuples数量超过autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold时，进行vacuum。如果要使vacuum工作勤奋点，则将此值改小。 

autovacuum_analyze_threshold:默认50。与autovacuum_analyze_scale_factor配合使用, autovacuum_analyze_scale_factor默认10%。当update,insert,delete的tuples数量超过autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold时，进行analyze。 

autovacuum_freeze_max_age和autovacuum_multixact_freeze_max_age：前面一个200 million,后面一个400 million。离下一次进行xid冻结的最大事务数。 

autovacuum_vacuum_cost_delay：如果为-1，取vacuum_cost_delay值。 

autovacuum_vacuum_cost_limit：如果为-1，到vacuum_cost_limit的值，这个值是所有worker的累加值。

更改系统autovacuum相关参数

ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.03;

ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.03;

ALTER SYSTEM SET autovacuum_vacuum_threshold = 30;

ALTER SYSTEM SET autovacuum_analyze_threshold = 30;

select pg_reload_conf ();

更改单表autovacuum相关参数

alter table tccpu_tempre set (autovacuum_vacuum_scale_factor=.03);

查看单表参数

SELECT n.nspname, c.relname, pg_catalog.array_to_string(c.reloptions || array( select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x),', ') as relopts 

FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) JOIN pg_namespace n ON c.relnamespace = n.oid 

WHERE c.relkind = 'r' AND nspname NOT IN ('pg_catalog', 'information_schema') 

and pg_catalog.array_to_string(c.reloptions || array( select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x),', ') <>'';