Project

General

Profile

Actions

Task #4531

closed

Tweak the AUTOVACUUM and AUTOANALYZE parameters for lower peak load

Added by Radko Krkoš over 3 years ago. Updated over 3 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Development - Core
Target version:
Start date:
01/14/2019
Due date:
% Done:

100%

Estimated time:
To be discussed:

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

Related to Mentat - Bug #4515: Runaway query?ClosedPavel Kácha01/14/2019

Actions
Actions #1

Updated by Radko Krkoš over 3 years ago

Actions #2

Updated by Radko Krkoš over 3 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.

Actions #3

Updated by Radko Krkoš over 3 years ago

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.

Actions #4

Updated by Radko Krkoš over 3 years ago

  • Status changed from New to Resolved
Actions #5

Updated by Jan Mach over 3 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.

Actions

Also available in: Atom PDF