On Fri, Nov 9, 2012 at 5:28 PM, Lists <lists@benjamindsmith.com> wrote:
> As I've spent a considerable amount of time trying to sort this out, I'm
> posting it for the benefit other users.
SNIP
> D) concurrent use of pg_dump;
Not usually a problem, unless it's overloading your IO subsystem.
> C) use of transactions, especially prepared transactions and multiple
> savepoints;
> E) use of numerous databases on a single server, average about 50;
These two can be killers. Long running transactions can cause
autovacuum processes to stall out or be autocancelled.
As well, since the default nap time is 1 minute, it will take at least
50 minutes to vacuum each db as nap time is how long autovac waits
between databases.
Reducing autovacuum nap time to 5 or 10 seconds would be a good move
here, also possibly making it more aggressive by increasing max worker
threads, decreasing cost delay (possibly to zero or close to it) and /
or increasing cost limit. After making such a change then watching
iostat when vacuum is running to see how hard its hitting your IO
subsystem. I'm guessing that with SSDs it isn't gonna be a big
problem.
As Greg Smith has pointed out in the past, usually the answer to an
autovacuum problem is making it more, not less aggressive. Unless
you're flooding your IO this is almost always the right answer. Keep
in mind that autovacuum by default is setup to be VERY unaggressive
because it may be running on a netbook for all it knows.
To tune autovacuum with 50 databases, start by dropping nap time to
something much lower, like 10s. Then if you need to, drop cost delay
until you get to 0. If you get to 0 and it's still not hitting your
IO too hard, but not keeping up, then increase cost limit. If you get
to something in the 5000 to 10000 range, and its still not keeping up
then start bumping the thread count