|PostgreSQL Reference Manual - Volume 3 - Server Administration Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 204 pages
RRP £13.95 ($24.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
4.4.4 Cost-Based Vacuum Delay
During the execution of
ANALYZE commands, the system maintains an
internal counter that keeps track of the estimated cost of the
various I/O operations that are performed. When the accumulated
cost reaches a limit (specified by
vacuum_cost_limit), the process performing
the operation will sleep for a while (specified by
vacuum_cost_delay). Then it will reset the
counter and continue execution.
The intent of this feature is to allow administrators to reduce
the I/O impact of these commands on concurrent database
activity. There are many situations in which it is not very
important that maintenance commands like
quickly; however, it is usually very important that 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.
This feature is disabled by default. To enable it, set the
vacuum_cost_delay variable to a nonzero
The length of time, in milliseconds, that the process will sleep
when the cost limit has been exceeded.
The default value is zero, which disables the cost-based vacuum
delay feature. Positive values enable cost-based vacuuming.
Note that on many systems, the effective resolution
of sleep delays is 10 milliseconds; setting
vacuum_cost_delayto a value that is not a multiple of 10 may have the same results as setting it to the next higher multiple of 10.
- The estimated cost 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 one.
- The estimated cost 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.
- The estimated cost charged 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.
- The accumulated cost that will cause the vacuuming process to sleep. The default value is 200.
Note: There are certain 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 delay is calculated as
vacuum_cost_limitwith a maximum of
|ISBN 0954612043||PostgreSQL Reference Manual - Volume 3 - Server Administration Guide||See the print edition|