<!--

$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.240 2004/02/1706:28:05 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.241 2004/02/1707:36:47 tgl Exp $

-->

<Chapter Id="runtime">

<title>Cost-Based Vacuum Delay</title>

<para>

During the execution of <command>VACUUM</command>,

<command>VACUUM FULL</command>and <command>ANALYZE</command>,

the systemmantains an internal counter that keeps track of the

cost of the various I/O operations that are performed. When the

accumulated cost reaches a limit

(specified by <varname>vacuum_cost_limit</varname>), thebackend performing

the operation will sleep for a while (specified by

During the execution of <command>VACUUM</command>

and <command>ANALYZE</command> commands,

the systemmaintains an internal counter that keeps track of the

estimatedcost of the various I/O operations that are performed.

When theaccumulated cost reaches a limit

(specified by <varname>vacuum_cost_limit</varname>), theprocess

performingthe operation will sleep for a while (specified by

<varname>vacuum_cost_naptime</varname>). Then it will reset the

counter and continue execution.

</para>

<para>

The intent of this feature is to allow administratorsthe reduce

The intent of this feature is to allow administratorsto reduce

the I/O impact of these commands on concurrent database

activity. There are some situations in which it is not very

important thatmaintainence commands like

important thatmaintenance commands like

<command>VACUUM</command> and <command>ANALYZE</command> finish

quickly; however, it is usually very importantthese these

quickly; however, it is usually very importantthat these

commands do not significantly interfere with the ability of the

system to perform other database operations. Cost-based vacuum

delay provides a way for administrators to achieve this.

</para>

<para>

This feature is disabled by default. To enable it, set the

<varname>vacuum_cost_naptime</varname> variable to areasonable

<varname>vacuum_cost_naptime</varname> variable to anonzero

value.

</para>

<term><varname>vacuum_cost_page_hit</varname> (<type>integer</type>)</term>

<listitem>

<para>

The cost for vacuuming a buffer found in the shared buffer

Theestimatedcost for vacuuming a buffer found in the shared buffer

cache. It represents the cost to lock the buffer pool, lookup

the shared hash table and scan the content of the page. The

default value is 1.

<term><varname>vacuum_cost_page_miss</varname> (<type>integer</type>)</term>

<listitem>

<para>

The cost for vacuuming a buffer that has to be read from

Theestimatedcost for vacuuming a buffer that has to be read from

disk. This represents the effort to lock the buffer pool,

lookup the shared hash table, read the desired block in from

the disk and scan its content. The default value is 10.

<term><varname>vacuum_cost_page_dirty</varname> (<type>integer</type>)</term>

<listitem>

<para>

Theextra costadded when vacuum modifies a block that was

Theestimated costcharged when vacuum modifies a block that was

previously clean. It represents the extra I/O required to

flush the dirty block out to disk again. The default value is

20.

<term><varname>vacuum_cost_limit</varname> (<type>integer</type>)</term>

<listitem>

<para>

The accumulated cost that will cause thebackend to briefly

The accumulated cost that will cause thevacuuming process to briefly

nap. The default value is 200.

</para>

</listitem>

<term><varname>vacuum_cost_naptime</varname> (<type>integer</type>)</term>

<listitem>

<para>

The length of time in milliseconds that a backend will nap

when the cost limit has been exceeded. There are certain bulk

operations that hold critical locks and should therefore

complete as quickly as possible. Because of that it is

possible that the cost actually accumulates far higher than

this limit. To compensate for this, the final naptime is

calculated as <varname>vacuum_cost_naptime</varname> *

<varname>accumulated_balance</varname> /

<varname>vacuum_cost_limit</varname> with a maximum of

<varname>vacuum_cost_naptime</varname> * 4.

</para>

<para>

The length of time, in milliseconds, that the process will nap

when the cost limit has been exceeded.

The default value is 0, which disables the cost-based vacuum

delay feature.

delay feature. Positive values enable cost-based vacuuming.

Note however that on many systems, the effective resolution

of sleep delays is 10 milliseconds; setting

<varname>vacuum_cost_naptime</varname> to a value that is

not a multiple of 10 may have the same results as setting it

to the next higher multiple of 10.

</para>

</listitem>

</varlistentry>

</variablelist>

<note>

<para>

There are certain bulk operations that hold critical locks and should

therefore complete as quickly as possible. Cost-based vacuum

delays do not occur during such operations. Therefore it is

possible that the cost accumulates far higher than the specified

limit. To avoid uselessly long delays in such cases, the actual

naptime is calculated as <varname>vacuum_cost_naptime</varname> *

<varname>accumulated_balance</varname> /

<varname>vacuum_cost_limit</varname> with a maximum of

<varname>vacuum_cost_naptime</varname> * 4.

</para>

</note>

</sect3>

</sect2>