postgresql bloat monitoring script

1 комментарий к “postgresql bloat monitoring script”

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
   

   Ответить

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.