pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVery slow "bloat query"
8+ messages / 4 participants
[nested] [flat]
* Very slow "bloat query"
@ 2021-05-14 11:06 Marcin Gozdalik <gozdal@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Marcin Gozdalik @ 2021-05-14 11:06 UTC (permalink / raw)
To: pgsql-performance
Hi
I am trying to use `pgmetrics` on a big (10TB+), busy (1GB/s RW) database.
It takes around 5 minutes for pgmetrics to run. I traced the problem to the
"bloat query" (version of
https://wiki.postgresql.org/wiki/Show_database_bloat) spinning in CPU,
doing no I/O.
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.
CPU 1.36s/6.40u sec elapsed 9.85 sec.
INFO: analyzing "pg_catalog.pg_class"
INFO: "pg_class": scanned 60000 of 158869 pages, containing 295 live rows
and 2806547 dead rows; 295 rows in sample, 781 estimated total rows
VACUUM
```
`pg_class` has so many dead rows because the workload is temp-table heavy
(creating/destroying 1M+ temporary tables per day) and has long running
analytics queries running for 24h+.
PG query planner assumes that index scan on `pg_class` will be very quick
and plans Nested loop with Index scan. However, the index scan has 7M dead
tuples to filter out and the query takes more than 200 seconds (
https://explain.depesz.com/s/bw2G).
If I create a temp table from `pg_class` to contain only the live tuples:
```
CREATE TEMPORARY TABLE pg_class_alive AS SELECT oid,* from pg_class;
CREATE UNIQUE INDEX pg_class_alive_oid_index ON pg_class_alive(oid);
CREATE UNIQUE INDEX pg_class_alive_relname_nsp_index ON
pg_class_alive(relname, relnamespace);
CREATE INDEX pg_class_tblspc_relfilenode_index ON
pg_class_alive(reltablespace, relfilenode);
ANALYZE pg_class_alive;
```
and run the bloat query on `pg_class_alive` instead of `pg_class`:
```
SELECT
nn.nspname AS schemaname,
cc.relname AS tablename,
COALESCE(cc.reltuples,0) AS reltuples,
COALESCE(cc.relpages,0) AS relpages,
COALESCE(CEIL((cc.reltuples*((datahdr+8-
(CASE WHEN datahdr%8=0 THEN 8 ELSE datahdr%8
END))+nullhdr2+4))/(8192-20::float)),0) AS otta
FROM
pg_class_alive cc
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <>
'information_schema'
LEFT JOIN
(
SELECT
foo.nspname,foo.relname,
(datawidth+32)::numeric AS datahdr,
(maxfracsum*(nullhdr+8-(case when nullhdr%8=0 THEN 8 ELSE nullhdr%8
END))) AS nullhdr2
FROM (
SELECT
ns.nspname, tbl.relname,
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS
datawidth,
MAX(coalesce(null_frac,0)) AS maxfracsum,
23+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename
= tbl.relname
) AS nullhdr
FROM pg_attribute att
JOIN pg_class_alive tbl ON att.attrelid = tbl.oid
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname
WHERE att.attnum > 0 AND tbl.relkind='r'
GROUP BY 1,2
) AS foo
) AS rs
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class_alive c2 ON c2.oid = i.indexrelid
```
it runs in 10s, 20x faster (https://explain.depesz.com/s/K4SH)
The rabbit hole probably goes deeper (e.g. should do the same for
pg_statistic and pg_attribute and create a new pg_stats view).
I am not able (at least not quickly) change the amount of temporary tables
created or make the analytics queries finish quicker. Apart from the above
hack of filtering out live tuples to a separate table is there anything I
could do?
Thank you,
Marcin Gozdalik
--
Marcin Gozdalik
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Very slow "bloat query"
@ 2021-05-14 11:50 Gilles Darold <gilles@darold.net>
parent: Marcin Gozdalik <gozdal@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Gilles Darold @ 2021-05-14 11:50 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Le 14/05/2021 à 13:06, Marcin Gozdalik a écrit :
> Hi
>
> I am trying to use `pgmetrics` on a big (10TB+), busy (1GB/s RW)
> database. It takes around 5 minutes for pgmetrics to run. I traced the
> problem to the "bloat query" (version of
> https://wiki.postgresql.org/wiki/Show_database_bloat
> <https://wiki.postgresql.org/wiki/Show_database_bloat;) spinning in
> CPU, doing no I/O.
>
> 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.
> CPU 1.36s/6.40u sec elapsed 9.85 sec.
> INFO: analyzing "pg_catalog.pg_class"
> INFO: "pg_class": scanned 60000 of 158869 pages, containing 295 live
> rows and 2806547 dead rows; 295 rows in sample, 781 estimated total rows
> VACUUM
> ```
>
> `pg_class` has so many dead rows because the workload is temp-table
> heavy (creating/destroying 1M+ temporary tables per day) and has long
> running analytics queries running for 24h+.
>
> PG query planner assumes that index scan on `pg_class` will be very
> quick and plans Nested loop with Index scan. However, the index scan
> has 7M dead tuples to filter out and the query takes more than 200
> seconds (https://explain.depesz.com/s/bw2G
> <https://explain.depesz.com/s/bw2G;).
>
> If I create a temp table from `pg_class` to contain only the live tuples:
> ```
> CREATE TEMPORARY TABLE pg_class_alive AS SELECT oid,* from pg_class;
> CREATE UNIQUE INDEX pg_class_alive_oid_index ON pg_class_alive(oid);
> CREATE UNIQUE INDEX pg_class_alive_relname_nsp_index ON
> pg_class_alive(relname, relnamespace);
> CREATE INDEX pg_class_tblspc_relfilenode_index ON
> pg_class_alive(reltablespace, relfilenode);
> ANALYZE pg_class_alive;
> ```
>
> and run the bloat query on `pg_class_alive` instead of `pg_class`:
> ```
> SELECT
> nn.nspname AS schemaname,
> cc.relname AS tablename,
> COALESCE(cc.reltuples,0) AS reltuples,
> COALESCE(cc.relpages,0) AS relpages,
> COALESCE(CEIL((cc.reltuples*((datahdr+8-
> (CASE WHEN datahdr%8=0 THEN 8 ELSE datahdr%8
> END))+nullhdr2+4))/(8192-20::float)),0) AS otta
> FROM
> pg_class_alive cc
> JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <>
> 'information_schema'
> LEFT JOIN
> (
> SELECT
> foo.nspname,foo.relname,
> (datawidth+32)::numeric AS datahdr,
> (maxfracsum*(nullhdr+8-(case when nullhdr%8=0 THEN 8 ELSE
> nullhdr%8 END))) AS nullhdr2
> FROM (
> SELECT
> ns.nspname, tbl.relname,
> SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS
> datawidth,
> MAX(coalesce(null_frac,0)) AS maxfracsum,
> 23+(
> SELECT 1+count(*)/8
> FROM pg_stats s2
> WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND
> s2.tablename = tbl.relname
> ) AS nullhdr
> FROM pg_attribute att
> JOIN pg_class_alive tbl ON att.attrelid = tbl.oid
> JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
> LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
> AND s.tablename = tbl.relname
> AND s.inherited=false
> AND s.attname=att.attname
> WHERE att.attnum > 0 AND tbl.relkind='r'
> GROUP BY 1,2
> ) AS foo
> ) AS rs
> ON cc.relname = rs.relname AND nn.nspname = rs.nspname
> LEFT JOIN pg_index i ON indrelid = cc.oid
> LEFT JOIN pg_class_alive c2 ON c2.oid = i.indexrelid
> ```
>
> it runs in 10s, 20x faster (https://explain.depesz.com/s/K4SH
> <https://explain.depesz.com/s/K4SH;)
>
> The rabbit hole probably goes deeper (e.g. should do the same for
> pg_statistic and pg_attribute and create a new pg_stats view).
>
> I am not able (at least not quickly) change the amount of temporary
> tables created or make the analytics queries finish quicker. Apart
> from the above hack of filtering out live tuples to a separate table
> is there anything I could do?
Hi,
To avoid bloating your catalog with temporary tables you can try using
https://github.com/darold/pgtt-rsl I don't know if it will fit the
performances but at least you will not bloat the catalog anymore.
About your hack, I don't see other solution except running vacuum on the
catalog tables more often, but I guess that this is already done or not
possible. But not bloating the catalog at such level is the right solution.
--
Gilles Darold
http://www.darold.net/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Very slow "bloat query"
@ 2021-05-14 12:07 Imre Samu <pella.samu@gmail.com>
parent: Marcin Gozdalik <gozdal@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Imre Samu @ 2021-05-14 12:07 UTC (permalink / raw)
To: Marcin Gozdalik <gozdal@gmail.com>; +Cc: pgsql-performance
> Apart from the above hack of filtering out live tuples to a separate
table is there anything I could do?
This is the latest PG13.3 version?
IMHO: If not, maybe worth updating to the latest patch release, as soon
as possible
https://www.postgresql.org/docs/release/13.3/
Release date: 2021-05-13
*"Disable the vacuum_cleanup_index_scale_factor parameter and storage
option (Peter Geoghegan)*
*The notion of tracking “stale” index statistics proved to interact badly
with the autovacuum_vacuum_insert_threshold parameter, resulting in
unnecessary full-index scans and consequent degradation of autovacuum
performance. The latter mechanism seems superior, so remove the
stale-statistics logic. The control parameter for that,
vacuum_cleanup_index_scale_factor, will be removed entirely in v14. In v13,
it remains present to avoid breaking existing configuration files, but it
no longer does anything."*
best,
Imre
Marcin Gozdalik <gozdal@gmail.com> ezt írta (időpont: 2021. máj. 14., P,
13:20):
> Hi
>
> I am trying to use `pgmetrics` on a big (10TB+), busy (1GB/s RW) database.
> It takes around 5 minutes for pgmetrics to run. I traced the problem to the
> "bloat query" (version of
> https://wiki.postgresql.org/wiki/Show_database_bloat) spinning in CPU,
> doing no I/O.
>
> 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.
> CPU 1.36s/6.40u sec elapsed 9.85 sec.
> INFO: analyzing "pg_catalog.pg_class"
> INFO: "pg_class": scanned 60000 of 158869 pages, containing 295 live rows
> and 2806547 dead rows; 295 rows in sample, 781 estimated total rows
> VACUUM
> ```
>
> `pg_class` has so many dead rows because the workload is temp-table heavy
> (creating/destroying 1M+ temporary tables per day) and has long running
> analytics queries running for 24h+.
>
> PG query planner assumes that index scan on `pg_class` will be very quick
> and plans Nested loop with Index scan. However, the index scan has 7M dead
> tuples to filter out and the query takes more than 200 seconds (
> https://explain.depesz.com/s/bw2G).
>
> If I create a temp table from `pg_class` to contain only the live tuples:
> ```
> CREATE TEMPORARY TABLE pg_class_alive AS SELECT oid,* from pg_class;
> CREATE UNIQUE INDEX pg_class_alive_oid_index ON pg_class_alive(oid);
> CREATE UNIQUE INDEX pg_class_alive_relname_nsp_index ON
> pg_class_alive(relname, relnamespace);
> CREATE INDEX pg_class_tblspc_relfilenode_index ON
> pg_class_alive(reltablespace, relfilenode);
> ANALYZE pg_class_alive;
> ```
>
> and run the bloat query on `pg_class_alive` instead of `pg_class`:
> ```
> SELECT
> nn.nspname AS schemaname,
> cc.relname AS tablename,
> COALESCE(cc.reltuples,0) AS reltuples,
> COALESCE(cc.relpages,0) AS relpages,
> COALESCE(CEIL((cc.reltuples*((datahdr+8-
> (CASE WHEN datahdr%8=0 THEN 8 ELSE datahdr%8
> END))+nullhdr2+4))/(8192-20::float)),0) AS otta
> FROM
> pg_class_alive cc
> JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <>
> 'information_schema'
> LEFT JOIN
> (
> SELECT
> foo.nspname,foo.relname,
> (datawidth+32)::numeric AS datahdr,
> (maxfracsum*(nullhdr+8-(case when nullhdr%8=0 THEN 8 ELSE nullhdr%8
> END))) AS nullhdr2
> FROM (
> SELECT
> ns.nspname, tbl.relname,
> SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS
> datawidth,
> MAX(coalesce(null_frac,0)) AS maxfracsum,
> 23+(
> SELECT 1+count(*)/8
> FROM pg_stats s2
> WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND
> s2.tablename = tbl.relname
> ) AS nullhdr
> FROM pg_attribute att
> JOIN pg_class_alive tbl ON att.attrelid = tbl.oid
> JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
> LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
> AND s.tablename = tbl.relname
> AND s.inherited=false
> AND s.attname=att.attname
> WHERE att.attnum > 0 AND tbl.relkind='r'
> GROUP BY 1,2
> ) AS foo
> ) AS rs
> ON cc.relname = rs.relname AND nn.nspname = rs.nspname
> LEFT JOIN pg_index i ON indrelid = cc.oid
> LEFT JOIN pg_class_alive c2 ON c2.oid = i.indexrelid
> ```
>
> it runs in 10s, 20x faster (https://explain.depesz.com/s/K4SH)
>
> The rabbit hole probably goes deeper (e.g. should do the same for
> pg_statistic and pg_attribute and create a new pg_stats view).
>
> I am not able (at least not quickly) change the amount of temporary tables
> created or make the analytics queries finish quicker. Apart from the above
> hack of filtering out live tuples to a separate table is there anything I
> could do?
>
> Thank you,
> Marcin Gozdalik
>
> --
> Marcin Gozdalik
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Very slow "bloat query"
@ 2021-05-14 12:11 Marcin Gozdalik <gozdal@gmail.com>
parent: Imre Samu <pella.samu@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Marcin Gozdalik @ 2021-05-14 12:11 UTC (permalink / raw)
To: Imre Samu <pella.samu@gmail.com>; +Cc: pgsql-performance
Unfortunately it's still 9.6. Upgrade to latest 13 is planned for this year.
pt., 14 maj 2021 o 12:08 Imre Samu <pella.samu@gmail.com> napisał(a):
> > Apart from the above hack of filtering out live tuples to a separate
> table is there anything I could do?
>
> This is the latest PG13.3 version?
>
> IMHO: If not, maybe worth updating to the latest patch release, as soon
> as possible
>
> https://www.postgresql.org/docs/release/13.3/
> Release date: 2021-05-13
> *"Disable the vacuum_cleanup_index_scale_factor parameter and storage
> option (Peter Geoghegan)*
> *The notion of tracking “stale” index statistics proved to interact badly
> with the autovacuum_vacuum_insert_threshold parameter, resulting in
> unnecessary full-index scans and consequent degradation of autovacuum
> performance. The latter mechanism seems superior, so remove the
> stale-statistics logic. The control parameter for that,
> vacuum_cleanup_index_scale_factor, will be removed entirely in v14. In v13,
> it remains present to avoid breaking existing configuration files, but it
> no longer does anything."*
>
> best,
> Imre
>
>
> Marcin Gozdalik <gozdal@gmail.com> ezt írta (időpont: 2021. máj. 14., P,
> 13:20):
>
>> Hi
>>
>> I am trying to use `pgmetrics` on a big (10TB+), busy (1GB/s RW)
>> database. It takes around 5 minutes for pgmetrics to run. I traced the
>> problem to the "bloat query" (version of
>> https://wiki.postgresql.org/wiki/Show_database_bloat) spinning in CPU,
>> doing no I/O.
>>
>> 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.
>> CPU 1.36s/6.40u sec elapsed 9.85 sec.
>> INFO: analyzing "pg_catalog.pg_class"
>> INFO: "pg_class": scanned 60000 of 158869 pages, containing 295 live
>> rows and 2806547 dead rows; 295 rows in sample, 781 estimated total rows
>> VACUUM
>> ```
>>
>> `pg_class` has so many dead rows because the workload is temp-table heavy
>> (creating/destroying 1M+ temporary tables per day) and has long running
>> analytics queries running for 24h+.
>>
>> PG query planner assumes that index scan on `pg_class` will be very quick
>> and plans Nested loop with Index scan. However, the index scan has 7M dead
>> tuples to filter out and the query takes more than 200 seconds (
>> https://explain.depesz.com/s/bw2G).
>>
>> If I create a temp table from `pg_class` to contain only the live tuples:
>> ```
>> CREATE TEMPORARY TABLE pg_class_alive AS SELECT oid,* from pg_class;
>> CREATE UNIQUE INDEX pg_class_alive_oid_index ON pg_class_alive(oid);
>> CREATE UNIQUE INDEX pg_class_alive_relname_nsp_index ON
>> pg_class_alive(relname, relnamespace);
>> CREATE INDEX pg_class_tblspc_relfilenode_index ON
>> pg_class_alive(reltablespace, relfilenode);
>> ANALYZE pg_class_alive;
>> ```
>>
>> and run the bloat query on `pg_class_alive` instead of `pg_class`:
>> ```
>> SELECT
>> nn.nspname AS schemaname,
>> cc.relname AS tablename,
>> COALESCE(cc.reltuples,0) AS reltuples,
>> COALESCE(cc.relpages,0) AS relpages,
>> COALESCE(CEIL((cc.reltuples*((datahdr+8-
>> (CASE WHEN datahdr%8=0 THEN 8 ELSE datahdr%8
>> END))+nullhdr2+4))/(8192-20::float)),0) AS otta
>> FROM
>> pg_class_alive cc
>> JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <>
>> 'information_schema'
>> LEFT JOIN
>> (
>> SELECT
>> foo.nspname,foo.relname,
>> (datawidth+32)::numeric AS datahdr,
>> (maxfracsum*(nullhdr+8-(case when nullhdr%8=0 THEN 8 ELSE nullhdr%8
>> END))) AS nullhdr2
>> FROM (
>> SELECT
>> ns.nspname, tbl.relname,
>> SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS
>> datawidth,
>> MAX(coalesce(null_frac,0)) AS maxfracsum,
>> 23+(
>> SELECT 1+count(*)/8
>> FROM pg_stats s2
>> WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND
>> s2.tablename = tbl.relname
>> ) AS nullhdr
>> FROM pg_attribute att
>> JOIN pg_class_alive tbl ON att.attrelid = tbl.oid
>> JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
>> LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
>> AND s.tablename = tbl.relname
>> AND s.inherited=false
>> AND s.attname=att.attname
>> WHERE att.attnum > 0 AND tbl.relkind='r'
>> GROUP BY 1,2
>> ) AS foo
>> ) AS rs
>> ON cc.relname = rs.relname AND nn.nspname = rs.nspname
>> LEFT JOIN pg_index i ON indrelid = cc.oid
>> LEFT JOIN pg_class_alive c2 ON c2.oid = i.indexrelid
>> ```
>>
>> it runs in 10s, 20x faster (https://explain.depesz.com/s/K4SH)
>>
>> The rabbit hole probably goes deeper (e.g. should do the same for
>> pg_statistic and pg_attribute and create a new pg_stats view).
>>
>> I am not able (at least not quickly) change the amount of temporary
>> tables created or make the analytics queries finish quicker. Apart from the
>> above hack of filtering out live tuples to a separate table is there
>> anything I could do?
>>
>> Thank you,
>> Marcin Gozdalik
>>
>> --
>> Marcin Gozdalik
>>
>
--
Marcin Gozdalik
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Very slow "bloat query"
@ 2021-05-14 15:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marcin Gozdalik <gozdal@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2021-05-14 15:04 UTC (permalink / raw)
To: Marcin Gozdalik <gozdal@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Very slow "bloat query"
@ 2021-05-14 15:15 Marcin Gozdalik <gozdal@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Marcin Gozdalik @ 2021-05-14 15:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Very slow "bloat query"
@ 2021-05-14 15:44 Imre Samu <pella.samu@gmail.com>
parent: Marcin Gozdalik <gozdal@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Imre Samu @ 2021-05-14 15:44 UTC (permalink / raw)
To: Marcin Gozdalik <gozdal@gmail.com>; +Cc: pgsql-performance
> Unfortunately it's still 9.6.
And what is your "*version()*"?
for example:
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.22 on x86_64-pc-linux-gnu (Debian 9.6.22-1.pgdg110+1),
compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
Imre
Marcin Gozdalik <gozdal@gmail.com> ezt írta (időpont: 2021. máj. 14., P,
14:11):
> Unfortunately it's still 9.6. Upgrade to latest 13 is planned for this
> year.
>
> pt., 14 maj 2021 o 12:08 Imre Samu <pella.samu@gmail.com> napisał(a):
>
>> > Apart from the above hack of filtering out live tuples to a separate
>> table is there anything I could do?
>>
>> This is the latest PG13.3 version?
>>
>> IMHO: If not, maybe worth updating to the latest patch release, as soon
>> as possible
>>
>> https://www.postgresql.org/docs/release/13.3/
>> Release date: 2021-05-13
>> *"Disable the vacuum_cleanup_index_scale_factor parameter and storage
>> option (Peter Geoghegan)*
>> *The notion of tracking “stale” index statistics proved to interact badly
>> with the autovacuum_vacuum_insert_threshold parameter, resulting in
>> unnecessary full-index scans and consequent degradation of autovacuum
>> performance. The latter mechanism seems superior, so remove the
>> stale-statistics logic. The control parameter for that,
>> vacuum_cleanup_index_scale_factor, will be removed entirely in v14. In v13,
>> it remains present to avoid breaking existing configuration files, but it
>> no longer does anything."*
>>
>> best,
>> Imre
>>
>>
>> Marcin Gozdalik <gozdal@gmail.com> ezt írta (időpont: 2021. máj. 14., P,
>> 13:20):
>>
>>> Hi
>>>
>>> I am trying to use `pgmetrics` on a big (10TB+), busy (1GB/s RW)
>>> database. It takes around 5 minutes for pgmetrics to run. I traced the
>>> problem to the "bloat query" (version of
>>> https://wiki.postgresql.org/wiki/Show_database_bloat) spinning in CPU,
>>> doing no I/O.
>>>
>>> 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.
>>> CPU 1.36s/6.40u sec elapsed 9.85 sec.
>>> INFO: analyzing "pg_catalog.pg_class"
>>> INFO: "pg_class": scanned 60000 of 158869 pages, containing 295 live
>>> rows and 2806547 dead rows; 295 rows in sample, 781 estimated total rows
>>> VACUUM
>>> ```
>>>
>>> `pg_class` has so many dead rows because the workload is temp-table
>>> heavy (creating/destroying 1M+ temporary tables per day) and has long
>>> running analytics queries running for 24h+.
>>>
>>> PG query planner assumes that index scan on `pg_class` will be very
>>> quick and plans Nested loop with Index scan. However, the index scan has 7M
>>> dead tuples to filter out and the query takes more than 200 seconds (
>>> https://explain.depesz.com/s/bw2G).
>>>
>>> If I create a temp table from `pg_class` to contain only the live tuples:
>>> ```
>>> CREATE TEMPORARY TABLE pg_class_alive AS SELECT oid,* from pg_class;
>>> CREATE UNIQUE INDEX pg_class_alive_oid_index ON pg_class_alive(oid);
>>> CREATE UNIQUE INDEX pg_class_alive_relname_nsp_index ON
>>> pg_class_alive(relname, relnamespace);
>>> CREATE INDEX pg_class_tblspc_relfilenode_index ON
>>> pg_class_alive(reltablespace, relfilenode);
>>> ANALYZE pg_class_alive;
>>> ```
>>>
>>> and run the bloat query on `pg_class_alive` instead of `pg_class`:
>>> ```
>>> SELECT
>>> nn.nspname AS schemaname,
>>> cc.relname AS tablename,
>>> COALESCE(cc.reltuples,0) AS reltuples,
>>> COALESCE(cc.relpages,0) AS relpages,
>>> COALESCE(CEIL((cc.reltuples*((datahdr+8-
>>> (CASE WHEN datahdr%8=0 THEN 8 ELSE datahdr%8
>>> END))+nullhdr2+4))/(8192-20::float)),0) AS otta
>>> FROM
>>> pg_class_alive cc
>>> JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <>
>>> 'information_schema'
>>> LEFT JOIN
>>> (
>>> SELECT
>>> foo.nspname,foo.relname,
>>> (datawidth+32)::numeric AS datahdr,
>>> (maxfracsum*(nullhdr+8-(case when nullhdr%8=0 THEN 8 ELSE nullhdr%8
>>> END))) AS nullhdr2
>>> FROM (
>>> SELECT
>>> ns.nspname, tbl.relname,
>>> SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS
>>> datawidth,
>>> MAX(coalesce(null_frac,0)) AS maxfracsum,
>>> 23+(
>>> SELECT 1+count(*)/8
>>> FROM pg_stats s2
>>> WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND
>>> s2.tablename = tbl.relname
>>> ) AS nullhdr
>>> FROM pg_attribute att
>>> JOIN pg_class_alive tbl ON att.attrelid = tbl.oid
>>> JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
>>> LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
>>> AND s.tablename = tbl.relname
>>> AND s.inherited=false
>>> AND s.attname=att.attname
>>> WHERE att.attnum > 0 AND tbl.relkind='r'
>>> GROUP BY 1,2
>>> ) AS foo
>>> ) AS rs
>>> ON cc.relname = rs.relname AND nn.nspname = rs.nspname
>>> LEFT JOIN pg_index i ON indrelid = cc.oid
>>> LEFT JOIN pg_class_alive c2 ON c2.oid = i.indexrelid
>>> ```
>>>
>>> it runs in 10s, 20x faster (https://explain.depesz.com/s/K4SH)
>>>
>>> The rabbit hole probably goes deeper (e.g. should do the same for
>>> pg_statistic and pg_attribute and create a new pg_stats view).
>>>
>>> I am not able (at least not quickly) change the amount of temporary
>>> tables created or make the analytics queries finish quicker. Apart from the
>>> above hack of filtering out live tuples to a separate table is there
>>> anything I could do?
>>>
>>> Thank you,
>>> Marcin Gozdalik
>>>
>>> --
>>> Marcin Gozdalik
>>>
>>
>
> --
> Marcin Gozdalik
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Very slow "bloat query"
@ 2021-05-14 15:47 Marcin Gozdalik <gozdal@gmail.com>
parent: Imre Samu <pella.samu@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Marcin Gozdalik @ 2021-05-14 15:47 UTC (permalink / raw)
To: Imre Samu <pella.samu@gmail.com>; +Cc: pgsql-performance
PostgreSQL 9.6.21 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-44), 64-bit
pt., 14 maj 2021 o 15:45 Imre Samu <pella.samu@gmail.com> napisał(a):
> > Unfortunately it's still 9.6.
>
> And what is your "*version()*"?
>
>
> for example:
> postgres=# select version();
> version
>
>
> ---------------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.22 on x86_64-pc-linux-gnu (Debian 9.6.22-1.pgdg110+1),
> compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
> (1 row)
>
> Imre
>
>
> Marcin Gozdalik <gozdal@gmail.com> ezt írta (időpont: 2021. máj. 14., P,
> 14:11):
>
>> Unfortunately it's still 9.6. Upgrade to latest 13 is planned for this
>> year.
>>
>> pt., 14 maj 2021 o 12:08 Imre Samu <pella.samu@gmail.com> napisał(a):
>>
>>> > Apart from the above hack of filtering out live tuples to a separate
>>> table is there anything I could do?
>>>
>>> This is the latest PG13.3 version?
>>>
>>> IMHO: If not, maybe worth updating to the latest patch release, as
>>> soon as possible
>>>
>>> https://www.postgresql.org/docs/release/13.3/
>>> Release date: 2021-05-13
>>> *"Disable the vacuum_cleanup_index_scale_factor parameter and storage
>>> option (Peter Geoghegan)*
>>> *The notion of tracking “stale” index statistics proved to interact
>>> badly with the autovacuum_vacuum_insert_threshold parameter, resulting in
>>> unnecessary full-index scans and consequent degradation of autovacuum
>>> performance. The latter mechanism seems superior, so remove the
>>> stale-statistics logic. The control parameter for that,
>>> vacuum_cleanup_index_scale_factor, will be removed entirely in v14. In v13,
>>> it remains present to avoid breaking existing configuration files, but it
>>> no longer does anything."*
>>>
>>> best,
>>> Imre
>>>
>>>
>>> Marcin Gozdalik <gozdal@gmail.com> ezt írta (időpont: 2021. máj. 14.,
>>> P, 13:20):
>>>
>>>> Hi
>>>>
>>>> I am trying to use `pgmetrics` on a big (10TB+), busy (1GB/s RW)
>>>> database. It takes around 5 minutes for pgmetrics to run. I traced the
>>>> problem to the "bloat query" (version of
>>>> https://wiki.postgresql.org/wiki/Show_database_bloat) spinning in CPU,
>>>> doing no I/O.
>>>>
>>>> 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.
>>>> CPU 1.36s/6.40u sec elapsed 9.85 sec.
>>>> INFO: analyzing "pg_catalog.pg_class"
>>>> INFO: "pg_class": scanned 60000 of 158869 pages, containing 295 live
>>>> rows and 2806547 dead rows; 295 rows in sample, 781 estimated total rows
>>>> VACUUM
>>>> ```
>>>>
>>>> `pg_class` has so many dead rows because the workload is temp-table
>>>> heavy (creating/destroying 1M+ temporary tables per day) and has long
>>>> running analytics queries running for 24h+.
>>>>
>>>> PG query planner assumes that index scan on `pg_class` will be very
>>>> quick and plans Nested loop with Index scan. However, the index scan has 7M
>>>> dead tuples to filter out and the query takes more than 200 seconds (
>>>> https://explain.depesz.com/s/bw2G).
>>>>
>>>> If I create a temp table from `pg_class` to contain only the live
>>>> tuples:
>>>> ```
>>>> CREATE TEMPORARY TABLE pg_class_alive AS SELECT oid,* from pg_class;
>>>> CREATE UNIQUE INDEX pg_class_alive_oid_index ON pg_class_alive(oid);
>>>> CREATE UNIQUE INDEX pg_class_alive_relname_nsp_index ON
>>>> pg_class_alive(relname, relnamespace);
>>>> CREATE INDEX pg_class_tblspc_relfilenode_index ON
>>>> pg_class_alive(reltablespace, relfilenode);
>>>> ANALYZE pg_class_alive;
>>>> ```
>>>>
>>>> and run the bloat query on `pg_class_alive` instead of `pg_class`:
>>>> ```
>>>> SELECT
>>>> nn.nspname AS schemaname,
>>>> cc.relname AS tablename,
>>>> COALESCE(cc.reltuples,0) AS reltuples,
>>>> COALESCE(cc.relpages,0) AS relpages,
>>>> COALESCE(CEIL((cc.reltuples*((datahdr+8-
>>>> (CASE WHEN datahdr%8=0 THEN 8 ELSE datahdr%8
>>>> END))+nullhdr2+4))/(8192-20::float)),0) AS otta
>>>> FROM
>>>> pg_class_alive cc
>>>> JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <>
>>>> 'information_schema'
>>>> LEFT JOIN
>>>> (
>>>> SELECT
>>>> foo.nspname,foo.relname,
>>>> (datawidth+32)::numeric AS datahdr,
>>>> (maxfracsum*(nullhdr+8-(case when nullhdr%8=0 THEN 8 ELSE
>>>> nullhdr%8 END))) AS nullhdr2
>>>> FROM (
>>>> SELECT
>>>> ns.nspname, tbl.relname,
>>>> SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS
>>>> datawidth,
>>>> MAX(coalesce(null_frac,0)) AS maxfracsum,
>>>> 23+(
>>>> SELECT 1+count(*)/8
>>>> FROM pg_stats s2
>>>> WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND
>>>> s2.tablename = tbl.relname
>>>> ) AS nullhdr
>>>> FROM pg_attribute att
>>>> JOIN pg_class_alive tbl ON att.attrelid = tbl.oid
>>>> JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
>>>> LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
>>>> AND s.tablename = tbl.relname
>>>> AND s.inherited=false
>>>> AND s.attname=att.attname
>>>> WHERE att.attnum > 0 AND tbl.relkind='r'
>>>> GROUP BY 1,2
>>>> ) AS foo
>>>> ) AS rs
>>>> ON cc.relname = rs.relname AND nn.nspname = rs.nspname
>>>> LEFT JOIN pg_index i ON indrelid = cc.oid
>>>> LEFT JOIN pg_class_alive c2 ON c2.oid = i.indexrelid
>>>> ```
>>>>
>>>> it runs in 10s, 20x faster (https://explain.depesz.com/s/K4SH)
>>>>
>>>> The rabbit hole probably goes deeper (e.g. should do the same for
>>>> pg_statistic and pg_attribute and create a new pg_stats view).
>>>>
>>>> I am not able (at least not quickly) change the amount of temporary
>>>> tables created or make the analytics queries finish quicker. Apart from the
>>>> above hack of filtering out live tuples to a separate table is there
>>>> anything I could do?
>>>>
>>>> Thank you,
>>>> Marcin Gozdalik
>>>>
>>>> --
>>>> Marcin Gozdalik
>>>>
>>>
>>
>> --
>> Marcin Gozdalik
>>
>
--
Marcin Gozdalik
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-05-14 15:47 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-14 11:06 Very slow "bloat query" Marcin Gozdalik <gozdal@gmail.com>
2021-05-14 11:50 ` Gilles Darold <gilles@darold.net>
2021-05-14 12:07 ` Imre Samu <pella.samu@gmail.com>
2021-05-14 12:11 ` Marcin Gozdalik <gozdal@gmail.com>
2021-05-14 15:44 ` Imre Samu <pella.samu@gmail.com>
2021-05-14 15:47 ` Marcin Gozdalik <gozdal@gmail.com>
2021-05-14 15:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-05-14 15:15 ` Marcin Gozdalik <gozdal@gmail.com>
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