PostgreSQL and bloat

標籤：

The bucardo project has released its nagios plugins for PostgreSQL and we can extract from them this nice view in order to check for table and index bloatinto our PostgreSQL databases:

下面是檢查資料表空間的使用方式的指令碼：

CREATE OR REPLACE VIEW bloat AS      SELECT        schemaname, tablename, reltuples::bigint, relpages::bigint, otta,        ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,        relpages::bigint - otta AS wastedpages,        bs*(sml.relpages-otta)::bigint AS wastedbytes,        pg_size_pretty((bs*(relpages-otta))::bigint) AS wastedsize,        iname, ituples::bigint, ipages::bigint, iotta,        ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,        CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,        CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,        CASE WHEN ipages < iotta THEN pg_size_pretty(0::bigint) ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize      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        LEFT JOIN pg_index i ON indrelid = cc.oid        LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid      ) AS sml      WHERE sml.relpages - otta > 0 OR ipages - iotta > 10      ORDER BY wastedbytes DESC, wastedibytes DESC;

 

更詳細可以參見check_postgres指令碼：

http://bucardo.org/wiki/Check_postgres

參考：

http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html

PostgreSQL and bloat