Thread: finding tables about to be vacuum freezed
Hello, recently one of my tables needed a vacuum (to prevent wraparound) and of course it happened at a really bad time, so since then I've been learning about how Transaction ID Wraparound works and its associated parameters.
I'm trying this query to see how close my tables are to hitting the vacuum_freeze_table_age threshold (150M in my case):
SELECT
relname,
age(relfrozenxid) as xid_age,
ROUND(100.0 * age(relfrozenxid) / current_setting('vacuum_freeze_table_age')::numeric, 1) || '%' AS "% til vacuum freeze"
FROM
pg_class
WHERE relkind = 'r';
For now, assume my tables have no storage parameters that override the defaults.
I was surprised at the results, almost all my tables look like:
my_table | 160589343 | 107.1%
Or about 160m transactions old. I would have thought with my current settings:
vacuum_freeze_min_age = 50m
vacuum_freeze_table_age = 150m
autovacuum_freeze_max_age = 200m
that the autovacuumer would have already forced a vacuum freeze on all these tables. According to the docs, "a whole table sweep is forced if the table hasn't been fully scanned for vacuum_freeze_table_age minus vacuum_freeze_min_age transactions" which would be 100m transactions.
I'm guessing my understanding here is wrong. What did I miss?
Just in case it matters, here's my current txid:
mydb=# select txid_current();
txid_current
--------------
1485894081
(1 row)
Thanks!
On Tue, May 5, 2015 at 6:40 PM, Steve Kehlet <steve.kehlet@gmail.com> wrote:
Hello, recently one of my tables needed a vacuum (to prevent wraparound) and of course it happened at a really bad time, so since then I've been learning about how Transaction ID Wraparound works and its associated parameters.I'm trying this query to see how close my tables are to hitting the vacuum_freeze_table_age threshold (150M in my case):SELECTrelname,age(relfrozenxid) as xid_age,ROUND(100.0 * age(relfrozenxid) / current_setting('vacuum_freeze_table_age')::numeric, 1) || '%' AS "% til vacuum freeze"FROMpg_classWHERE relkind = 'r';For now, assume my tables have no storage parameters that override the defaults.I was surprised at the results, almost all my tables look like:my_table | 160589343 | 107.1%Or about 160m transactions old. I would have thought with my current settings:vacuum_freeze_min_age = 50mvacuum_freeze_table_age = 150mautovacuum_freeze_max_age = 200mthat the autovacuumer would have already forced a vacuum freeze on all these tables. According to the docs, "a whole table sweep is forced if the table hasn't been fully scanned for vacuum_freeze_table_age minus vacuum_freeze_min_age transactions" which would be 100m transactions.I'm guessing my understanding here is wrong. What did I miss?
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.
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.
Cheers,
Jeff
On Wed, May 6, 2015 at 9:46 AM Jeff Janes <jeff.janes@gmail.com> 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):
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.
On Wed, May 6, 2015 at 10:51 AM, Steve Kehlet <steve.kehlet@gmail.com> wrote:
On Wed, May 6, 2015 at 9:46 AM Jeff Janes <jeff.janes@gmail.com> 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
On Wed, May 6, 2015 at 7:24 PM Jeff Janes <jeff.janes@gmail.com> wrote:
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.
I'll see if I can do this!
I don't have any experience with 3.5TB databases, but I certainly think that that is something to worry about.
We did hit the autovacuum_freeze_max_age threshold on Saturday and the autovacuumer has been running for days now, slowly cranking through each table. Fortunately, I had autovacuum_vacuum_cost_delay set so the IO impact isn't bad: no complaints from the customer, and our ops group says the IO load is okay. So Postgres is just quietly doing its thing. This has clearly happened numerous times before, and explains a few mysterious incidents in the past where a nightly analyze script has hung for several days. It's really great to understand this better now.
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.
Fortunately in this case it's just one database, but good to know.
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.
You're right, that was the exact problem that got me going down this path. Some of our guys were doing a software update and some CREATE INDEX operations it wanted to do were blocked by the autovacuumer. Fortunately, we don't do software updates all the time, but it's falling onto me to figure out how to make the autovacuumer not do its thing in the middle of future software updates :-). I might do ALTER TABLES on all tables to temporarily increase their autovacuum_freeze_max_age before, and undo it after. Kind of hacky, but it should work.
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.
I'll investigate this. I found Josh Berkus' excellent articles, [Freezing your tuples off](http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.html) and the nightly [Flexible Freeze](https://github.com/pgexperts/flexible-freeze) program that came out that. I'll investigate using this so we can better control when this happens. Although, given that the IO load even during production hours isn't making anyone scream, I might just leave it alone.
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.
Thanks Jeff for all your help and insight.
Hello Steve,
Great monitoring query (https://gist.github.com/skehlet/36aad599171b25826e82). I suggest modifying the value "autovacuum_freeze_table_age" to "LEAST(autovacuum_freeze_table_age,(0.95*autovacuum_freeze_max_age)) AS autovacuum_freeze_table_age" since PostgreSQL implicitly limits vacuum_freeze_table_age to 95% of autovacuum_freeze_max_age (as documented at: http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE).
It might also be cool to add something like:
(((2^32)/2)-1-1000000) AS wraparound_dbfreeze_age,
ROUND(100.0 * age(relfrozenxid) / (((2^32)/2)-1-1000000)::numeric, 1) || '%' AS "% til wraparound db freeze"
to monitor how close it is getting to the point at which it gets close to going into safety shutdown mode (as documented in http://www.postgresql.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND) in case there is a problem with autovacuum (rare, but if you are already running that query adding it is cheap and nice for completeness).
You have the count of rows there already, but it might also be nice (albeit more computationally expensive) to add the size of the table so you can evaluate if it's really worth doing a manual off-hours vacuum of the table to avoid the scan happening during peak hours:
(pg_relation_size(pg_class.oid)) AS table_bytes
For your situation it might be good to set vacuum_freeze_min_age to a very low value, which will make it more likely that your ordinary vacuums are more likely to freeze the rows and advance relfrozenxid, and to keep autovacuum tuned aggressive (for bloat/ space reclamation purposes). This may favor many short/cheap operations and help avoid the long lock heavy vacuum freeze operations. Increasing autovacuum_max_workers will also help avoid having freeze operation of one table monopolize the autovacuum workers.
Will J. Dunn
On Tue, May 12, 2015 at 12:51 PM, Steve Kehlet <steve.kehlet@gmail.com> wrote:
On Wed, May 6, 2015 at 7:24 PM Jeff Janes <jeff.janes@gmail.com> wrote: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.I'll see if I can do this!I don't have any experience with 3.5TB databases, but I certainly think that that is something to worry about.We did hit the autovacuum_freeze_max_age threshold on Saturday and the autovacuumer has been running for days now, slowly cranking through each table. Fortunately, I had autovacuum_vacuum_cost_delay set so the IO impact isn't bad: no complaints from the customer, and our ops group says the IO load is okay. So Postgres is just quietly doing its thing. This has clearly happened numerous times before, and explains a few mysterious incidents in the past where a nightly analyze script has hung for several days. It's really great to understand this better now.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.Fortunately in this case it's just one database, but good to know.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.You're right, that was the exact problem that got me going down this path. Some of our guys were doing a software update and some CREATE INDEX operations it wanted to do were blocked by the autovacuumer. Fortunately, we don't do software updates all the time, but it's falling onto me to figure out how to make the autovacuumer not do its thing in the middle of future software updates :-). I might do ALTER TABLES on all tables to temporarily increase their autovacuum_freeze_max_age before, and undo it after. Kind of hacky, but it should work.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.I'll investigate this. I found Josh Berkus' excellent articles, [Freezing your tuples off](http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.html) and the nightly [Flexible Freeze](https://github.com/pgexperts/flexible-freeze) program that came out that. I'll investigate using this so we can better control when this happens. Although, given that the IO load even during production hours isn't making anyone scream, I might just leave it alone.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.Thanks Jeff for all your help and insight.