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/
