root/trunk/tools/pg_bloat_report.sh

Revision 40, 5.4 kB (checked in by robert, 5 years ago)

would be good to have easy locatable logfile too

  • Property svn:executable set to *
Line 
1 #!/bin/bash
2
3 ############################################################################
4 # Module Name   : pg_bloat_report                                          #
5 # Module Type   : Shell script                                             #
6 # Synopsis      : This script will send bloat report.                      #
7 # Copyright     : 2009, OmniTI Inc.                                        #
8 #                                                                          #
9 ############################################################################
10
11 LOGFILE=/home/postgres/logs/bloat_report.rpt
12 SERVER=`hostname`
13
14 if [ $1 = "INDEXES" ]; then
15
16 psql -U postgres -d pagila -c "SELECT
17   schemaname||'.'||iname as relation,
18 -- tablename, ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
19 -- reltuples::bigint, relpages::bigint, otta,
20   ituples::bigint, ipages::bigint, iotta,
21   ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
22   CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
23   CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
24   pg_size_pretty((CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END)::bigint) AS pwastedisize
25 FROM (
26   SELECT
27     schemaname, tablename, cc.reltuples, cc.relpages, bs,
28     CEIL((cc.reltuples*((datahdr+ma-
29       (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
30     COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
31     COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
32   FROM (
33     SELECT
34       ma,bs,schemaname,tablename,
35       (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
36       (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
37     FROM (
38       SELECT
39         schemaname, tablename, hdr, ma, bs,
40         SUM((1-null_frac)*avg_width) AS datawidth,
41         MAX(null_frac) AS maxfracsum,
42         hdr+(
43           SELECT 1+count(*)/8
44           FROM pg_stats s2
45           WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
46         ) AS nullhdr
47       FROM pg_stats s, (
48         SELECT
49           (SELECT current_setting('block_size')::numeric) AS bs,
50           CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
51           CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
52         FROM (SELECT version() AS v) AS foo
53       ) AS constants
54       GROUP BY 1,2,3,4,5
55     ) AS foo
56   ) AS rs
57   JOIN pg_class cc ON cc.relname = rs.tablename
58   JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
59   LEFT JOIN pg_index i ON indrelid = cc.oid
60   LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
61 ) AS sml
62 WHERE (sml.relpages - otta > 128 OR ipages - iotta > 128)
63   AND ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) > 1.2
64   AND CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END > 1024 * 100
65 ORDER BY wastedibytes DESC
66 " > $LOGFILE
67
68 else
69
70 psql -U postgres -d pagila -c "SELECT
71   schemaname||'.'||tablename as relation, reltuples::bigint, relpages::bigint, otta,
72   ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
73   CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
74   CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
75   pg_size_pretty((CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END)::bigint) AS pwastedbytes
76 FROM (
77   SELECT
78     schemaname, tablename, cc.reltuples, cc.relpages, bs,
79     CEIL((cc.reltuples*((datahdr+ma-
80       (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta
81   FROM (
82     SELECT
83       ma,bs,schemaname,tablename,
84       (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
85       (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
86     FROM (
87       SELECT
88         schemaname, tablename, hdr, ma, bs,
89         SUM((1-null_frac)*avg_width) AS datawidth,
90         MAX(null_frac) AS maxfracsum,
91         hdr+(
92           SELECT 1+count(*)/8
93           FROM pg_stats s2
94           WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
95         ) AS nullhdr
96       FROM pg_stats s, (
97         SELECT
98           (SELECT current_setting('block_size')::numeric) AS bs,
99           CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
100           CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
101         FROM (SELECT version() AS v) AS foo
102       ) AS constants
103       GROUP BY 1,2,3,4,5
104     ) AS foo
105   ) AS rs
106   JOIN pg_class cc ON cc.relname = rs.tablename
107   JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
108 ) AS sml
109 WHERE sml.relpages - otta > 128
110       AND ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) > 1.2
111       AND CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END > 1024 * 100
112 ORDER BY wastedbytes DESC
113 " > $LOGFILE
114
115 fi
116
117
118 if [ -s "$LOGFILE" ]; then
119   mailx -s "PG Bloat Report $SERVER $1" dba@example.com < $LOGFILE
120 fi
121 rm $LOGFILE
122 exit
123
124 # TODO
125 # add flags for db connection options (dbname, port, host, etc...)
126 # add flag for email address
127 # add flag for logfile location
Note: See TracBrowser for help on using the browser.