Re: How can i be certain autovacuum is causing reuse if table still grows - Mailing list pgsql-admin
| From | Keith Fiske |
|---|---|
| Subject | Re: How can i be certain autovacuum is causing reuse if table still grows |
| Date | |
| Msg-id | CAODZiv5Bb6TLEAi9Cq6ku+mFF-OZYj94GfEhR4jpvvJES7sd2Q@mail.gmail.com Whole thread Raw |
| In response to | RE: How can i be certain autovacuum is causing reuse if table still grows (Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>) |
| Responses |
RE: How can i be certain autovacuum is causing reuse if table still grows
|
| List | pgsql-admin |
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@tjrj.jus.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.fiske@crunchydata.com>
Enviado: segunda-feira, 27 de julho de 2020 19:21
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still growsOn Mon, Jul 27, 2020 at 6:19 PM Keith Fiske <keith.fiske@crunchydata.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 mapSorry, 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 gatheringWhen 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.
pgsql-admin by date: