Re: How can i be certain autovacuum is causing reuse if table still grows

On Mon, Jul 27, 2020 at 4:36 PM Sidney Aloisio Ferreira Pryor <
sidney(at)tjrj(dot)jus(dot)br> wrote:

> Thank you, David.
> So can i assume that autovacuum only release dead tuples when it ends its
> execution?
>
> Autovacuum is not finishing and is running for days.
> We did not find a parameter to limit autovacuum executing time.
> Do you have any recomendation?
>
> Thank you.
> Sidney.
> ------------------------------
> *De:* David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> *Enviado:* segunda-feira, 27 de julho de 2020 17:17
> *Para:* Sidney Aloisio Ferreira Pryor <sidney(at)tjrj(dot)jus(dot)br>
> *Cc:* pgsql-admin(at)lists(dot)postgresql(dot)org <pgsql-admin(at)lists(dot)postgresql(dot)org>
> *Assunto:* Re: How can i be certain autovacuum is causing reuse if table
> still grows
>
> On Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor <
> sidney(at)tjrj(dot)jus(dot)br> wrote:
>
> Hi, we have a 9.6.5 postgres
>
>
>
> Autovacuum is set with default parameters and is running on this table for
> days without naping or stopping.
>
> 3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE
> public.flip_pagina_edicao (to prevent wraparound)
> (1 rows)
>
>
> Its probably best avoid drawing conclusions here until the anti-wraparound
> vacuum has completed and you’ve updated to the latest minor version of
> PostgreSQL.
>
> David J.
>
>

Correct, the tuples will only be released once the autovacuum transaction
has committed.

If it's been running for days without being committed, I would recommend
cancelling it and immediately running a manual vacuum before it starts up
again. You'll have to be quick and double-check that your manual vacuum is
the one running and not getting blocked by the wraparound vac starting up
again. You can check this by looking at pg_stat_activity. You may see the
wraparound vacuum try and start up again as well, but it should be blocked
by yours and typically cancel out once yours finishes.

A manual vacuum runs with different, more aggressive settings than
autovacuum. So you should see this finish much sooner. May still take a few
hours, but it shouldn't be much longer. The biggest culprit with autovacuum
taking a long time on a given table, besides there being a lot of bloat, is
typically autovacuum_vacuum_cost_delay, which can cause autovacuum to pause
if autovacuum_vacuum_cost_limit is reached. With a manual vacuum, this
delay is disabled (it uses vaccum_cost_delay which is by default 0). You
can see more in this section of the documentation -
https://www.postgresql.org/docs/9.6/runtime-config-autovacuum.html

You may want to adjust the autovac cost delay (lower it from 2 to 1 maybe).
I believe you can do this on a per-table basis vs changing it for the
entire database. I would not recommend completely disabling the delay until
you at least get your system fully vacuumed and in a better state. I'd also
recommend some more aggressive vacuuming be configured for your given table
as well, so it doesn't accumulate so many dead rows before vacuum is able
to run. I wrote about how to do this here -
https://www.keithf4.com/per-table-autovacuum-tuning/

After it finishes running, in addition to upgrading to the latest version
of 9.6, I would recommend checking the table & indexes for bloat. You can
use the pgstattuple contrib module or this script which makes it a little
more user-friendly https://github.com/keithf4/pg_bloat_check

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com