On Wed, Jun 26, 2024 at 3:03 AM Shenavai, Manuel <manuel.shena...@sap.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.kla...@aklaver.com> > Sent: 22 June 2024 23:17 > To: Shenavai, Manuel <manuel.shena...@sap.com>; Achilleas Mantzios < > a.mantz...@cloud.gatewaynet.com>; pgsql-general@lists.postgresql.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.kla...@aklaver.com> > > Sent: 21 June 2024 22:39 > > To: Shenavai, Manuel <manuel.shena...@sap.com>; Achilleas Mantzios < > a.mantz...@cloud.gatewaynet.com>; pgsql-general@lists.postgresql.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.kla...@aklaver.com > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
