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;
}
Re: Size of mysql database/tables with perl
created: 2006-02-04 11:51:50
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}'
Re: Size of mysql database/tables with perl
created: 2006-02-07 12:21:47
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 {RaiseError=>1}
foreach (@$result) { $size += $_->[6] + $_->[8] }
return $size;
}
<-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 [http://en.wikipedia.org/wiki/Trebuchet|trebuchet]
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 [http://en.wikipedia.org/wiki/Trebuchet|trebuchet]