On Wed, May 6, 2015 at 10:51 AM, Steve Kehlet <steve.kehlet@xxxxxxxxx> wrote:
On Wed, May 6, 2015 at 9:46 AM Jeff Janes <jeff.janes@xxxxxxxxx> wrote:vacuum_freeze_table_age controls when it promotes a vacuum *which is already going to occur* so that it scans the whole table. It doesn't specially schedule a vacuum to occur. When those tables see enough activity to pass autovacuum_vacuum_scale_factor then the vacuum that occurs will get promoted to be a full scan.Thank you Jeff, that really helps understand this.If they never see that amount of activity, then the tables will continue to age until autovacuum_freeze_max_age, at which point a vacuum will get launched specifically for the purpose of advancing relfrozenxid.So based on this, I created this query to show autovacuum and analyze data, including if the next autovacuum will be a freeze; and how close the table is to being force-freezed. This borrows heavily from queries I found at Heroku (https://github.com/heroku/heroku-pg-extras/blob/master/lib/heroku/command/pg.rb):
I've booked-marked these but haven't really looked into them to any extent. It would be awesome if you put the SQL one somewhere on http://wiki.postgresql.org. That way it is easier to find, and anyone who finds it can contribute explanations, corrections, and update it to keep up with changes to the database.
My output looks like:I'm thinking I'm in for a world of pain when all my tables in my 3.5TB db simultaneously hit autovacuum_freeze_max_age, and I think I'm about 83% of the way there.What do you think? I'm thinking I should start doing a VACUUM FREEZE on tables at night to head this off.
I don't have any experience with 3.5TB databases, but I certainly think that that is something to worry about.
There are two main problems you are likely to encounter (from what I know):
One is that the autovacuum scheduler deals poorly with a database exceeding autovacuum_freeze_max_age. It forces all available to autovacuum resources to be directed to that database, starving any other database of attention. If you have multiple active databases, by the time one database has been frozen enough to no longer exceed autovacuum_freeze_max_age, the other one(s) might be horribly bloated. If your cluster only has one active database in it, this won't be a problem. The one that gets all the attention is the one that needs all the attention. But if you have multiple active databases in your cluster, this could be a problem.
The other problem is that autovac takes a fairly strong lock out on the table while it is vacuuming it. Normally it relinquishes the lock once it realizes someone else is waiting on it. But in the case of a forced full-table scan (either autovacuum_freeze_max_age or vacuum_freeze_table_age is exceeded), it refuses to relinquish the lock. This means that any process which needs a strong-ish table lock (add column, drop column, create index, drop index, cluster, truncate, reindex, etc.) is going to block for potentially a very very long time. This is only a problem if you actually have such processes. If all you do is select, insert, update, delete, none of those things will be blocked.
So if you have lull time at night, it would be a good idea to preemptively vacuum tables approaching autovacuum_freeze_max_age (and also exceeding vacuum_freeze_table_age). I wouldn't even do VACUUM FREEZE, just VACUUM.
But aware that, be default setting, autovac is highly throttled for IO, while regular vacuum is entirely unthrottled for IO. So if nighttime is not completely idle but only relatively less busy of user activity, you might want to evaluate what level of throttling is appropriate.
Cheers,
Jeff
