Changeset 15

Show
Ignore:
Timestamp:
06/25/08 22:50:16 (6 years ago)
Author:
robert
Message:

do some actual debugging, make this stuff actually work

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • trunk/table_growth/otools.collect_table_growth.sql

    r14 r15  
    11create or replace function otools.collect_table_growth()  
    2 returns setof otools.table_growth   
     2returns void    
    33as $$ 
    4     insert into otools.table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time 
    5     select pg_get_userbyid(c.relowner) AS table_owner, n.nspname AS schema_name, c.relname AS table_name, pg_relation_size(oid), 0, 0, current_date  
    6     from pg_class where relkind = 'r' and reltuples > 25000; 
     4    insert into otools.table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time)  
     5    select pg_get_userbyid(c.relowner) AS table_owner, n.nspname AS schema_name, c.relname AS table_name, pg_relation_size(c.oid), 0, 0, current_date  
     6    from pg_class c JOIN pg_namespace n ON (c.relnamespace=n.oid) where relkind = 'r' and reltuples > 25000; 
    77$$ language sql; 
  • trunk/table_growth/otools.summarize_table_growth.sql

    r14 r15  
    1 create or replace function otools.summarize_table_growth  
     1create or replace function otools.summarize_table_growth()  
    22returns void  
    33as $$ 
     
    88-- Daily summarization 
    99IF to_char(current_date,'dd') <> '01' THEN 
    10     insert into table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time) 
    11     select a.table_owner, a.schema_name, a.table_name, a.actual_size, CASE WHEN b.actual_size IS NULL then 0 else a.actual_size-b.actual_size END AS table_growth, 1, a.timestamp 
    12     from table_growth a left join table_growth b on (a.table_owner=b.table_owner and a.table_name=b.table_name and a.schema_name=b.schema_name)  
    13     where a.sum_flag=0 and a.capture_time = current_date and b.capture_time = current_date - 1; 
     10    insert into otools.table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time) 
     11    select a.table_owner, a.schema_name, a.table_name, a.actual_size, CASE WHEN b.actual_size IS NULL then 0 else a.actual_size-b.actual_size END AS table_growth, 1, a.capture_time 
     12    from otools.table_growth a  
     13        left join otools.table_growth b  
     14            on (a.table_owner=b.table_owner and a.table_name=b.table_name and a.schema_name=b.schema_name and b.capture_time = current_date -1)  
     15    where  
     16        a.sum_flag=0 and a.capture_time = current_date; 
    1417    -- now remove older rows 
    1518    delete from otools.table_growth where sum_flag = 0; 
     
    1821-- Monthly summarization 
    1922IF to_char(current_date,'dd') = '01' THEN 
    20     insert into table_growth (table_owner, schema_name, table_name, actual_size, growth_size, sum_flag, capture_time) 
    21     select a.table_owner, a.schema_name, a.table_name, a.actual_size, sum(growth_size), 2, (current_date - '1 month'::interval)  
    22     from otools.table_growth  
    23     where sum_flags=1 and capture_time between (current_date - '1 month'::interval) and current_date  
     23    insert into otools.table_growth (table_owner, schema_name, table_name, growth_size, sum_flag, capture_time) 
     24    select a.table_owner, a.schema_name, a.table_name, sum(growth_size), 2, (current_date - '1 month'::interval)  
     25    from otools.table_growth a  
     26    where sum_flag=1 and capture_time between (current_date - '1 month'::interval) and current_date  
    2427    group by table_owner, schema_name, table_name; 
    2528    -- now remove older rows 
  • trunk/table_growth/otools.table_growth.sql

    r14 r15  
    66    growth_size numeric NOT NULL, 
    77    sum_flag smallint NOT NULL, 
    8     capture_time timestamp without time zone NOT NULL 
     8    capture_time date NOT NULL 
    99); 
    1010 
  • trunk/table_growth/table_growth_report.sh

    r14 r15  
    1 select 'Top 10 Tables Growth For:-  '||to_char(current_date - '1 month'::interval,'Mon-YYYY') as Month ; 
     1#!/bin/bash 
    22 
    3 select  
    4     table_owner, schema_name, table_name, growth_size as Growth_size_MB  
    5 from  
    6     otools.table_growth  
    7 where  
    8     sum_flag = 2  
    9     and  
    10     to_char(capture_time,'mm')=to_char((current_date - '1 month'::interval),'mm')  
    11 order by  
    12     growth_size desc  
    13 limit  
    14     10; 
     3############################################################################ 
     4# Module Name   : table_growth                                             #  
     5# Module Type   : Shell script                                             # 
     6# Synopsis      : This script will send table growth monitor report.       # 
     7# Copyright     : 2008, OmniTI Inc.                                        # 
     8#                                                                          #  
     9############################################################################ 
     10 
     11LOGFILE=/home/postgres/logs/table_growth.rpt 
     12psql -d pagila -c "select 'Top 10 Tables Growth For:-  '||to_char(current_date - '1 month'::interval,'Mon-YYYY') as Month; select table_owner, schema_name, table_name, pg_size_pretty(growth_size::bigint) as Growth_size_MB from otools.table_growth where sum_flag = 2 and to_char(capture_time,'mm')=to_char((current_date - '1 month'::interval),'mm') order by growth_size desc limit 10;" > $LOGFILE 
     13 
     14if [ -s "$LOGFILE" ]; then 
     15  mailx -s "Tablegrowth Monitor Report" dba@example.com < $LOGFILE 
     16fi 
     17rm $LOGFILE 
     18exit 
     19