Natalie Wenz <nataliewenz@xxxxxxxxxxx> wrote: >>> autovacuum_freeze_max_age | 800000000 > We talked a little bit about lowering the > autovacuum_max_freeze_age, at least some, but there was concern > that it would end up doing a lot more lengthy full-table scans. > Is that a legitimate concern? It will cause full-table scans to occur more often, but possibly not as much as you fear if your baseline is based on how it behaved before recent bug fixes. It will tend to flush "hot" data from the caches, at least to some degree. The up side of doing it more often is that it will have fewer writes to do each time it is run, which might avoid write gluts that hurt performance more than the reads. > Would it be prudent to change any of the other values back to > their defaults at the same time? For example, we have the > autovacuum_vacuum_cost_delay set to 0, with the idea that we > don't mind if we take a hit on performance while the autovacuum > is running; our priority is that it be able to finish as quickly > as possible. If we start the vacuum earlier, though, maybe that > should be bumped up too? Maybe, but you do have a lot of machine there. You might not notice the hit very much. > Does the autovacuum do different work when it is vacuuming to > prevent wraparound (and that's triggered when a table passes the > autovacuum_max_freeze_age, right?) and a vacuum triggered by the > table changing in size by a certain amount, or a manually-invoked > vacuum? A "normal" vacuum just visits pages which need work based on the visibility map, so those really are almost a fixed amount of work per week regardless of the frequency of runs. It's just a matter of whether you wait until a lot of work needs to be done and do it all at once, or do smaller runs that nibble away at it. The latter usually has less noticeable impact. > (Are there any books, or articles, that cover "Vacuuming and > Autovacuuming: the gory details"?) The best book I know of for this is Greg Smith's "PostgreSQL 9.0 High Performance": http://www.postgresql.org/docs/books/ (Full disclosure, I was one of the technical editors, but don't get money from sales.) > Rereading the Routine Vacuuming page in the docs, this sentence > caught my eye: > > "However, for static tables (including tables that receive > inserts, but no updates or deletes), there is no need to vacuum > for space reclamation, so it can be useful to try to maximize the > interval between forced autovacuums on very large static tables. > Obviously one can do this either by increasing > autovacuum_freeze_max_age or decreasing vacuum_freeze_min_age." > > We generally never delete from this database at all. This case > was unusual; I was migrating the data from one table to another > because we added some columns, and changed the datatype of many > of the columns from text to more appropriate types (timestamp, > int, uuid, inet, etc). Ideally, even then we wouldn't have > preferred to delete anything until the whole table was migrated, > but disk space became an issue. Bleh. That might be a reason to tweak the settings, but since recent versions of vacuum skip heap pages that won't benefit from vacuum, I probably wouldn't. > With that in mind, would you still recommend putting the > autovacuum_max_freeze_age back to 200 million? Where possible, I'm a big fan of incremental change. I might nudge it in that direction a little at a time and watch the behavior. I do think that periodic VACUUM ANALYZE statements (weekly?) of the database might be a good supplement to the autovacuum jobs, especially if you have a time when load tends to be lower to schedule that in. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
