1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | select * from ( SELECT current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/ ROUND(( CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages:: FLOAT /otta END ):: NUMERIC ,1) AS tbloat, CASE WHEN relpages < otta THEN 0 ELSE round(bs*((sml.relpages-otta):: bigint )/1024/1024,1) END AS wastedMBytes, iname, /*ituples::bigint, ipages::bigint, iotta,*/ ROUND(( CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages:: FLOAT /iotta END ):: NUMERIC ,1) AS ibloat, CASE WHEN ipages < iotta THEN 0 ELSE round(bs*((ipages-iotta)/1024/1024):: bigint ,1) END AS wastediMBytes FROM ( SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- ( CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END ))+nullhdr2+4))/(bs-20:: FLOAT )) AS otta, COALESCE (c2.relname, '?' ) AS iname, COALESCE (c2.reltuples,0) AS ituples, COALESCE (c2.relpages,0) AS ipages, COALESCE (CEIL((c2.reltuples*(datahdr-12))/(bs-20:: FLOAT )),0) AS iotta -- very rough approximation, assumes all cols FROM ( SELECT ma,bs,schemaname,tablename, (datawidth+(hdr+ma-( CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END ))):: NUMERIC AS datahdr, (maxfracsum*(nullhdr+ma-( CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END ))) AS nullhdr2 FROM ( SELECT schemaname, tablename, hdr, ma, bs, SUM ((1-null_frac)*avg_width) AS datawidth, MAX (null_frac) AS maxfracsum, hdr+( SELECT 1+ COUNT (*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename ) AS nullhdr FROM pg_stats s, ( SELECT ( SELECT current_setting( 'block_size' ):: NUMERIC ) AS bs, CASE WHEN SUBSTRING (v,12,3) IN ( '8.0' , '8.1' , '8.2' ) THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma FROM ( SELECT version() AS v) AS foo ) AS constants GROUP BY 1,2,3,4,5 ) AS foo ) AS rs JOIN pg_class cc ON cc.relname = rs.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema' LEFT JOIN pg_index i ON indrelid = cc.oid LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid ) AS sml ) bloat where (bloat.tbloat > 5 or ibloat >5) and wastedMBytes > 100 or wastedMBytes > 1024 ORDER BY wastedMBytes DESC ; |
took from https://wiki.postgresql.org/wiki/Show_database_bloat
1 комментарий
CREATE
SCHEMA
monitoring;
alter
schema
monitoring owner
to
postgres;
create
view
monitoring.v_defrag
as
SELECT
current_database(), schemaname, tablename,
/*reltuples::bigint, relpages::bigint, otta,*/
ROUND((
CASE
WHEN
otta=0
THEN
0.0
ELSE
sml.relpages::
FLOAT
/otta
END
)::
NUMERIC
,1)
AS
tbloat,
CASE
WHEN
relpages < otta
THEN
0
ELSE
round(bs*((sml.relpages-otta)::
bigint
)/1024/1024,1)
END
AS
wastedMBytes,
iname,
/*ituples::bigint, ipages::bigint, iotta,*/
ROUND((
CASE
WHEN
iotta=0
OR
ipages=0
THEN
0.0
ELSE
ipages::
FLOAT
/iotta
END
)::
NUMERIC
,1)
AS
ibloat,
CASE
WHEN
ipages < iotta
THEN
0
ELSE
round(bs*((ipages-iotta)/1024/1024)::
bigint
,1)
END
AS
wastediMBytes
FROM
(
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(
CASE
WHEN
datahdr%ma=0
THEN
ma
ELSE
datahdr%ma
END
))+nullhdr2+4))/(bs-20::
FLOAT
))
AS
otta,
COALESCE
(c2.relname,
'?'
)
AS
iname,
COALESCE
(c2.reltuples,0)
AS
ituples,
COALESCE
(c2.relpages,0)
AS
ipages,
COALESCE
(CEIL((c2.reltuples*(datahdr-12))/(bs-20::
FLOAT
)),0)
AS
iotta
-- very rough approximation, assumes all cols
FROM
(
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(
CASE
WHEN
hdr%ma=0
THEN
ma
ELSE
hdr%ma
END
)))::
NUMERIC
AS
datahdr,
(maxfracsum*(nullhdr+ma-(
CASE
WHEN
nullhdr%ma=0
THEN
ma
ELSE
nullhdr%ma
END
)))
AS
nullhdr2
FROM
(
SELECT
schemaname, tablename, hdr, ma, bs,
SUM
((1-null_frac)*avg_width)
AS
datawidth,
MAX
(null_frac)
AS
maxfracsum,
hdr+(
SELECT
1+
COUNT
(*)/8
FROM
pg_stats s2
WHERE
null_frac<>0
AND
s2.schemaname = s.schemaname
AND
s2.tablename = s.tablename
)
AS
nullhdr
FROM
pg_stats s, (
SELECT
(
SELECT
current_setting(
'block_size'
)::
NUMERIC
)
AS
bs,
CASE
WHEN
SUBSTRING
(v,12,3)
IN
(
'8.0'
,
'8.1'
,
'8.2'
)
THEN
27
ELSE
23
END
AS
hdr,
CASE
WHEN
v ~
'mingw32'
THEN
8
ELSE
4
END
AS
ma
FROM
(
SELECT
version()
AS
v)
AS
foo
)
AS
constants
GROUP
BY
1,2,3,4,5
)
AS
foo
)
AS
rs
JOIN
pg_class cc
ON
cc.relname = rs.tablename
JOIN
pg_namespace nn
ON
cc.relnamespace = nn.oid
AND
nn.nspname = rs.schemaname
AND
nn.nspname <>
'information_schema'
LEFT
JOIN
pg_index i
ON
indrelid = cc.oid
LEFT
JOIN
pg_class c2
ON
c2.oid = i.indexrelid
)
AS
sml;
ALTER
view
monitoring.v_defrag OWNER
TO
postgres;
create
function
monitoring.f_defrag ()
RETURNS
SETOF monitoring.v_defrag
AS
$BODY$
SELECT
*
FROM
monitoring.v_defrag;
$BODY$
LANGUAGE sql VOLATILE
SECURITY DEFINER
COST 100
ROWS
1000;
ALTER
FUNCTION
monitoring.f_defrag() OWNER
TO
postgres;
grant
usage
on
schema
monitoring
to
monitoring_role;
GRANT
EXECUTE
ON
FUNCTION
monitoring.f_defrag()
TO
GROUP
monitoring_role;
check example
psql -U monitoring_user -d some_db -h localhost -c
" select * from monitoring.f_defrag() where ((tbloat>5 or ibloat >5) and wastedmbytes>100 or wastedimbytes >100) or wastedmbytes > 1024;"
current_database | schemaname | tablename | tbloat | wastedmbytes | iname | ibloat | wastedimbytes
------------------+------------+----------------+--------+--------------+------------------------------+--------+---------------
some_db | pg_catalog | pg_largeobject | 1.6 | 2156.6 | pg_largeobject_loid_pn_index | 0.0 | 0