no way to compare when less than two revisions

差異處

這裏顯示兩個版本的差異處。


sybase:db_space [2011/05/11 01:57] (目前版本) – 建立 jal
行 1: 行 1:
 +====== Sybase 資料庫空間 ======
 +在 Sybase 如果要看一個資料庫的整體使用空間,其實真的還蠻麻煩的。
 +後來找到一個 perl 檔可以快速的去計算目前使用量,作為是否要增加 Device 的依據。
  
 +該 Perl 檔的來源為:http://www.perlmonks.org/?node_id=130299
 +
 +===== 相依套件 =====
 +  * 在 FreeBSD 使用此套件除了 Perl 外還需要安裝下列二個套件
 +    - p5-DBI
 +    - p5-DBD-Sybase
 +
 +===== 使用方式 =====
 +<code cmd>
 +check-space.pl -U <user> -P <pwd> -S <server> -D <database>
 +</code>
 +
 +===== 程式碼 =====
 +備份一下,以避免它哪天消失了...
 +
 +<file perl check-space.pl>
 +#!/usr/bin/perl -w
 +
 +use strict;
 +use DBI;
 +
 +use Getopt::Long;
 +
 +my %args;
 +
 +GetOptions(\%args, '-U=s', '-P=s', '-S=s', '-D=s');
 +
 +my $dbh = DBI->connect("dbi:Sybase:server=$args{S};database=$args{D}", $args{U}, $args{P});
 +
 +$dbh->{syb_do_proc_status} = 1;
 +
 +my $dbinfo;
 +
 +# First check space in the DB:
 +my $sth = $dbh->prepare("sp_spaceused");
 +$sth->execute;
 +do {
 +    while(my $d = $sth->fetch) {
 +        if($d->[0] =~ /$args{D}/) {
 +            $d->[1] =~ s/[^\d.]//g;
 +            $dbinfo->{size} = $d->[1];
 +        } else {
 +            foreach (@$d) {
 +                s/\D//g;
 +            }
 +            $dbinfo->{reserved} = $d->[0] / 1024;
 +            $dbinfo->{data} = $d->[1] / 1024;
 +            $dbinfo->{index} = $d->[2] / 1024;
 +        }
 +    }
 +} while($sth->{syb_more_results});
 +
 +# Get the actual device usage from sp_helpdb to get the free log space
 +$sth = $dbh->prepare("sp_helpdb $args{D}");
 +$sth->execute;
 +do {
 +    while(my $d = $sth->fetch) {
 +        if($d->[2] && $d->[2] =~ /log only/) {
 +            $d->[1] =~ s/[^\d\.]//g;
 +            $dbinfo->{log} += $d->[1];
 +            my ($logfree) = $d->[3] =~ /(\d+)/;
 +            $dbinfo->{logfree} += $logfree / 1024;
 +        }
 +        if($d->[0] =~ /log only .* (\d+)/) {
 +            $dbinfo->{logfree} = $1 / 1024;
 +        }
 +    }
 +} while($sth->{syb_more_results});
 +
 +$dbinfo->{size} -= $dbinfo->{log};
 +
 +my $freepct = ($dbinfo->{size} - $dbinfo->{reserved}) / $dbinfo->{size};
 +
 +print "$args{S}/$args{D} spaceusage report\n\n";
 +printf "Database size: %10.2f MB\n", $dbinfo->{size};
 +printf "Log size:      %10.2f MB\n", $dbinfo->{log};
 +printf "Free Log:      %10.2f MB\n", $dbinfo->{logfree};
 +printf "Reserved:      %10.2f MB\n", $dbinfo->{reserved};
 +printf "Data:          %10.2f MB\n", $dbinfo->{data};
 +printf "Indexes:       %10.2f MB\n", $dbinfo->{index};
 +printf "Free space:    %10.2f %%\n", $freepct * 100;
 +
 +if($freepct < .25) {
 +    printf "**WARNING**: Free space is below 25%% (%.2f%%)\n\n", $freepct * 100;
 +}
 +
 +print "\nTable information (in MB):\n\n";
 +printf "%30s %15s %10s %10s %10s\n\n", "Table", "Rows", "Reserved", "Data", "Indexes";
 +
 +my @tables = getTables($dbh);
 +
 +foreach (@tables) {
 +    my $sth = $dbh->prepare("sp_spaceused $_");
 +    $sth->execute;
 +    do {
 +        while(my $d = $sth->fetch) {
 +            foreach (@$d) {
 +                s/KB//;
 +                s/\s//g;
 +            }
 +            printf("%30.30s %15d %10.2f %10.2f %10.2f\n",
 +                   $d->[0], $d->[1], $d->[2] / 1024, $d->[3] / 1024,
 +                   $d->[4] / 1024);
 +        }
 +    } while($sth->{syb_more_results});
 +}
 +
 +
 +sub getTables {
 +    my $dbh = shift;
 +
 +    my $sth = $dbh->table_info;
 +    my @tables;
 +    do {
 +        while(my $d = $sth->fetch) {
 +            push(@tables, $d->[2]) unless $d->[3] =~ /SYSTEM|VIEW/;
 +        }
 +    } while($sth->{syb_more_results});
 +
 +    @tables;
 +}
 +</file>
sybase/db_space.txt · 上一次變更: 2011/05/11 01:57 由 jal
上一頁 | 下一頁 | 回首頁 | RSS Feed | Facebook