ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(142) - pgAdmin(Monitoring PostgreSQL VACUUM processes)

PostgreSQL DBA(142) - pgAdmin(Monitoring PostgreSQL VACUUM processes)

原创 PostgreSQL 作者:husthxd 时间:2019-12-10 15:56:49 0 删除 编辑

本节介绍了PostgreSQL中如何监控VACUUM的处理过程。

概览
PG的MVCC要求“过期”的数据不能马上被物理清除,而是标记为dead rows,这些dead rows后续会通过vacuuming过程清理。
vacuuming通过以下方式让数据库保持健康:
1.标记dead rows可用于存储新数据,这样可以避免不必要的磁盘浪费以及可以跳过dead rows以提升顺序扫描的性能;
2.更新vm(用于跟踪过期或已废弃的数据,反应在pages上)。这可以提升index-only scans的性能;
3.避免出现事务ID回卷失败。
PG提供了autovacuum机制,通过周期性的运行ANALYZE来收集最近频繁更新的数据表统计信息。

监控指标
为了让VACUUMs平滑运行,应该监控以下几个指标:
1.dead rows
2.table disk usage
3.VACUUM/AUTOVACUUM最近执行的时间
4.监控vacuum full

dead rows
PG提供了pg_stat_user_tables视图用于监控dead rows

[local:/data/run/pg12]:5120 pg12@testdb=# \d pg_stat_user_tables
                      View "pg_catalog.pg_stat_user_tables"
       Column        |           Type           | Collation | Nullable | Default 
---------------------+--------------------------+-----------+----------+---------
 relid               | oid                      |           |          | 
 schemaname          | name                     |           |          | 
 relname             | name                     |           |          | 
 seq_scan            | bigint                   |           |          | 
 seq_tup_read        | bigint                   |           |          | 
 idx_scan            | bigint                   |           |          | 
 idx_tup_fetch       | bigint                   |           |          | 
 n_tup_ins           | bigint                   |           |          | 
 n_tup_upd           | bigint                   |           |          | 
 n_tup_del           | bigint                   |           |          | 
 n_tup_hot_upd       | bigint                   |           |          | 
 n_live_tup          | bigint                   |           |          | 
 n_dead_tup          | bigint                   |           |          | 
 n_mod_since_analyze | bigint                   |           |          | 
 last_vacuum         | timestamp with time zone |           |          | 
 last_autovacuum     | timestamp with time zone |           |          | 
 last_analyze        | timestamp with time zone |           |          | 
 last_autoanalyze    | timestamp with time zone |           |          | 
 vacuum_count        | bigint                   |           |          | 
 autovacuum_count    | bigint                   |           |          | 
 analyze_count       | bigint                   |           |          | 
 autoanalyze_count   | bigint                   |           |          | 
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 2;
UPDATE 20000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname,relname, n_dead_tup FROM pg_stat_user_tables;
 schemaname |      relname       | n_dead_tup 
------------+--------------------+------------
 public     | tbl                |          0
 public     | t2                 |          0
 public     | b                  |          0
 public     | a                  |          0
 public     | rel                |          0
 public     | t_count            |          0
 public     | t_big_autovacuum_1 |          0
 public     | t_autovacuum_1     |          0
 public     | t1                 |      20000
(9 rows)

监控每张表的dead rows,特别是监控频繁更新的表上,这样有助于DBA确定VACUUM进程是否已有效的周期性的清除这些dead rows。

Table disk usage
在出现dead rows时,磁盘空间会逐步增大,vacuuming执行后可标记dead rows为空闲空间,通过监控空间的变化

[local:/data/run/pg12]:5120 pg12@testdb=# SELECT 
       relname AS "table_name", 
       pg_size_pretty(pg_table_size(C.oid)) AS "table_size" 
FROM 
       pg_class C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r') 
ORDER BY pg_table_size(C.oid) 
DESC;
     table_name     | table_size 
--------------------+------------
 rel                | 845 MB
 t_big_autovacuum_1 | 498 MB
 tbl                | 100 MB
 a                  | 65 MB
 b                  | 65 MB
 t1                 | 1456 kB
 t_autovacuum_1     | 504 kB
 t2                 | 360 kB
 t_count            | 64 kB
(9 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 4;
UPDATE 20000
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 5;
UPDATE 20000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT 
       relname AS "table_name", 
       pg_size_pretty(pg_table_size(C.oid)) AS "table_size" 
FROM 
       pg_class C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r') 
ORDER BY pg_table_size(C.oid) 
DESC;
     table_name     | table_size 
--------------------+------------
 rel                | 845 MB
 t_big_autovacuum_1 | 498 MB
 tbl                | 100 MB
 a                  | 65 MB
 b                  | 65 MB
 t1                 | 2864 kB
 t_autovacuum_1     | 504 kB
 t2                 | 360 kB
 t_count            | 64 kB
(9 rows)

对t1执行全量更新,然后执行vacuum t1后再次插入等量的数据

[local:/data/run/pg12]:5120 pg12@testdb=# vacuum t1;
VACUUM
[local:/data/run/pg12]:5120 pg12@testdb=# select count(*) from t1;
 count 
-------
 20000
(1 row)
[local:/data/run/pg12]:5120 pg12@testdb=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
[local:/data/run/pg12]:5120 pg12@testdb=# insert into t1 select generate_series(1,20000);
INSERT 0 20000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT 
       relname AS "table_name", 
       pg_size_pretty(pg_table_size(C.oid)) AS "table_size" 
FROM 
       pg_class C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r') 
ORDER BY pg_table_size(C.oid) 
DESC;
     table_name     | table_size 
--------------------+------------
 rel                | 845 MB
 t_big_autovacuum_1 | 498 MB
 tbl                | 100 MB
 a                  | 65 MB
 b                  | 65 MB
 t1                 | 2864 kB
 t_autovacuum_1     | 504 kB
 t2                 | 360 kB
 t_count            | 64 kB
(9 rows)

可以看到table占用的空间并没有出现变化,原因是新的rows使用了dead rows的空间。
如不执行vacuum直接插入,则明显可以看到table size的变化。

[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 10;
UPDATE 60000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT                
       relname AS "table_name", 
       pg_size_pretty(pg_table_size(C.oid)) AS "table_size" 
FROM 
       pg_class C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r') 
ORDER BY pg_table_size(C.oid) 
DESC;
     table_name     | table_size 
--------------------+------------
 rel                | 845 MB
 t_big_autovacuum_1 | 498 MB
 tbl                | 100 MB
 a                  | 65 MB
 b                  | 65 MB
 t1                 | 4288 kB -->这是原占用空间
 t_autovacuum_1     | 504 kB
 t2                 | 360 kB
 t_count            | 64 kB
(9 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# insert into t1 select generate_series(1,20000);
INSERT 0 20000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT                                         
       relname AS "table_name", 
       pg_size_pretty(pg_table_size(C.oid)) AS "table_size" 
FROM 
       pg_class C 
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r') 
ORDER BY pg_table_size(C.oid) 
DESC;
     table_name     | table_size 
--------------------+------------
 rel                | 845 MB
 t_big_autovacuum_1 | 498 MB
 tbl                | 100 MB
 a                  | 65 MB
 b                  | 65 MB
 t1                 | 4992 kB  --> 新增占用空间
 t_autovacuum_1     | 504 kB
 t2                 | 360 kB
 t_count            | 64 kB
(9 rows)

Last time (auto)vacuum ran
PG提供了pg_stat_user_tables 视图用于监控最近一次vacuum运行的时间。

[local:/data/run/pg12]:5120 pg12@testdb=# \d pg_stat_user_tables
                      View "pg_catalog.pg_stat_user_tables"
       Column        |           Type           | Collation | Nullable | Default 
---------------------+--------------------------+-----------+----------+---------
 relid               | oid                      |           |          | 
 schemaname          | name                     |           |          | 
 relname             | name                     |           |          | 
 seq_scan            | bigint                   |           |          | 
 seq_tup_read        | bigint                   |           |          | 
 idx_scan            | bigint                   |           |          | 
 idx_tup_fetch       | bigint                   |           |          | 
 n_tup_ins           | bigint                   |           |          | 
 n_tup_upd           | bigint                   |           |          | 
 n_tup_del           | bigint                   |           |          | 
 n_tup_hot_upd       | bigint                   |           |          | 
 n_live_tup          | bigint                   |           |          | 
 n_dead_tup          | bigint                   |           |          | 
 n_mod_since_analyze | bigint                   |           |          | 
 last_vacuum         | timestamp with time zone |           |          | 
 last_autovacuum     | timestamp with time zone |           |          | 
 last_analyze        | timestamp with time zone |           |          | 
 last_autoanalyze    | timestamp with time zone |           |          | 
 vacuum_count        | bigint                   |           |          | 
 autovacuum_count    | bigint                   |           |          | 
 analyze_count       | bigint                   |           |          | 
 autoanalyze_count   | bigint                   |           |          | 
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       |          last_vacuum          |        last_autovacuum        
------------+--------------------+-------------------------------+-------------------------------
 public     | tbl                |                               | 
 public     | t2                 |                               | 
 public     | b                  |                               | 
 public     | a                  |                               | 
 public     | rel                |                               | 
 public     | t_count            |                               | 
 public     | t_big_autovacuum_1 |                               | 
 public     | t_autovacuum_1     |                               | 
 public     | t1                 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:32:02.639873+08
(9 rows)

可以看到最近一次的vacumm是14:29:31,而最近一次的autovacuum是14:32:02,autovacuum默认60s执行一次,其他没有变化的表PG不会执行autovacuum。

[local:/data/run/pg12]:5120 pg12@testdb=# select name,setting from pg_settings where name like '%autovacuum%';
                name                 |  setting  
-------------------------------------+-----------
 autovacuum                          | on
 autovacuum_analyze_scale_factor     | 0.1
 autovacuum_analyze_threshold        | 50
 autovacuum_freeze_max_age           | 200000000
 autovacuum_max_workers              | 3
 autovacuum_multixact_freeze_max_age | 400000000
 autovacuum_naptime                  | 60 --> 60s
 autovacuum_vacuum_cost_delay        | 2
 autovacuum_vacuum_cost_limit        | -1
 autovacuum_vacuum_scale_factor      | 0.2
 autovacuum_vacuum_threshold         | 50
 autovacuum_work_mem                 | -1
 log_autovacuum_min_duration         | -1
(13 rows)

执行update操作,60s后再次查询,发现last_autovacuum已更新。

[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 1;
UPDATE 80000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       |          last_vacuum          |        last_autovacuum        
------------+--------------------+-------------------------------+-------------------------------
 public     | tbl                |                               | 
 public     | t2                 |                               | 
 public     | b                  |                               | 
 public     | a                  |                               | 
 public     | rel                |                               | 
 public     | t_count            |                               | 
 public     | t_big_autovacuum_1 |                               | 
 public     | t_autovacuum_1     |                               | 
 public     | t1                 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:38:02.771566+08
(9 rows)

监控vacuum full
通过视图pg_stat_progress_vacuum可监控vacuum full的进度

[local:/data/run/pg12]:5120 pg12@testdb=# \d pg_stat_progress_vacuum
           View "pg_catalog.pg_stat_progress_vacuum"
       Column       |  Type   | Collation | Nullable | Default 
--------------------+---------+-----------+----------+---------
 pid                | integer |           |          | 
 datid              | oid     |           |          | 
 datname            | name    |           |          | 
 relid              | oid     |           |          | 
 phase              | text    |           |          | 
 heap_blks_total    | bigint  |           |          | 
 heap_blks_scanned  | bigint  |           |          | 
 heap_blks_vacuumed | bigint  |           |          | 
 index_vacuum_count | bigint  |           |          | 
 max_dead_tuples    | bigint  |           |          | 
 num_dead_tuples    | bigint  |           |          | 
[local:/data/run/pg12]:5120 pg12@testdb=#

VACUUM的相关主题
如果上述指标提示VACUUMs没有正常执行,可以通过查询设置可发现问题所在,包括:
1.The autovacuum process is disabled on your database
2.The autovacuum process is disabled on one or more tables
3.Autovacuuming settings aren’t keeping pace with updates
4.Lock conflicts
5.Long-running open transactions

1.The autovacuum process is disabled on your database
通过执行ps -axww | grep autovacuum命令可监控autovacuum是否正在运行

[root@localhost ~]# ps -axww | grep autovacuum
55958 ?        Ss     0:00 postgres: autovacuum launcher  
56057 pts/4    S+     0:00 grep --color=auto autovacuum
[root@localhost ~]#

同时亦可通过查询pg_settings获得

[local:/data/run/pg12]:5120 pg12@testdb=# SELECT name, setting FROM pg_settings WHERE name='autovacuum';
    name    | setting 
------------+---------
 autovacuum | on
(1 row)

如autovacuum已开启,但结果没有如我们预期,那么问题可能出现在statistics collector上面,autovacuum依赖statistics collector用于确定何时以及间隔多少时间应该运行。通常来说,statistics collector应启用,但如果禁用此项,对autovacuum的正常运行会有较大影响。通过检查track_counts配置项来检查statistics collector是否启用。

[local:/data/run/pg12]:5120 pg12@testdb=# SELECT name, setting FROM pg_settings WHERE name='track_counts';
     name     | setting 
--------------+---------
 track_counts | on
(1 row)
[local:/data/run/pg12]:5120 pg12@testdb=#

如track_counts为OFF,则statistics collector不会更新dead rows信息,而该项是autovacuum所依赖的信息。

[local:/data/run/pg12]:5120 pg12@testdb=# set track_counts=off;
SET
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 2;
UPDATE 80000
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 3;
UPDATE 80000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       | n_dead_tup |          last_vacuum          |        last_autovacuum        
------------+--------------------+------------+-------------------------------+-------------------------------
 public     | tbl                |          0 |                               | 
 public     | t2                 |          0 |                               | 
 public     | b                  |          0 |                               | 
 public     | a                  |          0 |                               | 
 public     | rel                |          0 |                               | 
 public     | t_count            |          0 |                               | 
 public     | t_big_autovacuum_1 |          0 |                               | 
 public     | t_autovacuum_1     |          0 |                               | 
 public     | t1                 |          0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:38:02.771566+08
(9 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#

手工把track_counts设置为off,更新t1,查询pg_stat_user_tables发现n_dead_tup没有统计dead rows,导致autovacuum并没有对t1表进行“vacuum”。
手工设置track_counts为on,但没有触发统计信息的更新,退出psql重新登录,更新数据表后才会出现新的统计信息

[local:/data/run/pg12]:5120 pg12@testdb=# set track_counts=on;
SET
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       | n_dead_tup |          last_vacuum          |        last_autovacuum        
------------+--------------------+------------+-------------------------------+-------------------------------
 public     | tbl                |          0 |                               | 
 public     | t2                 |          0 |                               | 
 public     | b                  |          0 |                               | 
 public     | a                  |          0 |                               | 
 public     | rel                |          0 |                               | 
 public     | t_count            |          0 |                               | 
 public     | t_big_autovacuum_1 |          0 |                               | 
 public     | t_autovacuum_1     |          0 |                               | 
 public     | t1                 |          0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:38:02.771566+08
(9 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# \q
[pg12@localhost ~]$ psql
Expanded display is used automatically.
psql (12.1)
Type "help" for help.
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 100;
UPDATE 80000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       | n_dead_tup |          last_vacuum          |        last_autovacuum        
------------+--------------------+------------+-------------------------------+-------------------------------
 public     | tbl                |          0 |                               | 
 public     | t2                 |          0 |                               | 
 public     | b                  |          0 |                               | 
 public     | a                  |          0 |                               | 
 public     | rel                |          0 |                               | 
 public     | t_count            |          0 |                               | 
 public     | t_big_autovacuum_1 |          0 |                               | 
 public     | t_autovacuum_1     |          0 |                               | 
 public     | t1                 |      79868 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:38:02.771566+08
(9 rows)

2.The autovacuum process is disabled on one or more tables
PG可在表级别上设置autovacuum是否生效

[local:/data/run/pg12]:5120 pg12@testdb=# create table t2(id int);
CREATE TABLE
[local:/data/run/pg12]:5120 pg12@testdb=# alter table t2 SET (autovacuum_enabled = false);
ALTER TABLE
[local:/data/run/pg12]:5120 pg12@testdb=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT reloptions FROM pg_class WHERE relname='t2';
         reloptions         
----------------------------
 {autovacuum_enabled=false}
(1 row)
[local:/data/run/pg12]:5120 pg12@testdb=#

在t2上插入数据并更新

[local:/data/run/pg12]:5120 pg12@testdb=# insert into t2 select generate_series(1,100000);
INSERT 0 100000
[local:/data/run/pg12]:5120 pg12@testdb=# update t2 set id = 1;
UPDATE 100000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       | n_dead_tup |          last_vacuum          |        last_autovacuum        
------------+--------------------+------------+-------------------------------+-------------------------------
 public     | tbl                |          0 |                               | 
 public     | b                  |          0 |                               | 
 public     | a                  |          0 |                               | 
 public     | rel                |          0 |                               | 
 public     | t2                 |     100000 |                               | 
 public     | t_count            |          0 |                               | 
 public     | t_big_autovacuum_1 |          0 |                               | 
 public     | t_autovacuum_1     |          0 |                               | 
 public     | t1                 |          0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08
(9 rows)

t2的dead rows为100000,但60s超时后,autovacuum并没有对该表进行vacuum处理。

[local:/data/run/pg12]:5120 pg12@testdb=# \! date
Tue Dec 10 15:06:54 CST 2019
[local:/data/run/pg12]:5120 pg12@testdb=# \! date
Tue Dec 10 15:08:28 CST 2019
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       | n_dead_tup |          last_vacuum          |        last_autovacuum        
------------+--------------------+------------+-------------------------------+-------------------------------
 public     | tbl                |          0 |                               | 
 public     | b                  |          0 |                               | 
 public     | a                  |          0 |                               | 
 public     | rel                |          0 |                               | 
 public     | t2                 |     100000 |                               | 
 public     | t_count            |          0 |                               | 
 public     | t_big_autovacuum_1 |          0 |                               | 
 public     | t_autovacuum_1     |          0 |                               | 
 public     | t1                 |          0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08
(9 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#

设置数据表autovacuum_enabled为true,等待60s,这时候发现t2已被vacuum

[local:/data/run/pg12]:5120 pg12@testdb=# alter table t2 SET (autovacuum_enabled = true);
ALTER TABLE
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       | n_dead_tup |          last_vacuum          |        last_autovacuum        
------------+--------------------+------------+-------------------------------+-------------------------------
 public     | tbl                |          0 |                               | 
 public     | b                  |          0 |                               | 
 public     | a                  |          0 |                               | 
 public     | rel                |          0 |                               | 
 public     | t2                 |     100000 |                               | 
 public     | t_count            |          0 |                               | 
 public     | t_big_autovacuum_1 |          0 |                               | 
 public     | t_autovacuum_1     |          0 |                               | 
 public     | t1                 |          0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08
(9 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# \! date
Tue Dec 10 15:09:05 CST 2019
[local:/data/run/pg12]:5120 pg12@testdb=# 
[local:/data/run/pg12]:5120 pg12@testdb=# \! date
Tue Dec 10 15:10:26 CST 2019
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       | n_dead_tup |          last_vacuum          |        last_autovacuum        
------------+--------------------+------------+-------------------------------+-------------------------------
 public     | tbl                |          0 |                               | 
 public     | b                  |          0 |                               | 
 public     | a                  |          0 |                               | 
 public     | rel                |          0 |                               | 
 public     | t2                 |          0 |                               | 2019-12-10 15:09:57.621123+08
 public     | t_count            |          0 |                               | 
 public     | t_big_autovacuum_1 |          0 |                               | 
 public     | t_autovacuum_1     |          0 |                               | 
 public     | t1                 |          0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08
(9 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#

3.Autovacuuming settings aren’t keeping pace with updates
如果autovacuum已启用,但没有我们想象中那么频繁的执行,这时候需要调整默认的配置选项。

[local:/data/run/pg12]:5120 pg12@testdb=# SELECT name,setting,boot_val,pending_restart from pg_settings where category like 'Autovacuum';
                name                 |  setting  | boot_val  | pending_restart 
-------------------------------------+-----------+-----------+-----------------
 autovacuum                          | on        | on        | f
 autovacuum_analyze_scale_factor     | 0.1       | 0.1       | f
 autovacuum_analyze_threshold        | 50        | 50        | f
 autovacuum_freeze_max_age           | 200000000 | 200000000 | f
 autovacuum_max_workers              | 3         | 3         | f
 autovacuum_multixact_freeze_max_age | 400000000 | 400000000 | f
 autovacuum_naptime                  | 60        | 60        | f
 autovacuum_vacuum_cost_delay        | 2         | 2         | f
 autovacuum_vacuum_cost_limit        | -1        | -1        | f
 autovacuum_vacuum_scale_factor      | 0.2       | 0.2       | f
 autovacuum_vacuum_threshold         | 50        | 50        | f
(11 rows)

查询pg_settings,其中setting为当前配置的值,boot_val是默认值,可以看到当前库的配置与默认值一样。
确定autovacuum运行频度的参数有:
1.autovacuum_vacuum_threshold,触发阈值,默认为50
2.autovacuum_vacuum_scale_factor,触发dead rows率,默认为0.2,即20%
3.表的估算行数,存储在pg_class.reltuples中
PG结合上述3个参数来确定autovacuum是否需要执行,计算公式如下:

autovacuuming threshold = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * estimated number of rows in the table)

通过调整参数,如减少autovacuum_vacuum_scale_factor可触发VACUUMs运行得更频繁。
PG还提供了log_autovacuum_min_duration参数来诊断autovacuum的运行间隔时间,如超过该时间设置则会记录在日志中,这样有助于诊断autovacuum的设定是否合理。

4.Lock conflicts
vacuum的执行需要持有SHARE UPDATE EXCLUSIVE lock,如有session持有的锁(SHARE UPDATE EXCLUSIVE,SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE)与其冲突,则无法执行vacuum。
更新t2

[local:/data/run/pg12]:5120 pg12@testdb=#  begin;
BEGIN
[local:/data/run/pg12]:5120 pg12@testdb=#*  update t2 set id = 10;
UPDATE 100000
[local:/data/run/pg12]:5120 pg12@testdb=#* commit;
COMMIT
[local:/data/run/pg12]:5120 pg12@testdb=# 
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       | n_dead_tup |          last_vacuum          |        last_autovacuum        
------------+--------------------+------------+-------------------------------+-------------------------------
 public     | tbl                |          0 |                               | 
 public     | b                  |          0 |                               | 
 public     | a                  |          0 |                               | 
 public     | rel                |          0 |                               | 
 public     | t2                 |     100000 |                               | 2019-12-10 15:09:57.621123+08
 public     | t_count            |          0 |                               | 
 public     | t_big_autovacuum_1 |          0 |                               | 
 public     | t_autovacuum_1     |          0 |                               | 
 public     | t1                 |          0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08
(9 rows)

开另外一个窗口,lock表

[local:/data/run/pg12]:5120 pg12@testdb=# begin;
BEGIN
[local:/data/run/pg12]:5120 pg12@testdb=#* lock t2 in SHARE UPDATE EXCLUSIVE mode;
LOCK TABLE
[local:/data/run/pg12]:5120 pg12@testdb=#*

autovacuum由于无法获取锁,因此无法对表进行vacuum

[local:/data/run/pg12]:5120 pg12@testdb=# select pid,locktype,relation::regclass,mode,granted from pg_locks where pid <> pg_backend_pid();
  pid  |  locktype  | relation |           mode           | granted 
-------+------------+----------+--------------------------+---------
 58050 | virtualxid |          | ExclusiveLock            | t
 58050 | relation   | t2       | ShareUpdateExclusiveLock | t
(2 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       | n_dead_tup |          last_vacuum          |        last_autovacuum        
------------+--------------------+------------+-------------------------------+-------------------------------
 public     | tbl                |          0 |                               | 
 public     | b                  |          0 |                               | 
 public     | a                  |          0 |                               | 
 public     | rel                |          0 |                               | 
 public     | t2                 |     100000 |                               | 2019-12-10 15:09:57.621123+08
 public     | t_count            |          0 |                               | 
 public     | t_big_autovacuum_1 |          0 |                               | 
 public     | t_autovacuum_1     |          0 |                               | 
 public     | t1                 |          0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08
(9 rows)

释放锁

[local:/data/run/pg12]:5120 pg12@testdb=#* commit;
COMMIT
[local:/data/run/pg12]:5120 pg12@testdb=#

autovacuum可正常执行,last_autovacuum已更新

[local:/data/run/pg12]:5120 pg12@testdb=# \! date 
Tue Dec 10 15:33:01 CST 2019
[local:/data/run/pg12]:5120 pg12@testdb=# \! date 
Tue Dec 10 15:33:40 CST 2019
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 schemaname |      relname       | n_dead_tup |          last_vacuum          |        last_autovacuum        
------------+--------------------+------------+-------------------------------+-------------------------------
 public     | tbl                |          0 |                               | 
 public     | b                  |          0 |                               | 
 public     | a                  |          0 |                               | 
 public     | rel                |          0 |                               | 
 public     | t2                 |          0 |                               | 2019-12-10 15:32:58.743764+08
 public     | t_count            |          0 |                               | 
 public     | t_big_autovacuum_1 |          0 |                               | 
 public     | t_autovacuum_1     |          0 |                               | 
 public     | t1                 |          0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08
(9 rows)
[local:/data/run/pg12]:5120 pg12@testdb=#

通过进程状态亦可诊断

-- session 1
[local:/data/run/pg12]:5120 pg12@testdb=# begin;
BEGIN
[local:/data/run/pg12]:5120 pg12@testdb=#* lock t2 in SHARE UPDATE EXCLUSIVE mode;
LOCK TABLE
[local:/data/run/pg12]:5120 pg12@testdb=#* 
-- session 2
[local:/data/run/pg12]:5120 pg12@testdb=# vacuum t2;
-- console
[pg12@localhost ~]$ ps -ef|grep 'waiting'
pg12     56540 55944  0 14:59 ?        00:00:01 postgres: pg12 testdb [local] VACUUM waiting
pg12     58502 53760  0 15:36 pts/2    00:00:00 grep --color=auto waiting
[pg12@localhost ~]$

进程显示为VACUUM waiting

5.Long-running open transactions
MVCC的一个副作用是vacuum不能清理那些其他事务还需要访问的过期dead rows。因此,如无必要确保事务正常完结。
通过视图pg_stat_activity可监控事务的状态

[local:/data/run/pg12]:5120 pg12@testdb=# SELECT pid,xact_start, state, usename FROM pg_stat_activity;
  pid  |          xact_start           | state  | usename 
-------+-------------------------------+--------+---------
 55958 |                               |        | 
 55960 |                               |        | pg12
 56540 | 2019-12-10 15:42:47.210597+08 | active | pg12
 58050 |                               | idle   | pg12
 55956 |                               |        | 
 55955 |                               |        | 
 55957 |                               |        | 
(7 rows)

如state列显示为disabled,则需检查系统参数track_activities

[local:/data/run/pg12]:5120 pg12@testdb=# show track_activities;
 track_activities 
------------------
 on
(1 row)
[local:/data/run/pg12]:5120 pg12@testdb=# set track_activities=off;
SET
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT pid,xact_start, state, usename FROM pg_stat_activity;
  pid  | xact_start |  state   | usename 
-------+------------+----------+---------
 55958 |            |          | 
 55960 |            |          | pg12
 56540 |            | disabled | pg12
 58050 |            | idle     | pg12
 55956 |            |          | 
 55955 |            |          | 
 55957 |            |          | 
(7 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# 
[local:/data/run/pg12]:5120 pg12@testdb=# set track_activities=on;
SET
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT pid,xact_start, state, usename FROM pg_stat_activity;
  pid  |          xact_start           | state  | usename 
-------+-------------------------------+--------+---------
 55958 |                               |        | 
 55960 |                               |        | pg12
 56540 | 2019-12-10 15:52:19.500017+08 | active | pg12
 58050 |                               | idle   | pg12
 55956 |                               |        | 
 55955 |                               |        | 
 55957 |                               |        | 
(7 rows)

对于长时间闲置的session,PG提供了参数idle_in_transaction_session_timeout 用于控制这些session,超过该参数配置的时间(以ms为单位),PG会自动终止这些session。

参考资料
Monitoring PostgreSQL VACUUM processes

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2667766/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1465
  • 访问量
    3897284