|
Thank you for the explanation Keith.
We will really improve our minor release update policy.
We have few but important postgres database here.
I guess an outage would really be better.
Index bloat is shorter than table bloat but it is present too.
So far manual vacuum 3 of 8 indexes.
An alter table to disable autovacuum and an autovacuum are both waiting for manual vacuum verbose.
flip=# SELECT pid,
flip-# Age(query_start, Clock_timestamp()),
flip-# usename,
flip-# query,
flip-# state,
flip-# wait_event
flip-# FROM pg_stat_activity
flip-# WHERE query != '<IDLE>'
flip-# AND query ilike '%vacuum%'
flip-# ORDER BY query_start ASC;
pid | age | usename | query | state | wait_event
-------+------------------+----------+------------------------------------------------------------------------------+--------+------------
25849 | -01:59:22.309787 | postgres | vacuum verbose flip_pagina_edicao; | active |
22317 | -00:55:09.848074 | postgres | ALTER TABLE flip_pagina_edicao SET (autovacuum_enabled = false); | active | relation
27718 | -00:53:00.368776 | postgres | autovacuum: VACUUM ANALYZE public.flip_pagina_edicao (to prevent wraparound) | active | relation
I am truly optimist it will solve.
So far no user session is locked or waiting.
De: Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx>
Enviado: segunda-feira, 27 de julho de 2020 20:55 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 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.
On Mon, Jul 27, 2020 at 6:49 PM Sidney Aloisio Ferreira Pryor <sidney@xxxxxxxxxxx> wrote:
|
