MIME-Version: 1.0
References: 
 <CAC5iy63HKROj55PSZ3Ls7WmXVWi-DvRaqqRpp_7A6g1QTPAb=Q@mail.gmail.com>
In-Reply-To: 
 <CAC5iy63HKROj55PSZ3Ls7WmXVWi-DvRaqqRpp_7A6g1QTPAb=Q@mail.gmail.com>
From: Jeff Janes <jeff.janes@gmail.com>
Date: Sat, 18 Jan 2025 21:15:37 -0500
Message-ID: 
 <CAMkU=1zFRdNrHfugMUkhqNPJtoAN=TJm1opKz=3ugbJrxM9PmA@mail.gmail.com>
Subject: Re: vacuuming taking long time for pg_attribute
To: Siraj G <tosiraj.g@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f5614e062c05b80f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMkU%3D1zFRdNrHfugMUkhqNPJtoAN%3DTJm1opKz%3D3ugbJrxM9PmA%40mail.gmail.com>
Precedence: bulk

--000000000000f5614e062c05b80f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Jan 17, 2025 at 1:15=E2=80=AFAM Siraj G <tosiraj.g@gmail.com> 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 timeou=
t
> 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 abou=
t
> 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

>

--000000000000f5614e062c05b80f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Jan 17, 2025 at 1:15=E2=80=AFAM S=
iraj G &lt;<a href=3D"mailto:tosiraj.g@gmail.com">tosiraj.g@gmail.com</a>&g=
t; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote=
 class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">He=
llo Experts!<div><br></div><div>The PgSQL version is 16 and it runs in Clou=
d SQL managed by GCP.</div><div><br></div><div>Problem was that we were una=
ble to conveniently=C2=A0get the object details in the schema browser (with=
in 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_att=
ribute was having tons of dead tuples. While vacuum on this took several ho=
urs, the problem got resolved eventually.</div><div>Command ran: vacuum pg_=
attribute;</div><div><br></div><div>I would like to understand how this iss=
ue can be prevented. We do have autovacuum ON and I could see the last vacu=
um on this table was just about 30hrs back.</div></div></div></blockquote><=
div><br></div><div>It might be hard to figure it out now that=C2=A0the evid=
ence has likely been destroyed.=C2=A0 When looking at when the last autovac=
 finished, did you also happen to capture=C2=A0the other column=C2=A0values=
 in pg_stat_sys_tables for that relname?</div><div><br></div><div>Look in t=
he db server log file to see if there were failed or canceled autovacuum at=
tempts on that table.</div><div><br></div><div>I would probably set log_aut=
ovacuum_min_duration to a smallish value so that if it happens again you ha=
ve some evidence to look at.</div><div><br></div><div>Cheers,</div><div><br=
></div><div>Jeff</div><blockquote class=3D"gmail_quote" style=3D"margin:0px=
 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><di=
v dir=3D"ltr">
</div>
</blockquote></div></div>

--000000000000f5614e062c05b80f--