This article is half-done without your Comment! *** Please share your thoughts via Comment ***
I recently started work with Greenplum Distributed RDBMS. Internally, it is using PostgreSQL 8.2 which is an ancient version of PostgreSQL.
I shared a couple of articles on finding the fragmentation in PostgreSQL, but none of my scripts worked for Greenplum PostgreSQL 8.2.
I have Posted one question on StackOverflow. Please check this link.
Finally, I found a script for finding the fragmentation of Greenplum tables which is very important for Greenplum DBA because Greenplum is a based on MVCC architecture which generates dead rows at every UPDATE / DELETE.
You can use below script for finding the wasted space of the tables and can execute VACUUM ANALYZE on those tables.
|
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 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
select current_database() as dbname , relid , schemaname , tablename , round(bloat,1) as bloat_ratio , pg_size_pretty(expbytes) as expected_size , pg_size_pretty(relbytes) as relation_size , pg_size_pretty(wastedbytes) as wasted_space , round(1.0 * relbytes / nullif((sum(relbytes) over ())::bigint,0),4) as relative_size , round((bloat-1) * (1.0 * relbytes / nullif((sum(relbytes) over ())::bigint, 0)), 8) as concern from ( SELECT relid , schemaname , tablename , CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END AS bloat , reltuples::bigint , relpages::bigint , otta , (bs*otta)::bigint as expbytes , CASE WHEN relpages < otta THEN 0 ELSE (bs*(sml.relpages-otta))::bigint END AS wastedbytes , CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages , (bs*relpages)::bigint as relbytes FROM ( SELECT schemaname , tablename , cc.oid as relid , 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 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-s.null_frac)*avg_width) AS datawidth , MAX(s.null_frac) AS maxfracsum , hdr+(1+(count(case when s.null_frac<>0 then 1 end))/8) as nullhdr FROM pg_stats s cross join ( SELECT current_setting('block_size')::numeric AS bs , CASE WHEN substring(version(),12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr , CASE WHEN version() ~ 'mingw32' THEN 8 ELSE 4 END AS ma ) AS constants GROUP BY schemaname, tablename, hdr, ma, bs ) AS foo ) AS rs JOIN pg_class cc ON cc.relname = rs.tablename and cc.relkind = 'r' JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname ) AS sml ) wrapper where wastedbytes > 2*1024*1024 and bloat >= 1.4order by (bloat-1) * (1.0 * relbytes / nullif((sum(relbytes) over ())::bigint, 0)) desc; |
Taken reference from this article
