PostgreSQL上的Aggressive Autovacuum

我试图让PostgreSQL主动自动清理数据库。我目前已将自动真空配置如下：

autovacuum_vacuum_cost_delay = 0＃关闭基于成本的真空
autovacuum_vacuum_cost_limit = 10000＃最大值
autovacuum_vacuum_threshold = 50＃默认值
autovacuum_vacuum_scale_factor = 0.2＃默认值

我注意到，只有在数据库未处于加载状态时才会启动自动清理功能，因此我遇到了死元组比活动元组多得多的情况元组。有关示例，请参见随附的屏幕截图。其中一张桌子有23个活动元组，但有16845个死元组正在等待真空。这太疯狂了！

当测试运行完成并且数据库服务器处于空闲状态时，自动真空启动，这不是我想要的，因为我希望自动真空启动每当失效元组的数量超过活动元组的20％+ 50时（因为已配置数据库）。服务器空闲时自动清理对我来说毫无用处，因为生产服务器在持续的时间内有望达到每秒数千次的更新，这就是为什么即使服务器处于负载状态，我也需要自动清理运行的原因。
我有什么想念的吗？在服务器负载沉重时，如何强制运行自动吸尘器？

更新

这可能是锁定问题吗？有问题的表是汇总表，这些表是通过插入后触发器填充的。这些表被锁定为SHARE ROW EXCLUSIVE模式，以防止并发写入同一行。

#1 楼

Eelke几乎可以肯定，您的锁定阻止了自动真空。 Autovacuum旨在让位给用户活动。如果这些表被锁定，则自动清理无法清理它们。

但是，为方便起见，我想举一个过度攻击性自动清理的示例设置，因为您提供的设置不太能做到。请注意，使自动真空更具侵略性不可能解决您的问题。还要注意，默认自动真空设置是基于使用DBT2进行200多次测试运行以寻求最佳设置组合的，因此，除非您有充分的理由认为不这样做，或者除非您的数据库明显位于外部，否则应假定默认设置为良好OLTP数据库的主流（例如，每秒可更新10K的小型数据库，或3TB数据仓库）。

首先，打开日志记录，以便您可以检查autovacuum是否在按照您的想法进行它是：

log_autovacuum_min_duration = 0

然后让我们派更多的autovac工人并让他们更频繁地检查桌子：

autovacuum_max_workers = 6
autovacuum_naptime = 15s

让我们降低自动真空度和自动分析的阈值，使其更快地触发：

autovacuum_vacuum_threshold = 25
autovacuum_vacuum_scale_factor = 0.1

autovacuum_analyze_threshold = 10
autovacuum_analyze_scale_factor = 0.05

然后让自动真空度的中断性降低，因此完成速度更快，但要付出代价对并发用户活动产生更大影响的方法：

autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

您有完整的程序来执行具有侵略性的au tovacuum，这可能适合于更新速度非常高的小型数据库，但对并发用户活动的影响可能太大。

此外，请注意，可以对每个表调整autovacuum参数，对于需要调整自动真空的行为，这几乎总是一个更好的答案。

同样，也不太可能解决您的实际问题。

#2 楼

只是为了查看哪些表完全符合自动抽真空的条件，可以使用以下查询（基于http://www.postgresql.org/docs/current/static/routine-vacuuming.html）。但是请注意，查询不会查找表的特定设置：

 SELECT psut.relname,
     to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,
     to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,
     to_char(pg_class.reltuples, '9G999G999G999') AS n_tup,
     to_char(psut.n_dead_tup, '9G999G999G999') AS dead_tup,
     to_char(CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
         + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
            * pg_class.reltuples), '9G999G999G999') AS av_threshold,
     CASE
         WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
             + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
                * pg_class.reltuples) < psut.n_dead_tup
         THEN '*'
         ELSE ''
     END AS expect_av
 FROM pg_stat_user_tables psut
     JOIN pg_class on psut.relid = pg_class.oid
 ORDER BY 1;

#3 楼

是的，这是一个锁定问题。根据此页面（非完整），VACUUM需要SHARE UPDATE EXCLUSIVE访问，该访问已被您使用的锁定级别所阻止。

确定要使用此锁定吗？ PostgreSQL是ACID兼容的，因此在大多数情况下并发写入都不是问题，因为如果发生序列化违规，PostgreSQL将中止其中一个事务。

此外，您还可以使用SELECT FOR UPDATE锁定行来锁定行。而不是整个表。

另一个没有锁定的替代方法是使用可序列化的事务隔离级别。但是，这可能会影响其他事务的性能，因此您应该为更多的序列化失败做好准备。

这是由于锁定了摘要表，因为这些摘要表是使用SHARE ROW EXCLUSIVE MODE锁定的。没有锁定的并发写入可能会成功，但是最有可能的是它们最终将以错误的值结束。想象一下，我正在维护N个类型X的行。如果我同时插入2个类型X的行，而没有锁定，我将在汇总表中以N +1而不是N + 2结束。有一个cron作业，可以手动清理数据库中的摘要表。它运作良好，似乎是推荐的方法，但是对我来说，感觉实在太像了。

–CadentOrange
2012年7月24日9:23

#4 楼

现有的“自动抽空合格”脚本非常有用，但是（正确说明）缺少表特定的选项。这是考虑到这些选项的修改后的版本：

WITH rel_set AS
(
    SELECT
        oid,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)::BIGINT
        END AS rel_av_vac_threshold,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)::NUMERIC
        END AS rel_av_vac_scale_factor
    FROM pg_class
) 
SELECT
    PSUT.relname,
    to_char(PSUT.last_vacuum, 'YYYY-MM-DD HH24:MI')     AS last_vacuum,
    to_char(PSUT.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
    to_char(C.reltuples, '9G999G999G999')               AS n_tup,
    to_char(PSUT.n_dead_tup, '9G999G999G999')           AS dead_tup,
    to_char(coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples, '9G999G999G999') AS av_threshold,
    CASE
        WHEN (coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples) < PSUT.n_dead_tup
        THEN '*'
    ELSE ''
    END AS expect_av
FROM
    pg_stat_user_tables PSUT
    JOIN pg_class C
        ON PSUT.relid = C.oid
    JOIN rel_set RS
        ON PSUT.relid = RS.oid
ORDER BY C.reltuples DESC;

#5 楼

增加自动真空处理的数量并减少午睡时间可能会有所帮助。这是我在存储备份信息的服务器上使用的PostgreSQL 9.1的配置，因此得到了大量的插入活动。

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

autovacuum_max_workers = 6              # max number of autovacuum subprocesses
autovacuum_naptime = 10         # time between autovacuum runs
autovacuum_vacuum_cost_delay = 20ms     # default vacuum cost delay for

我还将尝试降低cost_delay以使吸尘更具侵略性。

我可以还可以使用pgbench测试自动抽真空。

http://wiki.postgresql.org/wiki/Pgbenchtesting

高竞争示例：

创建Bench_Replication数据库

/>

pgbench -i -p 5433 bench_replication

运行pgbench

pgbench -U postgres -p 5432 -c 64 -j 4 -T 600 bench_replication

检查自动真空状态

psql
>\connect bench_replicaiton
bench_replication=# select schemaname, relname, last_autovacuum from pg_stat_user_tables;
 schemaname |     relname      |        last_autovacuum        
------------+------------------+-------------------------------
 public     | pgbench_branches | 2012-07-18 18:15:34.494932+02
 public     | pgbench_history  | 
 public     | pgbench_tellers  | 2012-07-18 18:14:06.526437+02
 public     | pgbench_accounts |

编程黑洞网