Re: Autovacuum, dead tuples and bloat

On Wed, Jun 26, 2024 at 3:03 AM Shenavai, Manuel <manuel(dot)shenavai(at)sap(dot)com>
wrote:

> Thanks for the suggestions.
> I checked pg_locks shows and pg_stat_activity but I could not find a LOCK
> or an transaction on this (at this point in time).
>
> I assume that this problem may relate to long running transactions which
> write a lot of data. Is there already something in place that would help me
> to:
> 1) identify long running transactions
>

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
https://www.postgresql.org/docs/current/pgstatstatements.html

> 2) get an idea of the data-volume a single transaction writes?
>
> I tested the log_statement='mod' but this writes too much data (including
> all payloads). I rather would like to get a summary entry of each
> transaction like:
> "Tx 4752 run for 1hour and 1GB data was written."
>
> Is there something like this already available in postgres?
>

*Maybe* you can interpolate that by seeing how much wal activity is written
during the transaction, but I'm dubious.

>
> Best regards,
> Manuel
>
> -----Original Message-----
> From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> Sent: 22 June 2024 23:17
> To: Shenavai, Manuel <manuel(dot)shenavai(at)sap(dot)com>; Achilleas Mantzios <
> a(dot)mantzios(at)cloud(dot)gatewaynet(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org
> Subject: Re: Autovacuum, dead tuples and bloat
>
> On 6/22/24 13:13, Shenavai, Manuel wrote:
> > Thanks for the suggestion. This is what I found:
> >
> > - pg_locks shows only one entry for my DB (I filtered by db oid). The
> entry is related to the relation "pg_locks" (AccessShareLock).
>
> Which would be the SELECT you did on pg_locks.
>
> > - pg_stat_activity shows ~30 connections (since the DB is in use, this
> is expected)
>
> The question then is, are any of those 30 connections holding a
> transaction open that needs to see the data in the affected table and is
> keeping autovacuum from recycling the tuples?
>
> You might need to look at the Postgres logs to determine the above.
> Logging connections/disconnections helps as well at least 'mod' statements.
>
> See:
>
> https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
>
> for more information.
>
> >
> > Is there anything specific I should further look into in these tables?
> >
> > Regarding my last post: Did we see a problem in the logs I provided in
> my previous post? We have seen that there are 819294 n_live_tup in the
> toast-table. Do we know how much space these tuple use? Do we know how
> much space one tuple use?
>
> You will want to read:
>
> https://www.postgresql.org/docs/current/storage-toast.html
>
> Also:
>
> https://www.postgresql.org/docs/current/functions-admin.html
>
> 9.27.7. Database Object Management Functions
>
> There are functions there that show table sizes among other things.
>
> >
> > Best regards,
> > Manuel
> >
> > -----Original Message-----
> > From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> > Sent: 21 June 2024 22:39
> > To: Shenavai, Manuel <manuel(dot)shenavai(at)sap(dot)com>; Achilleas Mantzios <
> a(dot)mantzios(at)cloud(dot)gatewaynet(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org
> > Subject: Re: Autovacuum, dead tuples and bloat
> >
> > On 6/21/24 12:31, Shenavai, Manuel wrote:
> >> Hi,
> >>
> >> Thanks for the suggestions. I found the following details to our
> >> autovacuum (see below). The related toast-table of my table shows some
> >> logs related the vacuum. This toast seems to consume all the data
> >> (27544451 pages * 8kb ≈ 210GB )
> >
> > Those tuples(pages) are still live per the pg_stat entry in your second
> > post:
> >
> > "n_dead_tup": 12,
> > "n_live_tup": 819294
> >
> > So they are needed.
> >
> > Now the question is why are they needed?
> >
> > 1) All transactions that touch that table are done and that is the data
> > that is left.
> >
> > 2) There are open transactions that still need to 'see' that data and
> > autovacuum cannot remove them yet. Take a look at:
> >
> > pg_stat_activity:
> >
> >
> https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
> >
> > and
> >
> > pg_locks
> >
> > https://www.postgresql.org/docs/current/view-pg-locks.html
> >
> > to see if there is a process holding that data open.
> >
> >>
> >> Any thoughts on this?
> >>
> >> Best regards,
> >> Manuel
> >>
> >
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)aklaver(dot)com
> >
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>