PostgreSQL内嵌的autovacuum一直在改进,但是在很多客户环境下,仍然有很多不满足的地方。
autovacuum的设置是基于一些扩展因子和阈值的。在高峰期,如果表上有大量的事务,就很容易超过这些设置。实际上是在错误的时间做了vacuum操作。
很容易看到有些表频繁变成autovacuum的候选对象,重复地占用了工作进程。而对于那些在候选表列表靠后的表,会很长时间得不到vacuum。当前的autovacuum还不够智能,并不能知道哪些表需要给与高优先级做vacuum。
这可能是最糟糕的事情。即使dba基于需求和时间窗口,想调整autovacuum_vacuum_cost_limit:
alter system set autovacuum_vacuum_cost_limit=2000; select pg_reload_conf();
这对正在工作的autovacuum工作进程不会起作用,下一次启动才有效。
看到过期的表或饥饿的表,绝望的dba会设置激进的设置和分配更多的工作进程。很多时候,这会使系统超出其限制,因为当系统已经有大量活动会话时,一切都在错误的时间以高攻击性出现。 工作进程的个数*maintenance_work_mem,太多的内存消耗,系统性能受到很大影响。我见过的最糟糕的情况是autovacuum worker占用了高达50% 的服务器资源。
如果在高峰窗口期间完成,autovacuum worker将引用旧的xid/快照。它不会清理在同一时间段内生成的死元组,这与autovacuum的目的背道而驰。
很容易遇到,在autovacuum持续时间较长的情况下饥饿的表会达到 autovacuum_freeze_max_age并触发wraparound从而阻塞autovacuum。
由于效率低下,我们不断看到DBA倾向于完全禁用autovacuum并引发更多问题甚至中断。至少,我对PostgreSQL新手的要求是,请永远不要尝试关闭autovacuum。这不是解决autovacuum 相关问题的方法。
参数autovacuum_vacuum_cost_limit和autovacuum_vacuum_cost_delay是控制autovacuum工作进程的两个主要参数。autovacuum_max_workers控制同时在不同表上工作的进程数量。默认情况下,autovacuum_vacuum_cost_limit将被禁用 (-1),这意味着其他参数Vacuum_cost_limit的值将生效。所以建议的第一件事是为autovacuum_vacuum_cost_limit设置一个值,这将有助于我们单独控制autovacuum工作进程。
我在许多安装中看到的一个常见错误是autovacuum_max_workers设置为非常高的值,例如 15!。假设这会使autovacuum运行得更快。请记住autovacuum_vacuum_cost_limit在所有工作进程之间分配。所以工作进程的数量越高,每个工作进程的运行速度就越慢。如上所述,较慢的工作进程意味着无效的清理工作。而且,它们每一个最多可以占用maintenance_work_mem大小的内存,一般情况下,autovacuum_max_workers的默认值,即3就足够了。请仅在绝对必要时考虑增加它。
postgres=# show autovacuum_vacuum_cost_limit; autovacuum_vacuum_cost_limit ------------------------------ -1 (1 row) postgres=# show autovacuum_vacuum_cost_delay; autovacuum_vacuum_cost_delay ------------------------------ 2ms (1 row) postgres=# show vacuum_cost_limit; vacuum_cost_limit ------------------- (1 row) postgres=# show autovacuum_max_workers; autovacuum_max_workers ------------------------ (1 row) postgres=# show maintenance_work_mem; maintenance_work_mem ---------------------- 64MB (1 row) postgres=#
实例级别的一揽子调整设置可能对一些表不起作用。这些异常需要特殊处理,并且在表级别调整设置可能变得不可避免。我将从那些过于频繁地成为 utovacuum候选者的表开始。
PostgreSQL使用log_autovacuum_min_duration记录日志,该设置提供了那些经常成为候选表的详细信息,以及那些花费大量时间和精力的autovacuum运行。就个人而言,我更喜欢以此为起点。也可以通过比较两个不同时间戳中的pg_stat_all_tables的autovacuum_count来获得autovacuum运行的摘要。我们需要考虑的是HOT(Heap Only Tuple)更新和填充因子。可以使用同一视图 (pg_stat_all_tables) 的 n_tup_hot_upd分析热更新信息,调整它可以大大降低autovacuum要求。
根据所有这些信息分析,可以调整特定的表级别设置。例如:
alter table t1 set (autovacuum_vacuum_scale_factor=0.0, autovacuum_vacuum_threshold=130000, autovacuum_analyze_scale_factor=0.0, autovacuum_analyze_threshold=630000, autovacuum_enabled=true, fillfactor=82);
我们的目标不是禁用autovacuum,而是用我们对系统的了解来补充autovacuum。这根本不需要复杂。我们可以拥有的最简单的方法是在其自身或其 TOAST 具有最大年龄的表上运行“VACUUM FREEZE”。
我们可以在最长时间或toast表上执行
例如,我们可以实现具有以下内容的 vaccumjob.sql 文件
WITH cur_vaccs AS (SELECT split_part(split_part(substring(query from '.*\..*'),'.',2),' ',1) as tab FROM pg_stat_activity WHERE query like 'autovacuum%') select 'VACUUM FREEZE "'|| n.nspname ||'"."'|| c.relname ||'";' from pg_class c inner join pg_namespace n on c.relnamespace = n.oid left join pg_class t on c.reltoastrelid = t.oid and t.relkind = 't' where c.relkind in ('r','m') AND NOT EXISTS (SELECT * FROM cur_vaccs WHERE tab = c.relname) order by GREATEST(age(c.relfrozenxid),age(t.relfrozenxid)) DESC limit 100; \gexec
找出当前需要autovacuum的100个aged表,并执行“vacuum freeze”。其中“\gexec”会执行上面的查询结果。
也可以做的定时任务:
0 0 * * * /full/path/to/psql -X -f /path/to/vacuumjob.sql > /tmp/vacuumjob.out 2>&1
手动设置定时任务具有以下好处:
1.在高峰期这些表变成autovacuum的候选表的机会降低了。
2.在低峰期间执行可以提高资源的使用。
3.候选表的选择是根据表的age,而不是扩展因子和阈值,表饥饿的机会降低了。也避免了有些表频繁地变成候选表。
4.在客户/用户环境中,几乎不会再报告wraparound阻塞autovacuum。