Re: Very slow checkpoints

On Wed, Mar 18, 2015 at 1:21 PM, Steven Jones
<steven.jones1201@outlook.com> wrote:
> #autovacuum = on                        # Enable autovacuum subprocess?  'on'
> #log_autovacuum_min_duration = -1       # -1 disables, 0 logs all actions and
> #autovacuum_max_workers = 3             # max number of autovacuum subprocesses
> #autovacuum_naptime = 1min              # time between autovacuum runs
> autovacuum_vacuum_threshold = 500       # min number of row updates before
> autovacuum_analyze_threshold = 500      # min number of row updates before
> #autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum
> #autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze

If I were you, Ill use _scale_factor settings instead of threshold,
because it makes your autovacuum aggressive enough (you need it on
such workload) without firing too frequently (vacuuming has its
price).  autovacuum_vacuum_scale_factor = 0.01 and
autovacuum_analyze_scale_factor = 0.05 will be OK

And if you see all your autovacuum workers active all the time (more
than 80% of the time for example) it is a reason to increase
autovacuum_max_workers

--
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com