SRE文档

深浅模式

官方文档

http://www.postgres.cn/docs/12/runtime-config-autovacuum.html

建议： ​

1、autovacuum_max_workers的建议值为CPU核数/3。CPU资源充足，I/O性能较好时，可以适当加大。

2、对于更新频繁的交易系统，如果系统资源充足，可以缩小autovacuum_vacuum_scale_factor 与 autovacuum_vacuum_threshold，让vacuum清理频繁

VACUUM命令基本上删除由更新删除或过时但未从表中物理删除的元组。这对避免腹胀很有帮助

1.查看配置参数 ​

ptgres@pg_beta data]$ psql -U postgres -h 127.0.0.1 -p 5532
psql (12.2)
输入 "help" 来获取帮助信息.

postgres=# show log_autovacuum_min_duration;
 log_autovacuum_min_duration 
-----------------------------
 1ms
(1 行记录)
ptgres@pg_beta data]$ psql -U postgres -h 127.0.0.1 -p 5532
psql (12.2)
输入 "help" 来获取帮助信息.

postgres=# show log_autovacuum_min_duration;
 log_autovacuum_min_duration 
-----------------------------
 1ms
(1 行记录)

2.修改参数 ​

ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.03;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.03;
ALTER SYSTEM SET autovacuum_vacuum_threshold = 300;
ALTER SYSTEM SET autovacuum_analyze_threshold = 300;
select pg_reload_conf ();
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.03;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.03;
ALTER SYSTEM SET autovacuum_vacuum_threshold = 300;
ALTER SYSTEM SET autovacuum_analyze_threshold = 300;
select pg_reload_conf ();
  • 查看配置
postgres=# \d pg_settings;
            视图 "pg_catalog.pg_settings"
      栏位       |  类型   | 校对规则 | 可空的 | 预设 
-----------------+---------+----------+--------+------
 name            | text    |          |        | 
 setting         | text    |          |        | 
 unit            | text    |          |        | 
 category        | text    |          |        | 
 short_desc      | text    |          |        | 
 extra_desc      | text    |          |        | 
 context         | text    |          |        | 
 vartype         | text    |          |        | 
 source          | text    |          |        | 
 min_val         | text    |          |        | 
 max_val         | text    |          |        | 
 enumvals        | text[]  |          |        | 
 boot_val        | text    |          |        | 
 reset_val       | text    |          |        | 
 sourcefile      | text    |          |        | 
 sourceline      | integer |          |        | 
 pending_restart | boolean |          |        |
 
 #查看位置
 postgres=# select name,setting from pg_settings where name like 'log%' ;
            name             |                    setting                     
-----------------------------+------------------------------------------------
 log_autovacuum_min_duration | 1
 log_checkpoints             | on
 log_connections             | on
 log_destination             | csvlog
 log_directory               | log
 log_disconnections          | on
 log_duration                | on
 log_error_verbosity         | default
 log_executor_stats          | off
 log_file_mode               | 0600
 log_filename                | postgresql-%Y-%m-%d_%H%.log
 log_hostname                | off
 log_line_prefix             | %t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h
 log_lock_waits              | on
 log_min_duration_statement  | 30000
 log_min_error_statement     | error
 log_min_messages            | warning
 log_parser_stats            | off
 log_planner_stats           | off
 log_replication_commands    | off
 log_rotation_age            | 1440
 log_rotation_size           | 10240
 log_statement               | ddl
 log_statement_stats         | off
 log_temp_files              | 1
 log_timezone                | PRC
 log_transaction_sample_rate | 0
 log_truncate_on_rotation    | on
 logging_collector           | on
(29 行记录)
postgres=# \d pg_settings;
            视图 "pg_catalog.pg_settings"
      栏位       |  类型   | 校对规则 | 可空的 | 预设 
-----------------+---------+----------+--------+------
 name            | text    |          |        | 
 setting         | text    |          |        | 
 unit            | text    |          |        | 
 category        | text    |          |        | 
 short_desc      | text    |          |        | 
 extra_desc      | text    |          |        | 
 context         | text    |          |        | 
 vartype         | text    |          |        | 
 source          | text    |          |        | 
 min_val         | text    |          |        | 
 max_val         | text    |          |        | 
 enumvals        | text[]  |          |        | 
 boot_val        | text    |          |        | 
 reset_val       | text    |          |        | 
 sourcefile      | text    |          |        | 
 sourceline      | integer |          |        | 
 pending_restart | boolean |          |        |
 
 #查看位置
 postgres=# select name,setting from pg_settings where name like 'log%' ;
            name             |                    setting                     
-----------------------------+------------------------------------------------
 log_autovacuum_min_duration | 1
 log_checkpoints             | on
 log_connections             | on
 log_destination             | csvlog
 log_directory               | log
 log_disconnections          | on
 log_duration                | on
 log_error_verbosity         | default
 log_executor_stats          | off
 log_file_mode               | 0600
 log_filename                | postgresql-%Y-%m-%d_%H%.log
 log_hostname                | off
 log_line_prefix             | %t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h
 log_lock_waits              | on
 log_min_duration_statement  | 30000
 log_min_error_statement     | error
 log_min_messages            | warning
 log_parser_stats            | off
 log_planner_stats           | off
 log_replication_commands    | off
 log_rotation_age            | 1440
 log_rotation_size           | 10240
 log_statement               | ddl
 log_statement_stats         | off
 log_temp_files              | 1
 log_timezone                | PRC
 log_transaction_sample_rate | 0
 log_truncate_on_rotation    | on
 logging_collector           | on
(29 行记录)