Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Size of mysql database/tables with perl

by TedYoung (Deacon)
on Feb 03, 2006 at 19:49 UTC ( [id://527797]=CUFP: print w/replies, xml ) Need Help??

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; }

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}'
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; }
    <-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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: CUFP [id://527797]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (4)
As of 2024-03-19 04:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found