Size of mysql database/tables with perl

I need to track how much space is being used by each of our database (records and indexes) for disk quota reasons. Googling for it will give you many code examples in PHP. Here is a Perl equiv. It isn't super fast. Let me know if there is a better way.

$DB = DB->connect(...);
my $size = sizeOfDB($DB);

sub sizeOfDB {
   my $db = shift;
   my $size = 0;
   my $st = $db->prepare('show table status');
   $st->execute;
   while (my $h = $st->fetchrow_hashref) {
      # other fields in %$h could be used, for example,
      # to limit the count to just certain tables.
      $size += $h->{Data_length} + $h->{Index_length};
   }
   $st->finish;
   return $size;
}
[download]

Comment on Size of mysql database/tables with perl Download Code

Replies are listed 'Best First'.
Re: Size of mysql database/tables with perl by rnahi (Curate) on Feb 04, 2006 at 16:51 UTC
I am often in need of this info, and I usually resort to the one-liner way. Here is my personal recipe: `$ mysql dbname -e 'show table status' \| \ perl -lane '$size += $F[6]+$F[8];END{print $size}'` [download]	[reply] [d/l]
Re: Size of mysql database/tables with perl by radiantmatrix (Parson) on Feb 07, 2006 at 17:21 UTC
The DBI `_hashref` methods are the slowest way to fetch. Here's a shorter and faster version for you. (The columns you named are returned 6th and 8th {0-indexed}, respectively) `sub sizeOfDB { my $db = shift; my $size = 0; # No placeholders needed, so let selectall_ do the annoying stuff my $result = $db->selectall_arrayref('show table status'); # Assume that the call succeeded, either check error or use {RaiseE +rror=>1} foreach (@$result) { $size += $_->[6] + $_->[8] } return $size; }` [download] <-radiant.matrix-> A collection of thoughts and links from the minds of geeks The Code that can be seen is not the true Code I haven't found a problem yet that can't be solved by a well-placed trebuchet	[reply] [d/l] [select]