postgresql vacuum 浅谈

数据库总是不断地在执行删除，更新等操作。良好的空间管理非常重要，能够对性能带来大幅提高。在postgresql中用于维护数据库磁盘空间的工具是VACUUM，其重要的作用是删除那些已经标示为删除的数据并释放空间。
VACUUM语法结构：

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

Full Vacuum
full vacuum与单纯的vacuum还是有很大的区别的。vacuum只是将删除状态的空间释放掉，转换到能够重新使用的状态，但是对于系统来说该数据块的空闲空间并没有反应到系统的元数据中。类似oracle中高水位标记并没有下降。Full vacuum将会使空间释放的信息表现在系统级别，其实质是将当前删除记录后面的数据进行移动，使得整体的记录连贯起来，降低了“高水位标记”。
Vacuum analyze
analyze的功能是更新统计信息，使得优化器能够选择更好的方案执行sql。oracle中同样也有analyze，作用也相同，目前更多的使用的是dbms_stats包。统计信息收集和更新对于系统性能来说非常重要，与oracle维护类似，通常可以通过采用手动或者定制任务的方式。也有不同，oracle在进行imp后自动的对相应数据对象进行统计信息的收集和更新，而postgresql的恢复过程还没有集成到里面，需要手动去执行。
自动vacuum配置
自动vacuum的执行直接由autovacuum参数值决定，默认值是on。

Vacuumming Basics

PG不得不对每张表进行 Vacuum 命令，原因如下：

1、为了回收和再利用通过更新或者删除行所占用的磁盘空间

2、为了更新被PG查询计划所使用的数据分析

3、为了更新只读索引扫描的可见的集合

4、避免由于事务ID或者混合事务ID丢失历史数据

由于这些原因，在进行频繁的 VACUUM 操作时进行规定：

标准 VACUUM

进行回收时，生产环境不影响数据库库的正常使用(SELECT、INSERT、UPDATE、DELETE),

并行使用，清理时不允许对表结构进行修改（ALTER TABLE）推荐使用该方案

VACUUM FULL

a、可以回收大量空间，但是比标准回收执行慢

b、运行时需要锁表

VACUUM 运行会导致读写性能比较差，所以需要调整一些参数降低影响

temp_file_limit = -1 #默认-1表示不限制每个进程可使用的最大临时文件限制，单位kb

#max_files_per_process = 1000 #每个子进程允许同时打开文件的最大数量

在执行 VACUUM 和 ANYLYZE 期间，系统会维护一个用于估算各种I/O操作所消耗的内部计数器,当该值达到vacuum_cost_limit的值时，该进程会休眠 vacuum_cost_delay 指

定的时间，并重置计数器的值，继续运行 VACUM 或者 ANYLYZE 操作

vacuum_cost_limit = 200

vacuum_cost_delay = 0 # 单位微秒，默认为 0 没有开启

该参数 vacuum_cost_delay 主要用于并发时降低I/O的影响,推荐为10

vacuum_cost_page_hit = 1 # 代表从缓存池查找共享的hash table并扫描该`页`的内容 #的估计值

vacuum_cost_page_miss = 10 # 0-10000 credits

vacuum_cost_page_dirty = 20

NOTE

当一张表中包含了大量数据时，同时进行删除或者更新操作时，VACUUM 并不是最好的方案,如果有该情况，则应该使用 VACUU FULL ,当执行 ALTER TABLE 时，会重新 COPY整

个表和重新构建索引，会进行执行锁，临时占用和原始表大小的磁盘空间，直到新数据COPY完成。

升级执行计划

执行计划通过自己或者 VACUUM调用命令 ANALYZE 收集统计，

创建表达式索引能够提高查询执行计划

default_statistics_target = 100 #提高查询的析计划

以下是无vacuum,vacuum和vacuum full各种场景的区别：

删除大量数据之后：

1 只是将删除数据的状态置为已删除，该空间不能记录被重新使用。

2 如果删除的记录位于表的末端，其所占用的空间将会被物理释放并归还操作系统。如果不是末端数据，该命令会将指定表或索引中被删除数据所占用空间重新置为可用状态，那么在今后有新数据插入时，将优先使用该空间，直到所有被重用的空间用完时，再考虑使用新增的磁盘页面。

3 不论被删除的数据是否处于数据表的末端，这些数据所占用的空间都将被物理的释放并归还于操作系统。之后再有新数据插入时，将分配新的磁盘页面以供使用。

执行效率：

1 无法评论

2 由于只是状态置为删除，因此效率较高。

3 该命令会为指定的表或索引重新生成一个数据文件，并将原有文件中可用的数据导入到新文件中，之后再删除原来的数据文件。因此在导入过程中，要求当前磁盘有更多的空间可用于此操作，该命令的执行效率相对较低。

被删除的数据所占用的物理空间是否被重新规划给操作系统：

1 不会

2 不会

3 会

在执行VACUUM命令时，是否可以并发执行针对该表的其他操作：

1 是

2 由于该操作是共享锁，因此可以与其他dml操作并行进行

3 由于该操作需要在指定的表上应用排它锁，因此在执行该操作期间，任何基于该表的操作都将被挂起，直到该操作完成

推荐使用方式：

1 在进行数据清空时，可以使用truncate操作，因为该操作将会物理的清空数据表，并将其所占用的空间直接归还于操作系统。

2 为了保证数据表的磁盘页面数量能够保持在一个相对稳定值，可以定期执行该操作，如每天或每周中数据操作相对较少的时段。

3 考虑到该操作的开销，以及对其他错误的排斥，推荐的方式是，定期监控数据量变化较大的表，只有确认其磁盘页面占有量接近临界值时，才考虑执行一次该操作。即便如此，也需要注意尽量选择数据操作较少的时段来完成该操作

执行后其它操作的效率：

1 对于查询而言，由于存在大量的磁盘页面碎片，因此效率会逐步降低。

2 相比于不执行任何VACUUM操作，其效率更高，但是插入的效率会有所降低。

3 在执行完该操作后，所有基于该表的操作效率都会得到极大的提升。

更多相关文章

随机推荐