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

Поиск
Список
Период
Сортировка
От Lists
Тема Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)
Дата
Msg-id 50A15659.3090103@benjamindsmith.com
обсуждение исходный текст
Ответ на Re: Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum)  (Adrian Klaver <adrian.klaver@gmail.com>)
Ответы Re: Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)  (Craig Ringer <craig@2ndQuadrant.com>)
Re: Enabling Autovacuum Postgres 9.1 (was Unexpectedly high disk space usage)  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Lists
Дата:
Сообщение: Re: Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum)
Следующее
От: David Greco
Дата:
Сообщение: plpgsql cursor reuse