public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jeff Janes <[email protected]>
To: Siraj G <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: vacuuming taking long time for pg_attribute
Date: Sat, 18 Jan 2025 21:15:37 -0500
Message-ID: <CAMkU=1zFRdNrHfugMUkhqNPJtoAN=TJm1opKz=3ugbJrxM9PmA@mail.gmail.com> (raw)
In-Reply-To: <CAC5iy63HKROj55PSZ3Ls7WmXVWi-DvRaqqRpp_7A6g1QTPAb=Q@mail.gmail.com>
References: <CAC5iy63HKROj55PSZ3Ls7WmXVWi-DvRaqqRpp_7A6g1QTPAb=Q@mail.gmail.com>

On Fri, Jan 17, 2025 at 1:15 AM Siraj G <[email protected]> wrote:

> Hello Experts!
>
> The PgSQL version is 16 and it runs in Cloud SQL managed by GCP.
>
> Problem was that we were unable to conveniently get the object details in
> the schema browser (within the Cloud SQL Studio) as it was getting timeout
> again and again. Several application jobs start to fail. Eventually we
> found that pg_catalog.pg_attribute was having tons of dead tuples. While
> vacuum on this took several hours, the problem got resolved eventually.
> Command ran: vacuum pg_attribute;
>
> I would like to understand how this issue can be prevented. We do have
> autovacuum ON and I could see the last vacuum on this table was just about
> 30hrs back.
>

It might be hard to figure it out now that the evidence has likely been
destroyed.  When looking at when the last autovac finished, did you also
happen to capture the other column values in pg_stat_sys_tables for that
relname?

Look in the db server log file to see if there were failed or canceled
autovacuum attempts on that table.

I would probably set log_autovacuum_min_duration to a smallish value so
that if it happens again you have some evidence to look at.

Cheers,

Jeff

>

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]
  Subject: Re: vacuuming taking long time for pg_attribute
  In-Reply-To: <CAMkU=1zFRdNrHfugMUkhqNPJtoAN=TJm1opKz=3ugbJrxM9PmA@mail.gmail.com>

* 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