From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marcin Gozdalik <gozdal@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very slow "bloat query"
Date: Fri, 14 May 2021 11:04:02 -0400
Message-ID: <1884331.1621004642@sss.pgh.pa.us> (raw)
In-Reply-To: <CADu1mRN1PPXO0p2HUF4NkrLSLmdiXXcafm5B6r2kk7Tawd1UoQ@mail.gmail.com>
References: <CADu1mRN1PPXO0p2HUF4NkrLSLmdiXXcafm5B6r2kk7Tawd1UoQ@mail.gmail.com>
Marcin Gozdalik <gozdal@gmail.com> writes:
> I have traced the problem to the bloated `pg_class` (the irony: `pgmetrics`
> does not collect bloat on `pg_catalog`):
> `vacuum (full, analyze, verbose) pg_class;`
> ```
> INFO: vacuuming "pg_catalog.pg_class"
> INFO: "pg_class": found 1 removable, 7430805 nonremovable row versions in
> 158870 pages
> DETAIL: 7429943 dead row versions cannot be removed yet.
Ugh. It's understandable that having a lot of temp-table traffic
would result in the creation of lots of dead rows in pg_class.
The question to be asking is why aren't they vacuumable? You
must have a longstanding open transaction somewhere (perhaps
a forgotten prepared transaction?) that is holding back the
global xmin horizon. Closing that out and then doing another
manual VACUUM FULL should help.
regards, tom lane
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: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, gozdal@gmail.com
Subject: Re: Very slow "bloat query"
In-Reply-To: <1884331.1621004642@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox