Postgresql Automatic Vacuuming

VACUUM是PG日常回收磁盘空间的一个手段，通过参数设置，触发做vacuum回收磁盘空间

相关参数：

#autovacuum_work_mem = -1               # 最小1MB,如果是-1，那么使用maintenance_work_mem设置的值

vacuum_cost_delay = 10                  # 0-100 milliseconds

#vacuum_cost_page_hit = 1               # 0-10000 credits

#vacuum_cost_page_miss = 10             # 0-10000 credits

#vacuum_cost_page_dirty = 20            # 0-10000 credits

#vacuum_cost_limit = 200                # 1-10000 credits

#vacuum_defer_cleanup_age = 0   # 该参数只能在postgresql.conf参数文件中设置，表示延迟多少个事物后再vacuum

autovacuum = on                 # 自动vacuum的开关

log_autovacuum_min_duration = 0 # -1 关闭，0记录所有vacuum日志,其他数字表是大于等于这个数值的vacuum操作才记录到日志

autovacuum_max_workers = 3              # 设置最大autovacuum进程个数，默认3

autovacuum_naptime = 1min               # autovacuum运行的时间间隔

autovacuum_vacuum_threshold = 50        # vacuum触发的阀值条件之一 (修改，删除，的记录数)

autovacuum_analyze_threshold = 50       # analyze触发的阀值条件之一 (修改，删除，插入的记录数)

#触发vacuum计算公式：vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples

autovacuum_vacuum_scale_factor = 0.2    # vacuum触发的阀值条件之一 (修改，删除，插入表的20%)

autovacuum_analyze_scale_factor = 0.1   # analyze触发的阀值条件之一 (修改，删除，插入表的10%)

#触发analyze计算公式：analyze threshold = analyze base threshold + analyze scale factor * number of tuples

autovacuum_freeze_max_age = 200000000  

#即使autovacuum未开启，也会vacuum，表示一个表中的xid如果大于设置的阀值，那么可能导致数据丢失，不可见。如果达到这个阀值，那么就会触发vacuum，防止wraparound，可以通过 pg_class.relfrozenxid 查询表的事务年龄。

autovacuum_multixact_freeze_max_age = 400000000 # 对于multixid冻结的阀值，pg_class.relminmxid查询，如果达到该阀值，就进行vaccum

#autovacuum_vacuum_cost_delay = 20ms    

#autovacuum执行时，对vacuum的cost进程评估，如果超过autovacuum_vacuum_cost_limit的值，那么就延迟该参数的值20ms，如果设置为-1，那么使用vacuum_cost_delay设置的值

                                       

#autovacuum_vacuum_cost_limit = -1      

# 默认autovacuum评估的阀值，设置为-1，表是使用vacuum_cost_limit的值，超过就会休眠autovacuum_vacuum_cost_delay这么久的时间，然后再唤醒继续执行vacuum

#vacuum_freeze_min_age = 50000000

#vacuum_freeze_table_age = 150000000

#vacuum_multixact_freeze_min_age = 5000000

#vacuum_multixact_freeze_table_age = 150000000

触发analyze：

hank=> create table test1 (a int,b text);

CREATE TABLE

hank=> select reltuples from pg_class where relname='test1';

 reltuples 

-----------

         0

hank=> show autovacuum_analyze_threshold;

-[ RECORD 1 ]----------------+---

autovacuum_analyze_threshold | 50

hank=> show autovacuum_analyze_scale_factor;

-[ RECORD 1 ]-------------------+----

autovacuum_analyze_scale_factor | 0.1

根据之前触发analyze的条件计算所得

hank=> select 0.1*0+50;

?column? | 50.0     #超过50就进行analyze

 hank=> select * from pg_stat_all_tables where relname ='test1';

-[ RECORD 1 ]-------+------

relid               | 21913

schemaname          | hank

relname             | test1

seq_scan            | 0

seq_tup_read        | 0

idx_scan            | 

idx_tup_fetch       | 

n_tup_ins           | 50

n_tup_upd           | 0

n_tup_del           | 0

n_tup_hot_upd       | 0

n_live_tup          | 50

n_dead_tup          | 0                       #

n_mod_since_analyze | 50

last_vacuum         | 

last_autovacuum     | 

last_analyze        | 

last_autoanalyze    |                         #最后一次自动ananlyze的时间

vacuum_count        | 0

autovacuum_count    | 0

analyze_count       | 0

autoanalyze_count   | 0

hank=>insert into test1 values (51,'hank');

hank=> select * from pg_stat_all_tables where relname ='test1';

-[ RECORD 1 ]-------+------------------------------

relid               | 21913

schemaname          | hank

relname             | test1

seq_scan            | 0

seq_tup_read        | 0

idx_scan            | 

idx_tup_fetch       | 

n_tup_ins           | 51

n_tup_upd           | 0

n_tup_del           | 0

n_tup_hot_upd       | 0

n_live_tup          | 51

n_dead_tup          | 0

n_mod_since_analyze | 0

last_vacuum         | 

last_autovacuum     | 

last_analyze        | 

last_autoanalyze    | 2016-04-08 13:59:40.333561+08

vacuum_count        | 0

autovacuum_count    | 0

analyze_count       | 0

autoanalyze_count   | 1

触发vacuum：

hank=> select reltuples from pg_class where relname='test1';

-[ RECORD 1 ]-

reltuples | 51

hank=> show autovacuum_vacuum_threshold;

-[ RECORD 1 ]---------------+---

autovacuum_vacuum_threshold | 50

hank=> show autovacuum_vacuum_scale_factor;

-[ RECORD 1 ]------------------+----

autovacuum_vacuum_scale_factor | 0.2

hank=> select 0.2*51+50;

-[ RECORD 1 ]--

?column? | 60.2    #需要61条触发vacuum

hank=> update test1 set b='oooo' where a<50;           

UPDATE 49

hank=> delete from test1 where a<12;

DELETE 11

hank=> select * from pg_stat_all_tables where relname ='test1';

-[ RECORD 1 ]-------+------------------------------

relid               | 21913

schemaname          | hank

relname             | test1

seq_scan            | 2

seq_tup_read        | 102

idx_scan            | 

idx_tup_fetch       | 

n_tup_ins           | 51

n_tup_upd           | 49

n_tup_del           | 11

n_tup_hot_upd       | 49

n_live_tup          | 40

n_dead_tup          | 60         #可以看到有60条数据是dead rows

n_mod_since_analyze | 60

last_vacuum         | 

last_autovacuum     | 

last_analyze        | 

last_autoanalyze    | 2016-04-08 13:59:40.333561+08

vacuum_count        | 0

autovacuum_count    | 0

analyze_count       | 0

autoanalyze_count   | 1

hank=> delete from test1 where a<13;                           

DELETE 1

hank=> select * from pg_stat_all_tables where relname ='test1';

-[ RECORD 1 ]-------+------------------------------

relid               | 21913

schemaname          | hank

relname             | test1

seq_scan            | 3

seq_tup_read        | 142

idx_scan            | 

idx_tup_fetch       | 

n_tup_ins           | 51

n_tup_upd           | 49

n_tup_del           | 12

n_tup_hot_upd       | 49

n_live_tup          | 39

n_dead_tup          | 0

n_mod_since_analyze | 0

last_vacuum         | 

last_autovacuum     | 2016-04-08 14:10:51.113524+08    #最后autovacuum的时间

last_analyze        | 

last_autoanalyze    | 2016-04-08 14:10:51.143719+08    

vacuum_count        | 0

autovacuum_count    | 1

analyze_count       | 0

autoanalyze_count   | 2

修改表级autovacuum触发条件：

修改表级参数：

hank=>  alter table test1 set  (autovacuum_vacuum_threshold = 100,autovacuum_vacuum_scale_factor = 0.5);

ALTER TABLE

hank=> select reltuples from pg_class where relname='test1';

-[ RECORD 1 ]-

reltuples | 39

hank=> select 39*0.5+100;  #计算触发autovacuum条数

-[ RECORD 1 ]---

?column? | 119.5

hank=> update test1 set b='abc' where a<51;

UPDATE 38

hank=> update test1 set b='aaa' where a<51;   

UPDATE 38

hank=> update test1 set b='bbb' where a<51;   

UPDATE 38

hank=> select 38*3;    #已经update114条

-[ RECORD 1 ]-

?column? | 114

hank=> select min(a) from test1 ; #因为之前实验删除过表看下a的最小值然后根据a的值继续更新到我们需要的条数

-[ RECORD 1 ]

min | 13

hank=> update test1 set b='ccc' where a<18;   #更新5条，目前是114+5=119

UPDATE 5

hank=> select * from pg_stat_all_tables where relname ='test1';  #观察last_autovacuum可见并没有触发autovacuum

-[ RECORD 1 ]-------+------------------------------

relid               | 21913

schemaname          | hank

relname             | test1

seq_scan            | 9

seq_tup_read        | 376

idx_scan            | 

idx_tup_fetch       | 

n_tup_ins           | 51

n_tup_upd           | 168

n_tup_del           | 12

n_tup_hot_upd       | 168

n_live_tup          | 39

n_dead_tup          | 119

n_mod_since_analyze | 5

last_vacuum         | 

last_autovacuum     | 2016-04-08 14:10:51.113524+08

last_analyze        | 

last_autoanalyze    | 2016-04-08 14:32:51.394712+08

vacuum_count        | 0

autovacuum_count    | 1

analyze_count       | 0

autoanalyze_count   | 3

hank=> update test1 set b='ddd' where a<14;       #更新一条，现在是120条，刚好触发autovacuum的条件                

UPDATE 1

hank=> select * from pg_stat_all_tables where relname ='test1'; #可见last_autovacuum已经变化

-[ RECORD 1 ]-------+------------------------------

relid               | 21913

schemaname          | hank

relname             | test1

seq_scan            | 10

seq_tup_read        | 415

idx_scan            | 

idx_tup_fetch       | 

n_tup_ins           | 51

n_tup_upd           | 169

n_tup_del           | 12

n_tup_hot_upd       | 169

n_live_tup          | 39

n_dead_tup          | 0

n_mod_since_analyze | 6

last_vacuum         | 

last_autovacuum     | 2016-04-08 14:35:51.513553+08

last_analyze        | 

last_autoanalyze    | 2016-04-08 14:32:51.394712+08

vacuum_count        | 0

autovacuum_count    | 2

analyze_count       | 0

autoanalyze_count   | 3

analyze同上的道理，就不再实验

pg_stat_all_tables视图见下表

ColumnTypeDescriptionrelidoidtable的oidschemanamename表所属的schema名称relnamename表名seq_scanbigintNumber of sequential scans initiated on this tableseq_tup_readbigintNumber of live rows fetched by sequential scansidx_scanbigintNumber of index scans initiated on this tableidx_tup_fetchbigintNumber of live rows fetched by index scansn_tup_insbigintNumber of rows insertedn_tup_updbigintNumber of rows updated (includes HOT updated rows)n_tup_delbigintNumber of rows deletedn_tup_hot_updbigintNumber of rows HOT updated (i.e., with no separate index update required)n_live_tupbigintlive rows 的数量n_dead_tupbigintdead rows的数量n_mod_since_analyzebigintEstimated number of rows modified since this table was last analyzedlast_vacuumtimestamp with time zoneLast time at which this table was manually vacuumed (not counting VACUUM FULL)last_autovacuumtimestamp with time zone最后一次autovacuum的时间last_analyzetimestamp with time zoneLast time at which this table was manually analyzedlast_autoanalyzetimestamp with time zone最后一次autoanalyze的时间vacuum_countbigintNumber of times this table has been manually vacuumed (not counting VACUUM FULL)autovacuum_countbigintNumber of times this table has been vacuumed by the autovacuum daemonanalyze_countbigintNumber of times this table has been manually analyzedautoanalyze_countbigintNumber of times this table has been analyzed by the autovacuum daemon

参考：http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html