首先设置相关参数，默认情况下，autovacuum是开启的，但是执行 autovacuum的信息不在log中记录，需要设置log_autovacuum_min_duration参数为非-1的值，即可在log中看到autovacuum的信息。
相关参数配置参考：
# -- Query/Index Statistics Collector --
track_counts = on
track_activity_query_size = 2048
# -- Autovacuum --
autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 1d
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 500
autovacuum_vacuum_scale_factor = 0.5
autovacuum_vacuum_cost_limit = -1
autovacuum_vacuum_cost_delay = 30ms
log_autovacuum_min_duration=0
autovacuum 包含两个操作：automatic analyze和automatic vacuum，两个操作的触发条件是不一样的，引用官方文档的描述：
Tables whose relfrozenxid value is more than autovacuum_freeze_max_age transactions old are always vacuumed (this also applies to those tables whose freeze max age has been modified via storage parameters; see below). Otherwise, if the number of tuples obsoleted since the last VACUUM exceeds the “vacuum threshold”, the table is vacuumed. The vacuum threshold is defined as:
vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples
where the vacuum base threshold is autovacuum_vacuum_threshold, the vacuum scale factor is au- tovacuum_vacuum_scale_factor, and the number of tuples is pg_class.reltuples. The number of obsolete tuples is obtained from the statistics collector; it is a semi-accurate count updated by each UPDATE and DELETE operation. (It is only semi-accurate because some information might be lost un- der heavy load.) If the relfrozenxid value of the table is more than vacuum_freeze_table_age transactions old, the whole table is scanned to freeze old tuples and advance relfrozenxid, other- wise only pages that have been modified since the last vacuum are scanned.
For analyze, a similar condition is used: the threshold, defined as:
analyze threshold = analyze base threshold + analyze scale factor * number of tuples
is compared to the total number of tuples inserted, updated, or deleted since the last ANALYZE.
用如下脚本来查看当前relation的阈值：
select
to_number(current_setting('autovacuum_analyze_threshold'),'9999')+to_number(current_setting('autovacuum_analyze_scale_factor'),'99.99')*reltuples as auto_analyze_threshold,
to_number(current_setting('autovacuum_vacuum_threshold'),'9999')+to_number(current_setting('autovacuum_vacuum_scale_factor'),'99.99')*reltuples as auto_vacuum_threshold,
relname,reltuples
from pg_class c
where relname like '%vac%';
auto_analyze_threshold | auto_vacuum_threshold | relname | reltuples
------------------------+-----------------------+----------+-----------
55.2 | 60.4 | test_vac | 52
创建测试表，并查看初始触发阈值：
create table test_vac (id int);
auto_analyze_threshold | auto_vacuum_threshold | relname | reltuples | relpages | pg_stat_get_live_tuples | pg_stat_get_dead_tuples | pg_stat_get_last_autoanalyze_time | pg_stat_get_last_autovacuum_time
------------------------+-----------------------+----------+-----------+----------+-------------------------+-------------------------+-----------------------------------+----------------------------------
50 | 50 | test_vac | 0 | 0 | 0 | 0 | |
(1 row)
初始阈值为50，先insert into 49条数，预计不会触发auto analyze。
insert into test_vac select generate_series(1,49);
等待一段时间后，last_autoanalyze_time 依然没有刷新：
auto_analyze_threshold | auto_vacuum_threshold | relname | reltuples | relpages | pg_stat_get_live_tuples | pg_stat_get_dead_tuples | pg_stat_get_last_autoanalyze_time | pg_stat_get_last_autovacuum_time
------------------------+-----------------------+----------+-----------+----------+-------------------------+-------------------------+-----------------------------------+----------------------------------
50 | 50 | test_vac | 0 | 0 | 49 | 0 | |
(1 row)
接着继续 insert into 3条数据，超过阈值50，应该会触发 auto analyze。
insert into test_vac select generate_series(49,51);
等待2分钟左右时间，查看log，有auto analyze，同时relation的last_autoanalyze_time时间更新为log中的时间：
2016-08-19 16:39:08.757 CST,,,22242,,57b6c5ac.56e2,1,,2016-08-19 16:39:08 CST,3/363,2372,LOG,00000,"automatic analyze of table ""postgres.public.test_vac"" system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec",,,,,,,,,""
auto_analyze_threshold | auto_vacuum_threshold | relname | reltuples | relpages | pg_stat_get_live_tuples | pg_stat_get_dead_tuples | pg_stat_get_last_autoanalyze_time | pg_stat_get_last_autovacuum_time
------------------------+-----------------------+----------+-----------+----------+-------------------------+-------------------------+-----------------------------------+----------------------------------
55.2 | 60.4 | test_vac | 52 | 1 | 52 | 0 | 2016-08-19 16:39:08.757766+08 |
(1 row)
目前触发阈值分别为55.2和60.4。
更新两次全表52条数据，
postgres@postgres:5532 # update test_vac set id=100;
UPDATE 52
postgres@postgres:5532 # update test_vac set id=101;
UPDATE 52
等待几分钟时间，查看log，有auto vacuum和auto analyze，同时relation的last_autoanalyze_time和last_autovacuum_time时间更新为log中的时间：
auto_analyze_threshold | auto_vacuum_threshold | relname | reltuples | relpages | pg_stat_get_live_tuples | pg_stat_get_dead_tuples | pg_stat_get_last_autoanalyze_time | pg_stat_get_last_autovacuum_time
------------------------+-----------------------+----------+-----------+----------+-------------------------+-------------------------+-----------------------------------+----------------------------------
55.2 | 60.4 | test_vac | 52 | 1 | 52 | 0 | 2016-08-19 16:43:08.82371+08 | 2016-08-19 16:43:08.797203+08
(1 row)
2016-08-19 16:43:08.797 CST,,,25147,,57b6c69c.623b,1,,2016-08-19 16:43:08 CST,3/405,0,LOG,00000,"automatic vacuum of table ""postgres.public.test_vac"": index scans: 0
pages: 0 removed, 1 remain, 0 skipped due to pins
tuples: 104 removed, 52 remain, 0 are dead but not yet removable
buffer usage: 21 hits, 4 misses, 5 dirtied
avg read rate: 68.833 MB/s, avg write rate: 86.041 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec",,,,,,,,,""
2016-08-19 16:43:08.823 CST,,,25147,,57b6c69c.623b,2,,2016-08-19 16:43:08 CST,3/406,2375,LOG,00000,"automatic analyze of table ""postgres.public.test_vac"" system usage: CPU 0.00s/0.00u sec elapsed 0.02 sec",,,,,,,,,""
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
