Re: finding tables about to be vacuum freezed

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*
*willjdunn.com <http://willjdunn.com>*

On Tue, May 12, 2015 at 12:51 PM, Steve Kehlet <steve(dot)kehlet(at)gmail(dot)com>
wrote:

> On Wed, May 6, 2015 at 7:24 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)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.
>
>
>