Re: Vacuum Tuning Question

On Fri, May 19, 2023 at 11:36 AM Murthy Nunna <mnunna(at)fnal(dot)gov> wrote:

> Hi,
>
>
>
> I have a fairly large database (several TBs) where auto vacuum is enabled.
> My focus is to keep the datfrozenxid reasonably low and avoid manual
> vacuum.
>

Why? The age of datfrozenxid needs to be kept below 2 billion, but you
should already be staying way below that, without needing to do anything.
What benefit do you think you will accrue from keeping it even more lower?

> ...So, eventually I am afraid I have to vacuum the tables manually

Autovacuum to prevent wrap around will automatically kick in at
autovacuum_freeze_max_age (if nothing makes it happen sooner). There is no
need to do a manual vacuum to accomplish that, so nothing needs to be done
to avoid it.

> which has its own problems like creating massive WALs in a short time etc.
> I would like to avoid manual vacuuming for this reason.
>

While manual vacuums are unthrottled by default, you can change
vacuum_cost_delay to be the same as autovacuum_vacuum_cost_delay so they
will be throttled in the same way as autovac is. So if you were to have a
good reason to do regular manual vacuums (which I don't think you do), this
would not be much of a counterargument. (There are other counterarguments
which are better, like autocancelling upon lock conflicts, or just not
wanting to write your own scheduling code when autovacuum already exists.)

> SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname
>
> FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname
>
> FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE
> name = 'autovacuum_freeze_max_age') AS foo
>
> ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC;
>

...
>

> Following are the settings I have. I am wondering if there is a way
> autovacuum can keep the above “datfrozenxid” low and not keep increasing.
> Thank you ahead time for reading my post.
>

If you want autovacuum_freeze_max_age to be lower just make it lower. You
are basically saying you want it to behave as if it were lower, but without
actually making it lower. You don't want the ratio to be much more than
100%, but expecting it to never even get close to 100% doesn't make any
sense. On a busy system, it will likely approach the value you told it to,
that is what the setting is for.

Cheers,

Jeff