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

What were the results of pgstattuple? Curious what the live tuple_* and
dead_tuple* values vs the free space were. Also note that if you just ran
it on the table, that is the results for JUST the table. If there are
indexes as well, you'd have to check those separately and they may be even
worse (index bloat is often worse than table). The pg_bloat_check script I
linked, when run against a single table, will run against the table and all
b-tree indexes (it cannot check gin/gist).

Unfortunately, since this is a wraparound vacuum that is starting,
disabling autovacuum will not work. Wraparound vacuums run even when
autovac is disabled in order to try and keep you from getting into
transaction exhaustion territory which means you must take the database
offline to fix things and avoid data corruption. As I said autovac will
attempt to run again, even if you start yours. And both will attempt to get
the lock, but only one will actually succeed in getting it. Check the
wait_event_type and wait_event column in pg_stat_activity to determine
which one actually obtained the lock and you want to make sure yours is the
one that did. Can check the documentation for the meanings of those columns
https://www.postgresql.org/docs/9.6/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

In all honesty, though, if this table is as bloated as it seems, you may
want to just try and schedule an outage to run a VACUUM FULL on this table
and get it fully cleaned up. 2.3TB of empty space in just the table is
quite a lot and each index may be just as bad. Until regular vacuums can
efficiently run on this table it's only going to get progressively worse
and just keep taking longer and longer. I would also highly suggest getting
on the latest version of 9.6 before doing so that you're sure all bug fixes
are in place.

An outage now may be costly, but it will be nowhere near as costly as
autovacuum never being able to run on this table and reaching transaction
exhaustion.

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

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

> Thank you for the explanation Keith.
>
> Pgstattuple runs for 8 hours approximately before printing its result.
> and pg_freespace runs for "only" 16 minutes so we presumed really that
> pgstattuple scans the relation and brings more accurate information.
>
> The strange fact is that pg_freespace result is changing from day to day,
> even without vacuum or analyze finishing.
> At day one we start monitoring it was saying it has 108GB and kept
> changing each day.
> And the worse fact is the different values for free space: pgstattupple
> with 2.3TB free VS pg_freespace with 96GB.
>
> But i just killed autovacuum and started a manual vacuum verbose.
> and 20 seconds later a autovacuum started.
> it seems they are both running together.
>
> Both sessions gets row exclusive locks.
> I think it is better disabling autovacuum, do you agree?
>
>
> ------------------------------
> *De:* Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com>
> *Enviado:* segunda-feira, 27 de julho de 2020 19:21
> *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 6:19 PM Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com>
> wrote:
>
> 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
>
>
>
> Sorry, just wanted to clarify this. You are looking at the actual
> freespace map. It's that the values actually stored in it are not exact and
> based off of statistics gathering
>
>
>
>
> https://www.postgresql.org/docs/9.6/pgfreespacemap.html
> <https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2F9.6%2Fpgfreespacemap.html&data=02%7C01%7C%7C053f528bacc8435d78d108d8327b7b5a%7Cce4e1164986f413285d11e3c17cf7d6e%7C0%7C0%7C637314853231996304&sdata=SpIoB3lzTI5AgXaOjTUA02HJc5Sa6AM4k%2BRLBA7bvXE%3D&reserved=0>
>
> 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.
>
>
>