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

As the documentation for pg_freespacemap says, it's values are not exact
and they may not be up to date because you are viewing the statistics that
were gathered during the last analyze, not the actual freespace map

https://www.postgresql.org/docs/9.6/pgfreespacemap.html

When you run pgstattuple, it is actually scanning the target object. Also
note that scanning a table does not also scan its indexes, those must be
done individually. So the free space reported by pgstattuple should be the
actual free space, given as both byte size and % of the whole object.

pg_freespacemap is used by the planner and several other internal parts of
PG, so checking it must be quick. pgstattuple can take a while to run on
larger objects, but it will give you 100% accurate results as of the time
it ran. This is why I prefer it when checking for bloat since many of the
queries you see out there also try and base their results on stats. While
the query runs quick, I've often seen them be wildly wrong.

Also, I would not recommend partitioning simply to improve vacuuming.
Especially if extensive tuning hasn't been tried first. Most times you can
get per-table tuning working well enough to get autovacuum running
properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
itself been improved.

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

> 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(dot)fiske(at)crunchydata(dot)com>
> *Enviado:* segunda-feira, 27 de julho de 2020 17:59
> *Para:* Sidney Aloisio Ferreira Pryor <sidney(at)tjrj(dot)jus(dot)br>
> *Cc:* David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>;
> 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 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
> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F9.6%2Fruntime-config-autovacuum.html&data=02%7C01%7C%7C71ae16f240a140ae9db408d8326ff6ed%7Cce4e1164986f413285d11e3c17cf7d6e%7C0%7C0%7C637314803761796413&sdata=04gUckQW6vKJaKHTdyU7kkDS6m2%2BB36rDaQNTQUO4oM%3D&reserved=0>
>
> 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/
> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.keithf4.com%2Fper-table-autovacuum-tuning%2F&data=02%7C01%7C%7C71ae16f240a140ae9db408d8326ff6ed%7Cce4e1164986f413285d11e3c17cf7d6e%7C0%7C0%7C637314803761796413&sdata=b7blaMbbR2Nfy14EZLFGad8D3Lq2RqGibrJU4Dk7fEk%3D&reserved=0>
>
> 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
> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fkeithf4%2Fpg_bloat_check&data=02%7C01%7C%7C71ae16f240a140ae9db408d8326ff6ed%7Cce4e1164986f413285d11e3c17cf7d6e%7C0%7C0%7C637314803761806410&sdata=jXMp2ihYTXeHz0Pwcltj%2BPT%2FXJlfGZBmjPZC7l7ecOo%3D&reserved=0>
>
> --
> Keith Fiske
> Senior Database Engineer
> Crunchy Data - http://crunchydata.com
> <https://nam10.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcrunchydata.com%2F&data=02%7C01%7C%7C71ae16f240a140ae9db408d8326ff6ed%7Cce4e1164986f413285d11e3c17cf7d6e%7C0%7C0%7C637314803761806410&sdata=dDNmLVFQtSJebi1UvLg6P7q3q5DpSxn91ar964jRKFE%3D&reserved=0>
>

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