PostgreSQL autovacuum

在上一篇文章中，我们了解到 PostgreSQL Vacuum 有助于清除表中的死元组并释放空间，但是表上多久会发生一次vacuum呢？PostgreSQL Autovacuum 在这里有帮助！

vacuum可以手动启动，也可以使用 autovacuum 守护程序自动启动。

默认情况下，在 PostgreSQL 中启用 autovacuum。

autovacuum 的目的是自动执行 VACUUM 和 ANALYZE 命令。启用后，autovacuum 会检查已插入、更新或删除大量元组的表，然后根据阈值对表进行清理或分析。

autovacuum 自动分析或清理表时的阈值是多少？

真空阈值为

vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples

分析阈值为

analyze threshold = analyze base threshold + analyze scale factor * number of tuples

下面我们来详细了解一下上面的参数

默认值

vacuum基础阈值 = 50

vacuum比例因子=0.2

即，如果一个表有 1000 条记录，如果该表有 50+0.2*1000 条记录，即 250 条已更改，则该表是 autovacuum 的候选者。

检查我们查询的 vac_table 的 autovaccum 状态

SELECT schemaname,relname,last_autovacuum,last_autoanalyze FROM pg_stat_all_tables where schemaname ='testuser' and relname='vac_table';

testdb=# SELECT schemaname,relname,last_autovacuum,last_autoanalyze FROM pg_stat_all_tables where schemaname ='testuser' and relname='vac_table'; schemaname |  relname  | last_autovacuum | last_autoanalyze------------+-----------+-----------------+------------------ testuser   | vac_table |                 |(1 row)

由于我的表不适合 autovacuum，所以我做了一些更改并等待了一段时间

testdb=# update vac_table set name='record11' ;UPDATE 8testdb=# update vac_table set name='record12' ;UPDATE 8testdb=# update vac_table set name='record13' ;UPDATE 8testdb=# update vac_table set name='record14' ;UPDATE 8testdb=# commit;WARNING:  there is no transaction in progressCOMMIT

它会自动清理。

testdb=#  SELECT schemaname,relname,last_autovacuum,last_autoanalyze FROM pg_stat_all_tables where schemaname ='testuser' and relname='vac_table'; schemaname |  relname  |         last_autovacuum          | last_autoanalyze------------+-----------+----------------------------------+------------------ testuser   | vac_table | 2019-02-25 20:03:52.286049+05:30 |(1 row)

日志说，

2019-02-25 20:03:52 IST [31674]: [1-1] user=,db=,app=,client= LOG:  automatic vacuum of table "testdb.testuser.vac_table": index scans: 0        pages: 0 removed, 1 remain, 0 skipped due to pins, 0 skipped frozen        tuples: 32 removed, 8 remain, 0 are dead but not yet removable        buffer usage: 25 hits, 4 misses, 5 dirtied        avg read rate: 37.879 MB/s, avg write rate: 47.348 MB/s        system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec

在这里，正如文章前面提到的，如果表大小太大，我们会遇到问题，比如 1,00,000 条记录。

仅当 50+0.2*100000 = 20050条记录已被修改时，它才是 autovacuum 的候选者 ，这是一个巨大的权利吗？

我们可以通过在 PostgreSQL.conf 文件中设置它们来解决 PostgreSQL autovacuum 调整参数的问题。

查询以检查表及其死元组：

SELECT *,n_dead_tup > av_threshold AS "av_needed",CASE WHEN reltuples > 0THEN round(100.0 * n_dead_tup  (reltuples))ELSE 0ENDAS pct_deadFROM(SELECTN.nspname,C.relname,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,pg_stat_get_live_tuples(C.oid) AS n_live_tup,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,C.reltuples AS reltuples,round(current_setting('autovacuum_vacuum_threshold')::integer+ current_setting('autovacuum_vacuum_scale_factor')::numeric *C.reltuples)AS av_threshold,date_trunc('minute',greatest(pg_stat_get_last_vacuum_time(C.oid),pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum,date_trunc('minute',greatest(pg_stat_get_last_analyze_time(C.oid),pg_stat_get_last_analyze_time(C.oid))) AS last_analyzeFROM pg_class CLEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)WHERE C.relkind IN ('r', 't')AND N.nspname NOT IN ('pg_catalog', 'information_schema') ANDN.nspname !~ '^pg_toast') AS avORDER BY av_needed DESC,n_dead_tup DESC;

调整 PostgreSQL Autovacuum

大多数情况下，Autovacuum 是 PostgreSQL 决定何时清理表，但对于我们来说，查看表并确定表是否需要 autovacuum 也同样重要，即使它不是 autovacuum 的候选者。

以下是我们在调整 autovacuum 时需要寻找的参数。

• autovacuum_vacuum_threshold = 50

• autovacuum_vacuum_scale_factor = 0.2

• Vacuum_cost_page_hit = 1

• Vacuum_cost_page_miss = 10

• Vacuum_cost_page_dirty = 20

• autovacuum_vacuum_cost_delay = 20ms

• autovacuum_vacuum_cost_limit = 200

根据上面的示例，如果我们将 autovacuum_vacuum_scale_factor 从 0.2 更改为 0.02，那么它将在 2050条记录更新时成为候选。

但是 PostgreSQL.conf 中的这些更改会影响所有表，并且可能会不希望地影响小表的清理，包括例如系统目录。

我们可以使用以下命令单独设置比例因子。

ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.02);ALTER TABLE t SET (autovacuum_vacuum_threshold = 100);

PostgreSQL autovacuum 是如何工作的？

不断地自动清理从磁盘中读取表的 8KB 页，并修改/写入包含死元组的页。这是一个资源（CPU 和磁盘 I/O）密集型操作，如果页面读取不包含任何死元组，它只会忽略该页面。

这就是基于成本的清理出现的地方。

本计算基于定义三个基本操作的成本：

vacuum_cost_page_hit：表示页面已经在共享缓冲区中，默认为 1。

Vacuum_cost_page_miss：表示必须从磁盘读取页面，默认为 10。

Vacuum_cost_page_dirty：表示从磁盘读取的页面必须是因死元组而修改，默认 20。

一旦成本总和达到 vacuum_cost_limit（vacuum_cost_limit默认为 200，手动 VACUUM 禁用），VACUUM 进程将休眠并且对于 autovacuum_vacuum_cost_delay（默认 20 毫秒）不执行任何操作。

使用默认参数，这意味着 autovacuum 最多将 4MB/s 写入磁盘，并从磁盘或 OS 页面缓存中读取 8MB/s。

要点：

是的，还有很多事情要讨论，我们几乎涵盖了 autovacuum 的基础知识，我们最后总结了这两个部分的 PostgreSQL：Vacuum 和 PostgreSQL Autovacuum。

1. 除非非常重要，否则不要禁用 autovacuum。

2. 在高 OLTP 机器上，建议在table上使用手动 VACUUM。

3. 降低事务计数高的数据库的 autovacuum_vacuum_scale_factor。

4. 在具有旧的、非活动数据的表上调整 autovacuum_vacuum_scale_factor。

5. 根据您的硬件调整节流参数以提高性能。