PostgreSQL VACUUM 之深入浅出 (二)

Python微信订餐小程序课程视频

https://edu.csdn.net/course/detail/36074

Python实战量化交易理财系统

https://edu.csdn.net/course/detail/35475

AUTOVACUUM

AUTOVACUUM 简介

PostgreSQL 提供了 AUTOVACUUM 的机制。

autovacuum 不仅会自动进行 VACUUM，也会自动进行 ANALYZE，以分析统计信息用于执行计划。

在 postgresql.conf 中，autovacuum 参数已默认打开。

|  | autovacuum = on |

autovacuum 打开后，会有一个 autovacuum launcher 进程

|  | $ ps -ef|grep postgres|grep autovacuum|grep -v grep |
|  | postgres 28398 28392 0 Nov13 ? 00:00:19 postgres: autovacuum launcher  |

在 pg_stat_activity 也可以看到 backend_type 为 autovacuum launcher 的连接:

|  | psql -d alvindb -U postgres |
|  | alvindb=# \x |
|  | Expanded display is on. |
|  | alvindb=# SELECT * FROM pg\_stat\_activity WHERE backend\_type = 'autovacuum launcher'; |
|  | -[ RECORD 1 ]----+------------------------------ |
|  | datid |  |
|  | datname |  |
|  | pid | 28398 |
|  | usesysid |  |
|  | usename |  |
|  | application\_name |  |
|  | client\_addr |  |
|  | client\_hostname |  |
|  | client\_port |  |
|  | backend\_start | 2021-11-13 23:18:00.406618+08 |
|  | xact\_start |  |
|  | query\_start |  |
|  | state\_change |  |
|  | wait\_event\_type | Activity |
|  | wait\_event | AutoVacuumMain |
|  | state |  |
|  | backend\_xid |  |
|  | backend\_xmin |  |
|  | query |  |
|  | backend\_type | autovacuum launcher |

那么 AUTOVACUUM 多久运行一次？

autovacuum launcher 会每隔 autovacuum_naptime ，创建 autovacuum worker，检查是否需要做 autovacuum。

|  | psql -d alvindb -U postgres |
|  | alvindb=# SELECT * FROM pg\_stat\_activity WHERE backend\_type = 'autovacuum worker'; |
|  | -[ RECORD 1 ]----+------------------------------ |
|  | datid | 13220 |
|  | datname | postgres |
|  | pid | 32457 |
|  | usesysid | |
|  | usename | |
|  | application\_name | |
|  | client\_addr | |
|  | client\_hostname | |
|  | client\_port | |
|  | backend\_start | 2021-11-06 23:32:53.880281+08 |
|  | xact\_start | |
|  | query\_start | |
|  | state\_change | |
|  | wait\_event\_type | |
|  | wait\_event | |
|  | state | |
|  | backend\_xid | |
|  | backend\_xmin | |
|  | query | |
|  | backend\_type | autovacuum worker |

autovacuum_naptime 默认为 1min：

|  | #autovacuum\_naptime = 1min # time between autovacuum runs |

autovacuum 又是根据什么标准决定是否进行 VACUUM 和 ANALYZE 呢?

当 autovacuum worker 检查到，

dead tuples 大于 vacuum threshold 时，会自动进行 VACUUM。

vacuum threshold 公式如下：

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

增删改的行数据大于 analyze threshold 时，会自动进行 ANALYZE。

analyze threshold 公式如下：

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

对应 postgresql.conf 中相关参数如下：

|  | #autovacuum\_vacuum\_threshold = 50 # min number of row updates before vacuum |
|  | #autovacuum\_analyze\_threshold = 50 # min number of row updates before analyze |
|  | #autovacuum\_vacuum\_scale\_factor = 0.2 # fraction of table size before vacuum |
|  | #autovacuum\_analyze\_scale\_factor = 0.1 # fraction of table size before analyze |

dead tuples 为 pg_stat_user_tables.n_dead_tup(Estimated number of dead rows)

|  | alvindb=> SELECT * FROM pg\_stat\_user\_tables WHERE schemaname = 'alvin' AND relname = 'tb\_test\_vacuum'; |
|  | -[ RECORD 1 ]-------+--------------- |
|  | relid | 37409 |
|  | schemaname | alvin |
|  | relname | tb\_test\_vacuum |
|  | seq\_scan | 2 |
|  | seq\_tup\_read | 0 |
|  | idx\_scan | 0 |
|  | idx\_tup\_fetch | 0 |
|  | n\_tup\_ins | 0 |
|  | n\_tup\_upd | 0 |
|  | n\_tup\_del | 0 |
|  | n\_tup\_hot\_upd | 0 |
|  | n\_live\_tup | 0 |
|  | n\_dead\_tup | 0 |
|  | n\_mod\_since\_analyze | 0 |
|  | last\_vacuum | |
|  | last\_autovacuum | |
|  | last\_analyze | |
|  | last\_autoanalyze | |
|  | vacuum\_count | 0 |
|  | autovacuum\_count | 0 |
|  | analyze\_count | 0 |
|  | autoanalyze\_count | 0 |

那么 number of tuples 是哪个列的值？是 pg_stat_user_tables.n_live_tup(Estimate number of live rows)？还是实际的 count 值?

其实是 pg_class.reltuples (Estimate number of live rows in the table used by the planner)。

|  | alvindb=> SELECT u.schemaname,u.relname,c.reltuples,u.n\_live\_tup,u.n\_mod\_since\_analyze,u.n\_dead\_tup,u.last\_autoanalyze,u.last\_autovacuum |
|  | FROM |
|  |  pg\_stat\_user\_tables u, pg\_class c, pg\_namespace n |
|  | WHERE n.oid = c.relnamespace |
|  | AND c.relname = u.relname |
|  | AND n.nspname = u.schemaname |
|  | AND u.schemaname = 'alvin' |
|  | AND u.relname = 'tb\_test\_vacuum' |
|  | -[ RECORD 1 ]-------+--------------- |
|  | schemaname | alvin |
|  | relname | tb\_test\_vacuum |
|  | reltuples | 0 |
|  | n\_live\_tup | 0 |
|  | n\_mod\_since\_analyze | 0 |
|  | n\_dead\_tup | 0 |
|  | last\_autoanalyze | |
|  | last\_autovacuum | |

所以 AUTO VACUUM 具体公式如下：

|  | pg\_stat\_user\_tables.n\_dead\_tup > autovacuum\_vacuum\_threshold + autovacuum\_vacuum\_scale\_factor * pg\_class.reltuples |

同理，AUTO ANALYZE 具体公式如下：

|  | pg\_stat\_user\_tables.n\_mod\_since\_analyze > autovacuum\_analyze\_threshold + autovacuum\_analyze\_scale\_factor * pg\_class.reltuples |

精准触发 AUTOVACUUM

下面实测一下 autovacuum。为了测试方便，autovacuum_naptime 临时修改为 5s，这样触发了临界条件，只需要等 5s 就能看到效果，而不是等 1min。

修改参数如下:

|  | autovacuum\_naptime = 5s |
|  | autovacuum\_vacuum\_threshold = 100 # min number of row updates before vacuum |
|  | autovacuum\_analyze\_threshold = 100 # min number of row updates before analyze |
|  | autovacuum\_vacuum\_scale\_factor = 0.2 # fraction of table size before vacuum |
|  | autovacuum\_analyze\_scale\_factor = 0.1 # fraction of table size before analyze |

接下来通过一步一步测试，精准触