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
>
> 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
>

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