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

[Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx>]

Thank you so much Keith.

We will try manual vacuum as you suggested.

I read something like this but did not give the right attention to it.

 

About pgstattuple was a bit confusing its free area information when we compared to pg_freespace.

Can you please explain this difference?

---

De: Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx>
Enviado: segunda-feira, 27 de julho de 2020 17:59
Para: Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx>
Cc: David G. Johnston <david.g.johnston@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
Assunto: 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@xxxxxxxxxxx> 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.g.johnston@xxxxxxxxx>
Enviado: segunda-feira, 27 de julho de 2020 17:17
Para: Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
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@xxxxxxxxxxx> 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