Task #4531
closed
Tweak the AUTOVACUUM and AUTOANALYZE parameters for lower peak load
Added by Radko Krkoš over 5 years ago.
Updated over 5 years ago.
Category:
Development - Core
Description
The settings for auto{vacuum, analyze} were chosen aggressively to speed up these operations. The values should be revised, to reduce conflicts with normal usage (and still keep the run times reasonably low).
Files
- % Done changed from 0 to 30
The autovacuum (affecting both autovacuum and autoanalyze) scheduling parameters have been adjusted, "autovacuum_vacuum_cost_delay" was increased to "10ms" (from 0), "autovacuum_vacuum_cost_limit" was lowered to "5000" (from 10000). This should lower the peak load. Also, "effective_io_concurrency" was lowered to "4" (from 8) to decrease the peak load of the disk subsystem (with little effect so far). Now the new settings are being tested.
Monitoring shows that the new settings work as expected. Less load should be generated against the disk subsystem if running out of disk cache. The AUTOVACUUM now takes about a minute longer (unladen), but that is acceptable. The AUTOVACUUM run time for large DELETE operations (10M rows order of magnitude) may take up to several hours under load, but that was already slow.
The documentation was updated to reflect the changes, see attached patch. It should be part of the next release as it is already deployed in production - hence the target version change.
- Status changed from New to Resolved
- Status changed from Resolved to Closed
- % Done changed from 90 to 100
I have just committed the attached documentation patch to the repository, this issue can therefore be closed.
Also available in: Atom
PDF