Re: High System CPU Usage on Selects Seemingly Caused By Vacuum of Same Table

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Joshua Banton <[email protected]>
To: Peter Geoghegan <[email protected]>
Cc: [email protected]
Subject: Re: High System CPU Usage on Selects Seemingly Caused By Vacuum of Same Table
Date: Fri, 31 Jan 2025 17:59:42 -0500
Message-ID: <CAHkYM9YXAXGbmWTNhPGMHjS2zgcuOYsXjQr2B1h2CS3KO5E=NQ@mail.gmail.com> (raw)
In-Reply-To: <CAH2-WzmZEQn7jH3iYZvsgMJ93Z=POaGcgu4BSF73_vSgoZUKcg@mail.gmail.com>
References: <CAHkYM9bmCEq8J=tUMYr6HRNL03ddUX3JUPCKY6frngBCbuPrGw@mail.gmail.com>
	<CAH2-WzmZEQn7jH3iYZvsgMJ93Z=POaGcgu4BSF73_vSgoZUKcg@mail.gmail.com>

There are not that many tables, 175.

There are a decent number of transactions on the database, 35k a second
during the day. I ran a vacuum verbose on table1 today and got this output
on the pages frozen.

frozen: 2813436 pages from table (0.70% of total) had 7028123 tuples frozen



On Fri, Jan 31, 2025 at 5:46 PM Peter Geoghegan <[email protected]> wrote:

> On Fri, Jan 31, 2025 at 5:28 PM Joshua Banton <[email protected]> wrote:
> > The issue mostly manifests near the end of the "scanning heap" phase of
> vacuuming of one of our largest tables, we'll call table1. RDS Performance
> Insights reports that selects on table1 start to wait on cpu, where
> previously it didn't even show up in the top 25 queries by wait. It doesn't
> always happen, but if there is a larger than usual number of selects on
> table1 it is more likely to happen.
>
> Does this database also have many tables? As in thousands of tables?
>
> I am reminded of this issue:
>
>
> https://www.postgresql.org/message-id/flat/da3205c4-5b07-a65c-6c26-a293c6464fdb%40postgrespro.ru
>
> I've heard of this happening when an aggressive VACUUM updates
> relfrozenxid on a larger table.
>
> --
> Peter Geoghegan
>

view thread (3+ messages)

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: High System CPU Usage on Selects Seemingly Caused By Vacuum of Same Table
  In-Reply-To: <CAHkYM9YXAXGbmWTNhPGMHjS2zgcuOYsXjQr2B1h2CS3KO5E=NQ@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