pg 定期vacuum和reindex

定期vacuum和reindex： 
一、说明 

    postgresql数据库执行delete操作后，表中的记录只是被标示为删除状态，并没有释放空间，在以后的update或insert操作中该部分的空间是不能够被重用的。在postgresql中用于维护数据库磁盘空间的工具是VACUUM，其作用是删除那些已经标示为删除的数据并释放空间。但vacuum工具不能够对相应的索引进行清理，需要手动去重建索引。 

   因此日常我们需要定期的做一些vacuum和reindex的操作。 

二、vacuum 

VACUUM语法结构： 
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] 
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] 

1）vacuum 、vacuum full、vacuum analyze、autovacuum 命令的区别 
vacuum          只是将删除状态的空间释放掉，转换到能够重新使用的状态，但是它不进行空间合并。 
vacuum full     将会使空间释放的信息表现在系统级别，其实质是将当前删除记录后面的数据进行移动，使得整体的记录连贯 
                起来，降低了“高水位标记”。因此它需要lock table。 
vacuum analyze  更新统计信息，使得优化器能够选择更好的方案执行sql。 
autovacuum      数据库定时自动进行vacuum 


备注： 
1、对于有大量update 的表，vacuum full是没有必要的，因为它的空间还会再次增长，所以vacuum就足够了。 
2、oracle中同样也有analyze，作用也相同，目前更多的使用的是dbms_stats包。统计信息收集和更新对于系统性能来说非常重要。 
   oracle在进行imp后自动的对相应数据对象进行统计信息的收集和更新，而postgresql的恢复过程还没有集成到里面，需要手动去执行。 
3、适当调大参数maintenance_work_mem，可加快vacuum的执行速度 

举例： 
tina=# vacuum t_sfa_sample;    ---非常快速（10s内）就执行完了 
VACUUM 
tina=# vacuum full t_sfa_sample;  ---约几分钟（表越大，时间越长） 
VACUUM 
tina=# vacuum analyze t_sfa_sample;  
VACUUM 

2）autovacuum参数配置 
执行直接由autovacuum参数值决定，默认值是on。但当需要冻结xid时，即使此值为off，PG也会进行vacuum： 

log_autovacuum_min_duration：默认值为-1,关闭vacuum的日志记录，配置为0表示记录autovacuum的所有log。参数设置为正整数表示对于在此时间内完成的vacuum操作不进行log记录，如果没能完成，则记录超出时间内的log 

autovacuum_max_workers：最大的autovacuum进程的数量，默认值为3。参数大小的配置主要依据系统当前负载和资源。对于系统负载较重的情况，建议开启少量的进程为好，反之，空闲时间可以采用较大值的方式 

autovacuum_naptime：检查数据库的时间间隔。默认为1分钟。这个naptime会被vacuum launcher分配到每个DB上，autovacuum_naptime/num of db。 

autovacuum_vacuum_threshold：参数表示执行autovacuum操作之前，对单个表中记录执行DML操作的最少行数。达到该行数时自动激活autovacuum操作。该参数针对数据库中的所有表，还可以通过对单个表配置不同的值来改变相应表的autovacuum操作。默认值是50。与autovacuum_vacuum_scale_factor配合使用，当update,delete的tuples数量超过autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold时，进行vacuum。如果要使vacuum工作勤奋点，则将此值改小。 
                             
autovacuum_analyze_threshold：激活自动analyze操作的最小行数。默认值50。与autovacuum_analyze_scale_factor配合使用,当update,insert,delete的tuples数量超过autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold时，进行analyze 

autovacuum_vacuum_scale_factor：该参数采用百分比的方式设定阀值。默认值为20%，当DML涉及的数据量大于某个表的20%时，自动触发autovacuum操作。同样可以通过对单个表进行阀值设定 

autovacuum_analyze_scale_factor：机制与上面相同，到达阀值是自动激活analyze操作。同样可以通过对单个表进行阀值设定 

autovacuum_freeze_max_age：为防止事务ID的重置，在启用vacuum操作之前，表的pg_class .relfrozenxid字段的最大值，默认为200万 

autovacuum_vacuum_cost_delay：autovacuum进程的时间延迟限制，默认值是20ms。对于单个表同样适用。取vacuum_cost_delay值 

autovacuum_vacuum_cost_limit：autovacuum进程的开销延迟限制，默认值是-1，表示不进行开销限制，到vacuum_cost_limit的值，这个值是所有worker的累加值。 


基于代价的vacuum参数: 
vacuum_cost_delay ：计算每个毫秒级别所允许消耗的最大IO，vacuum_cost_limit/vacuum_cost_dely。 默认vacuum_cost_delay为20毫秒。 
vacuum_cost_page_hit ：vacuum时，page在buffer中命中时，所花的代价。默认值为1。 
vacuum_cost_page_miss：vacuum时，page不在buffer中，需要从磁盘中读入时的代价默认为10。 
vacuum_cost_page_dirty：当vacuum时，修改了clean的page。这说明需要额外的IO去刷脏块到磁盘。默认值为20。 
vacuum_cost_limit：当超过此值时，vacuum会sleep。默认值为200。 

备注：在某些情况下，最好是把autovacuum关掉，因为postgresql.conf中，你看到autovacuum前面加了#号，但其实是默认设置为开启。而且这个vacuum是对所有的数据库进行vacuum，如果有那么一个数据库中表多数据大，ddl操作也多，那就导致cpu超高，而且持续时间特长。我们可以设置autovacuum=off ,然后对经常使用的database进行脚本vacuum，设定自动的时间最好是数据库使用不多的时间段，比如半夜。 
在进行vacuum时，耗资源耗内存，有时候还会锁死。 

例如脚本： 
cat pg_engine_vacuum.sh 
#!/bin/bash 
#2014-10-22 tina 
date=`date +"%Y-%m-%d %H:%M:%S"` 
echo "begin time is: $date" >>/tmp/pg_tinadb_vacuum.log 

tables=$(psql -U postgres -d tinadb -c "select tablename from pg_tables where schemaname='public';" |grep -v "tablename") 
echo $tables >>/tmp/pg_tinadb_vacuum.log 

for table in $tables 
do 
psql -U postgres -d tinadb -c "vacuum full $table;" >>/tmp/pg_tinadb_vacuum.log 
echo "table $table has finished vacuum.">>/tmp/pg_tinadb_vacuum.log 
done 

添加到crontab 定时执行即可。 


3)命令设置某个表不进行autovacuum： 
tina=# alter table test1 set (autovacuum_enabled=false); 
ALTER TABLE 
tina=# select relname from pg_class where reloptions@>array['autovacuum_enabled=false']; 
relname 
--------- 
test1 
(1 row) 

三、reindex 
reindex命令： 
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ] 

REINDEX 使用存储在索引的表上的数据重建索引， 替换旧的索引拷贝。使用 REINDEX 有两个主要原因： 
1、索引崩溃，并且不再包含有效的数据。尽管理论上这是不可能发生的， 但实际上索引会因为软件毛病或者硬件问题而崩溃。 
2、要处理的索引包含大量无用的索引页未被回收。在某些情况下， 这个问题会发生在 PostgreSQL 里面的B-树索引上。 
   
   对于B-Tree索引，只有那些已经完全清空的索引页才会得到重复使用，对于那些仅部分空间可用的索引页将不会得到重用，如果一个页面中大多数索引键值都被删除，只留下很少的一部分，那么该页将不会被释放并重用。在这种极端的情况下，由于每个索引页面的利用率极低，一旦数据量显著增加，将会导致索引文件变得极为庞大，不仅降低了查询效率，而且还存在整个磁盘空间被完全填满的危险。对于重建后的索引还存在另外一个性能上的优势，因为在新建立的索引上，逻辑上相互连接的页面在物理上往往也是连在一起的，这样可以提高磁盘页面被连续读取的几率，从而提高整个操作的IO效率。 

postgres=# reindex database tina; 
ERROR:  can only reindex the currently open database 
postgres=# \c tina 
You are now connected to database "tina" as user "postgres". 
tina=# reindex database tina; 
NOTICE:  table "pg_catalog.pg_class" was reindexed 
NOTICE:  table "pg_catalog.pg_statistic" was reindexed 
.... 
会将当前open的tina库里的索引都重建 

reindx table tablename; 
会将表中的所有索引都重建 

reindex index indexname; 
会将指定的这一个索引重建 

索引原大小：    
SELECT relname, pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' and relname='t_cert_sample_state_idx'; 
         relname         |  size   
-------------------------+--------- 
t_cert_sample_state_idx | 106000K 
(1 row) 


重建索引： 
REINDEX INDEX t_cert_sample_state_idx; 
REINDEX 

重建后索引大小： 
SELECT relname, pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' and relname='t_cert_sample_state_idx'; 
         relname         |  size  
-------------------------+-------- 
t_cert_sample_state_idx | 94648K 
(1 row) 

索引重建后一定要分析表： 
ANALYZE t_cert; 
ANALYZE 


补充： 
1. 查看表所占用的磁盘页面数  
SELECT relfilenode, relpages FROM pg_class WHERE relname = 't_cert'; 
relfilenode | relpages 
-------------+---------- 
    49037978 |   384895 
(1 row) 
说明：relpages只能被VACUUM、ANALYZE和几个DDL命令更新，如CREATE INDEX。通常一个页面的长度为8K字节。 
   
    
2. 查看表的索引名和索引占用的磁盘页面数量。 
SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i 
WHERE c.relname = 't_cert' AND c.oid = i.indrelid AND c2.oid = i.indexrelid ORDER BY c2.relname; 
             relname              | relpages 
----------------------------------+---------- 
sign_android_pkey                |    11659 
t_cert_sample_state_idx          |    11831 
t_cert_serialnumber_hash_md5_key |    45887 
(3 rows) 

3.查看表大小    
SELECT pg_relation_size('t_cert')/1024/1024 || 'MB' AS size; 
  size  
-------- 
3007MB 
(1 row) 

4.查看索引大小 
SELECT pg_relation_size('t_cert_sample_state_idx')/1024/1024 || 'MB' AS size; 
size 
------ 
92MB 
(1 row)