Task #4531
closedTweak the AUTOVACUUM and AUTOANALYZE parameters for lower peak load
100%
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
Related issues
Updated by Radko Krkoš over 5 years ago
- Related to Bug #4515: Runaway query? added
Updated by Radko Krkoš over 5 years ago
- % 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.
Updated by Radko Krkoš over 5 years ago
- File 0001-Updated-database-documentation-AUTOVACUUM-tuning.patch 0001-Updated-database-documentation-AUTOVACUUM-tuning.patch added
- Assignee changed from Radko Krkoš to Jan Mach
- Target version changed from Backlog to 2.3
- % Done changed from 30 to 90
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.
Updated by Jan Mach about 5 years ago
- 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.