Re: Very slow checkpoints

From: Ilya Kosmodemiansky <ilya(dot)kosmodemiansky(at)postgresql-consulting(dot)com>
To: Steven Jones <steven(dot)jones1201(at)outlook(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Very slow checkpoints
Date: 2015-03-18 12:28:27
Message-ID: CAG95seU367x64H4xjzEfdQvjgrN+ehvAf_Pk+DTb70cNGw4yQw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Wed, Mar 18, 2015 at 1:21 PM, Steven Jones
<steven(dot)jones1201(at)outlook(dot)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(at)postgresql-consulting(dot)com

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message didier 2015-03-18 15:27:36 Re: Very slow checkpoints
Previous Message Steven Jones 2015-03-18 12:21:12 Re: Very slow checkpoints