注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

Autovacuum 参数解释  

2011-03-14 16:25:16|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

       autovacuum 是 postgresql 里非常重要的一个服务端进程。它能够自动地执行,在一定
条件下自动地对 dead tuples 进行清理并对表进行分析, 这篇主要是讲述下 autovacuum
的 一些参数的含义。

--Autovaccum相关参数
1 autovacuum (boolean)
     autovacuum参数控制 autovacuum 进程是否打开,默认为 "on"

2 log_autovacuum_min_duration(integer)
    这个参数用来记录 autovacuum 的执行时间,当 autovaccum 的执行时间超过 log_autovacuum_min_duration
    参数设置时,则autovacuum信息记录到日志里,默认为 "-1", 表示不记录。 

3 autovacuum_max_workers (integer)
  指定同时运行的 最大的 autovacuum 进程,默认为3个。
 
4 autovacuum_naptime (integer) 
   指定 autovacuum 进程运行的最小间隔,默认为 1 min。也就是说当前一个 autovacuum 进程运行完成后,
   第二个 autovacuum 进程至少在一分钟后才会运行。

5 autovacuum_vacuum_threshold (integer)
  autovacuum 进程进行vacuum 操作的阀值条件一,(指修改,删除的记录数。)

6 autovacuum_analyze_threshold (integer)
  autovacuum 进程进行 analyze 操作的阀值条件一,(指插入,修改,删除的记录数。)
  
7 autovacuum_vacuum_scale_factor (floating point)  
  autovacuum因子, autovacuum 进程进行 vacuum 操作的阀值条件二,,默认为 0.2
  autovacuum进程进行 vacuum 触发条件
  表上(update,delte 记录) >= autovacuum_vacuum_scale_factor* reltuples(表上记录数) +
  autovacuum_vacuum_threshold

  
8 autovacuum_analyze_scale_factor (floating point)   
  autoanalyze 因子,autovacuum 进程进行 analyze  操作的阀值条件二,,默认为 0.1
  autovacuum进程进行 analyze  触发条件
  表上(insert,update,delte 记录) >= autovacuum_analyze_scale_factor* reltuples(表上记录数) +
  autovacuum_analyze_threshold


 9 autovacuum_freeze_max_age (integer)
  指定表上事务的最大年龄,默认为2亿,达到这个阀值将触发 autovacuum进程,从而避免 wraparound。
  表上的事务年龄可以通过 pg_class.relfrozenxid  查询。
 
--例如,查询表 test_1 的事务年龄
 skytf=> select relname,age(relfrozenxid) from pg_class where relname='test_1';
 relname |   age   
---------+----------
 test_1  | 14208876
(1 row)
 
10 autovacuum_vacuum_cost_delay (integer)
   当autovacuum进程即将执行时,对 vacuum 执行 cost 进行评估,如果超过 autovacuum_vacuum_cost_limit
   设置值时,则延迟,这个延迟的时间即为 autovacuum_vacuum_cost_delay。如果值为 -1, 表示使用
   vacuum_cost_delay 值,默认值为 20 ms
  
11 autovacuum_vacuum_cost_limit (integer)   
     这个值为 autovacuum 进程的评估阀值, 默认为 -1, 表示使用 "vacuum_cost_limit " 值,如果在执行
autovacuum 进程期间评估的 cost 超过 autovacuum_vacuum_cost_limit, 则 autovacuum 进程则会休眠。

--附:官网解释
1 autovacuum (boolean)
Controls whether the server should run the autovacuum launcher daemon. This is on by default; however, track_counts must also be turned on for autovacuum to work. This parameter can only be set in the postgresql.conf file or on the server command line.

Note that even when this parameter is disabled, the system will launch autovacuum processes if necessary to prevent transaction ID wraparound. See Section 23.1.4, “Preventing Transaction ID Wraparound Failures” for more information.

2 log_autovacuum_min_duration (integer)
Causes each action executed by autovacuum to be logged if it ran for at least the specified number of milliseconds. Setting this to zero logs all autovacuum actions. Minus-one (the default) disables logging autovacuum actions. For example, if you set this to 250ms then all automatic vacuums and analyzes that run 250ms or longer will be logged. Enabling this parameter can be helpful in tracking autovacuum activity. This setting can only be set in the postgresql.conf file or on the server command line.

3 autovacuum_max_workers (integer)
Specifies the maximum number of autovacuum processes (other than the autovacuum launcher) which may be running at any one time. The default is three. This parameter can only be set in the postgresql.conf file or on the server command line.

4 autovacuum_naptime (integer)
Specifies the minimum delay between autovacuum runs on any given database. In each round the daemon examines the database and issues VACUUM and ANALYZE commands as needed for tables in that database. The delay is measured in seconds, and the default is one minute (1m). This parameter can only be set in the postgresql.conf file or on the server command line.

5 autovacuum_vacuum_threshold (integer)
Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.

6 autovacuum_analyze_threshold (integer)
Specifies the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.

7 autovacuum_vacuum_scale_factor (floating point)
Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of table size). This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.

8 autovacuum_analyze_scale_factor (floating point)
Specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE. The default is 0.1 (10% of table size). This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.

9 autovacuum_freeze_max_age (integer)
Specifies the maximum age (in transactions) that a table is pg_class.relfrozenxid field can attain before a VACUUM operation is forced to prevent transaction ID wraparound within the table. Note that the system will launch autovacuum processes to prevent wraparound even when autovacuum is otherwise disabled. The default is 200 million transactions. This parameter can only be set at server start, but the setting can be reduced for individual tables by changing storage parameters. For more information see Section 23.1.4, “Preventing Transaction ID Wraparound Failures”.

10 autovacuum_vacuum_cost_delay (integer)
Specifies the cost delay value that will be used in automatic VACUUM operations. If -1 is specified, the regular vacuum_cost_delay value will be used. The default value is 20 milliseconds. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.

11 autovacuum_vacuum_cost_limit (integer)
Specifies the cost limit value that will be used in automatic VACUUM operations. If -1 is specified (which is the default), the regular vacuum_cost_limit value will be used. Note that the value is distributed proportionally among the running autovacuum workers, if there is more than one, so that the sum of the limits of each worker never exceeds the limit on this variable. This parameter can only be set in the postgresql.conf file or on the server command line. This setting can be overridden for individual tables by changing storage parameters.

  评论这张
 
阅读(27742)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016