Re: Very slow "bloat query"
| От | Marcin Gozdalik |
|---|---|
| Тема | Re: Very slow "bloat query" |
| Дата | |
| Msg-id | CADu1mROBKqpJA+trsp1psRd4gObY8hPivXSLuN3RGfBQT6OdJA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Very slow "bloat query" (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-performance |
There is a long running analytics query (which is running usually for 30-40 hours). I agree that's not the best position to be in but right now can't do anything about it.
pt., 14 maj 2021 o 15:04 Tom Lane <tgl@sss.pgh.pa.us> napisał(a):
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
--
Marcin Gozdalik
В списке pgsql-performance по дате отправления: