Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)

The good news is that we have now resolved our critical problem (disk
space overuse) with a somewhat hackish, slow answer that is nonetheless
good enough for now.

Now I'd like to work out how to get autovacuum to work smoothly within
our cluster. I'm happy to try to clarify my notes and post them either
here or on the PG wiki as I get them resolved.

There's a wealth of how to tune PG instruction that's old and (based on
this thread alone) often stale enough to be classified as
disinformative. For example, nearest I can tell, the entirety of this
page is just wrong and/or irrelevant for 9.x and up:
http://wiki.postgresql.org/wiki/VACUUM_FULL. In constrast to the advice
to "avoid Vacuum Full" (
http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT)
comes the insight that vacuum full is necessary to clean up stale data
that is not at the "end" of the table. (See Jeff Janes 11/10/2012 email)

> non-full vacuum can
> only free space from the "end" of the table.
This would imply that a full analyze is a good idea, at least
periodically (weekly/monthly/quarterly) in a database that combines the
use of temp tables and periodic changes to persistent objects. Does
autovacuum ever do a "full" analyze"? What about autovacuum and the
reindex question at the end of this email?

On 11/10/2012 02:49 PM, Adrian Klaver wrote:
>
> Seems to have changed in 8.3:
>
> http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html
>
> "Beginning in PostgreSQL 8.3, autovacuum has a multiprocess
> architecture: There is a daemon process, called the autovacuum
> launcher, which is in charge of starting autovacuum worker processes
> for all databases. The launcher will distribute the work across time,
> but attempt to start one worker on each database every
> autovacuum_naptime seconds. One worker will be launched for each
> database, with a maximum of autovacuum_max_workers processes running
> at the same time..."
>

Sadly, this change means that I can no be certain of the utility of the
otherwise excellent-sounding advice originally offered by Scott, quoted
below. It appears that naptime is (as of 9.x) almost irrelevant since
it's defined per database, and dropping this from 1 minute to 5 seconds
would have very little overall impact.

> These two can be killers. Long running transactions can cause
> autovacuum processes to stall out or be autocancelled.
"Long running transactions" - is now long? In our system it's rare to
have a transaction (even a prepared transaction) last much longer than a
few minutes. Is that enough time to cause problems with AutoVacuum?

> 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

Should I increase the max_workers field from the default of 3 to
(perhaps) 10? Noting that my solution to the disk space problem is
effectively a max_worker of 1 since it's all done sequentially, I wonder
if reducing max_workers would actually be better?

Also, what's the "thread count" ? Is that max_workers?

Why would I want to reduce the cost delay to 0, and how does this relate
to cost_limit? Careful reading of the docs:
http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html
makes me believe that, given my substantial I/O subsystem, I'd want to
drop cost_delay to near zero and set the cost_limit really high, which
is a rough restatement of the last quoted paragraph above. (I think)

Assuming that I make these suggestions and notice a subsequent system
load problem, what information should I be gathering in order to provide
better post-incident forensics? We have statistics turned on, and aren't
using replication. (yet)

Lastly, there's the question of reindexing before full vacuum. I've
observed that not doing a manual reindex prior to vacuum full did not,
in fact, free up the space, even though I've been told that reindex is
implicit in the vacuum process. (?!) I'm confident that I can reproduce
this behavior given a bit of time to allow one of our database servers
to bloat back up.

-Ben