public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Shenavai, Manuel <[email protected]>
To: Achilleas Mantzios <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Autovacuum, dead tuples and bloat
Date: Sat, 22 Jun 2024 14:16:51 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <AM9PR02MB7410F50DD4C2AFBD1E6C9032E8CA2@AM9PR02MB7410.eurprd02.prod.outlook.com>
References: <AM9PR02MB7410603C5F467DED657D5330E8C82@AM9PR02MB7410.eurprd02.prod.outlook.com>
	<[email protected]>
	<AM9PR02MB7410AA1DEA442AED52FBB5E9E8C92@AM9PR02MB7410.eurprd02.prod.outlook.com>
	<[email protected]>
	<AM9PR02MB7410F50DD4C2AFBD1E6C9032E8CA2@AM9PR02MB7410.eurprd02.prod.outlook.com>

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 <[email protected]>
> Sent: 21 June 2024 22:39
> To: Shenavai, Manuel <[email protected]>; Achilleas Mantzios <[email protected]>; [email protected]
> 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
> [email protected]
> 

-- 
Adrian Klaver
[email protected]

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Autovacuum, dead tuples and bloat
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox