public inbox for [email protected]
help / color / mirror / Atom feedvacuuming taking long time for pg_attribute
4+ messages / 3 participants
[nested] [flat]
* vacuuming taking long time for pg_attribute
@ 2025-01-17 06:15 Siraj G <[email protected]>
2025-01-17 06:16 ` Re: vacuuming taking long time for pg_attribute Siraj G <[email protected]>
2025-01-17 08:46 ` Re: vacuuming taking long time for pg_attribute Laurenz Albe <[email protected]>
2025-01-19 02:15 ` Re: vacuuming taking long time for pg_attribute Jeff Janes <[email protected]>
0 siblings, 3 replies; 4+ messages in thread
From: Siraj G @ 2025-01-17 06:15 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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.
Appreciate any suggestions.
Regards
Siraj
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuuming taking long time for pg_attribute
2025-01-17 06:15 vacuuming taking long time for pg_attribute Siraj G <[email protected]>
@ 2025-01-17 06:16 ` Siraj G <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Siraj G @ 2025-01-17 06:16 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
One other thing I would like to add..
While the performance was problem, our application jobs also failed and we
were unable to start them as internally they query pg_attribute.
Regards
Siraj
On Fri, Jan 17, 2025 at 11:45 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.
>
> Appreciate any suggestions.
>
> Regards
> Siraj
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuuming taking long time for pg_attribute
2025-01-17 06:15 vacuuming taking long time for pg_attribute Siraj G <[email protected]>
@ 2025-01-17 08:46 ` Laurenz Albe <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2025-01-17 08:46 UTC (permalink / raw)
To: Siraj G <[email protected]>; Pgsql-admin <[email protected]>
On Fri, 2025-01-17 at 11:45 +0530, Siraj G wrote:
> 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.
>
> Appreciate any suggestions.
I can only guess, but my guess is that you are creating temporary tables
very frequently. Creating a temporary table insers the columns into
"pg_attribute", and they get deleted again when the session ends.
Perhaps autovacuum was just not fast enough, perhaps there were
long-running transactions or queries that prevented autovacuum from cleaning
up the dead rows in "pg_attribute".
Make sure your transactions are short and that autovacuum is configured
sufficiently aggressive.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuuming taking long time for pg_attribute
2025-01-17 06:15 vacuuming taking long time for pg_attribute Siraj G <[email protected]>
@ 2025-01-19 02:15 ` Jeff Janes <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Jeff Janes @ 2025-01-19 02:15 UTC (permalink / raw)
To: Siraj G <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-01-19 02:15 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-17 06:15 vacuuming taking long time for pg_attribute Siraj G <[email protected]>
2025-01-17 06:16 ` Siraj G <[email protected]>
2025-01-17 08:46 ` Laurenz Albe <[email protected]>
2025-01-19 02:15 ` Jeff Janes <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox