Changeset 45

Show
Ignore:
Timestamp:
08/13/09 17:52:26 (5 years ago)
Author:
depesz
Message:

added options to format the report in more readable way (less information)

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • trunk/tools/pg_bloat_report.pl

    r44 r45  
    3232} 
    3333 
     34sub reformat_report { 
     35    my $txt = shift; 
     36    my @rows = split /\r?\n/, $txt; 
     37    return ( 0, '(0 rows)' ) if 0 == scalar @rows; 
     38 
     39    my $template; 
     40    if ( $O->{ 'format' } eq 'simple' ) { 
     41        $template = "__[3u]i__. __[-60s]relation__ __[7u]wastedpages__ of __[7u]relpages__ pages wasted ( __pwastedbytes__ )\n"; 
     42    } 
     43    else { 
     44        $template = "-- __[3u]i__. __[-60s]relation__ __[7u]wastedpages__ of __[7u]relpages__ pages wasted ( __pwastedbytes__ )\n"; 
     45        if ( $O->{ 'mode' } eq 'tables' ) { 
     46            $template .= "CLUSTER __relation__; -- You might need to add: USING <some_index_name>\n\n"; 
     47        } 
     48        else { 
     49            $template .= "REINDEX INDEX __relation__;\n\n"; 
     50        } 
     51    } 
     52 
     53    my $report = ''; 
     54    my $i      = 0; 
     55    for my $row ( @rows ) { 
     56        $i++; 
     57        my @columns = split /\|/, $row; 
     58        my %data; 
     59        @data{ qw( relation reltuples relpages otta bloat wastedpages wastedbytes pwastedbytes ) } = @columns; 
     60        $data{ "i" } = $i; 
     61        my $string = $template; 
     62        $string =~ s#__(?:\[(.*?)\])?([a-z]+)__#sprintf '%'.($1||'s'), $data{$2}||''#ge; 
     63        $report .= $string; 
     64    } 
     65    return ( scalar @rows, $report ); 
     66} 
     67 
    3468sub send_report_by_mail { 
    3569    return unless $O->{ 'recipients' }; 
     
    3771 
    3872    my $report = slurp_file( 'report.stdout' ); 
    39     if (   ( $report =~ m{^\(0\s+rows\)\s*\z}m ) 
     73    my ( $row_count, $reformatted ) = reformat_report( $report ); 
     74    if (   ( 0 == $row_count ) 
    4075        && ( !$O->{ 'send-zero' } ) ) 
    4176    { 
     
    6499        print $mailx "STDERR:\n" . slurp_file( 'report.stderr' ) . "\n"; 
    65100    } 
    66     if ( -s 'report.stdout' ) { 
    67         print $mailx "Report:\n" . $report . "\n"; 
    68     } 
    69     else { 
    70         print $mailx "There is no report!\n"; 
    71     } 
     101    print $mailx "Report:\n" . $reformatted . "\n"; 
    72102    close $mailx; 
    73103 
     
    104134    push @command, '-U', $O->{ 'user' } if $O->{ 'user' }; 
    105135    push @command, '-f', 'report.sql'; 
     136    push @command, '-qAt' unless $O->{ 'format' } eq 'table'; 
    106137 
    107138    my $command_string = join ' ', map { quotemeta $_ } @command; 
     
    159190    $sql =~ s/__EXTRA__WHERE__/ WHERE $where_string /g; 
    160191 
    161     return $sql if ( !$O->{ 'min-pages' } ) && ( !$O->{ 'min-wasted-pages' } ); 
    162  
    163192    my @pages_where = (); 
    164193 
    165     if ( $O->{ 'min-pages' } ) { 
    166         if ( $O->{ 'mode' } eq 'tables' ) { 
    167             push @pages_where, 'relpages >= ' . $O->{ 'min-pages' }; 
    168         } 
    169         else { 
    170             push @pages_where, 'ipages >= ' . $O->{ 'min-pages' }; 
    171         } 
    172     } 
    173     if ( $O->{ 'min-wasted-pages' } ) { 
    174         if ( $O->{ 'mode' } eq 'tables' ) { 
    175             push @pages_where, 'wastedpages >= ' . $O->{ 'min-wasted-pages' }; 
    176         } 
    177         else { 
    178             push @pages_where, 'wastedipages >= ' . $O->{ 'min-wasted-pages' }; 
    179         } 
    180     } 
     194    push @pages_where, 'relpages >= ' . $O->{ 'min-pages' }           if $O->{ 'min-pages' }; 
     195    push @pages_where, 'wastedpages >= ' . $O->{ 'min-wasted-pages' } if $O->{ 'min-wasted-pages' }; 
     196 
     197    return $sql if 0 == scalar @pages_where; 
    181198 
    182199    $sql = "SELECT * FROM ( $sql ) as subquery WHERE " . join( ' AND ', @pages_where ); 
     
    196213SELECT 
    197214  schemaname||'.'||tablename as relation, reltuples::bigint, relpages::bigint, otta, 
    198   ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat, 
     215  ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS bloat, 
    199216  CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages, 
    200217  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes, 
     
    243260        return q{ 
    244261SELECT 
    245   schemaname||'.'||iname as relation,ituples::bigint, ipages::bigint, iotta, 
    246   ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat, 
    247   CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages, 
    248   CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes, 
    249   pg_size_pretty((CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END)::bigint) AS pwastedisize 
     262  schemaname||'.'||iname as relation,ituples::bigint as reltuples, ipages::bigint as relpages, iotta as otta, 
     263  ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat, 
     264  CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedpages, 
     265  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedbytes, 
     266  pg_size_pretty((CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END)::bigint) AS pwastedbytes 
    250267FROM ( 
    251268  SELECT 
     
    289306  AND ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) > 1.2  
    290307  AND CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END > 1024 * 100  
    291 ORDER BY wastedibytes DESC 
     308ORDER BY wastedbytes DESC 
    292309        }; 
    293310    } 
     
    297314    my %o = ( 
    298315        'exclude-schema' => '^(pg_.*|information_schema)$', 
     316        'format'         => 'table', 
    299317        'logfile'        => '-', 
    300318        'mailx'          => 'mailx', 
     
    334352 
    335353        # other 
     354        'format|f=s', 
    336355        'help|?', 
    337356    ); 
     
    386405    show_help_and_die( 'mailx program name missing!' ) if ( !$O->{ 'mailx' } ) && ( $O->{ 'recipients' } ); 
    387406 
     407    show_help_and_dir( 'Bad format (%s) requsted!', $O->{ 'format' } ) unless $O->{ 'format' } =~ m{\A(?:table|simple|sql)\z}; 
     408 
    388409    chdir $O->{ 'workdir' }; 
    389410    return; 
     
    428449 
    429450  [ other ] 
     451   --format                (-f) : format of report (table, simple or sql) 
    430452   --help                  (-?) : show this help page 
    431453 
    432454Defaults: 
    433455   --exclude-schema   '^(pg_.*|information_schema)\$' 
     456   --format           table 
    434457   --logfile          - 
    435458   --mailx            mailx