Source: http://imgs.xkcd.com/store/imgs/actual_size_200.png

Someone recently asked a question on the MoodleMayhem.org email list:

I need to find out what the current database size is of my Moodle. Can someone tell me where that is located and how I can find it? Thanks.

Wow, what a great question! The answer, of course, wasn’t as hard as I would have imagined:

Here’s how:
From the link above (I’m stealing it for my notes here!):

MySQL Database size





MySQL Database size
<!--?php

function file_size_info($filesize) {

 $bytes = array('KB', 'KB', 'MB', 'GB', 'TB'); # values are always displayed

 if ($filesize < 1024) $filesize = 1; # in at least kilobytes.

 for ($i = 0; $filesize > 1024; $i++) $filesize /= 1024;

 $file_size_info['size'] = ceil($filesize);

 $file_size_info['type'] = $bytes[$i];

 return $file_size_info;

}

$db_server = 'put your server here';

$db_user = 'put your mysql user here';

$db_pwd = 'put your password here';

$db_name = 'put your db name here';

$db_link = @mysql_connect($db_server, $db_user, $db_pwd)

 or exit('Could not connect: ' . mysql_error());

$db = @mysql_select_db($db_name, $db_link)

 or exit('Could not select database: ' . mysql_error());

// Calculate DB size by adding table size + index size: 

$rows = mysql_query("SHOW TABLE STATUS");

$dbsize = 0;

while ($row = mysql_fetch_array($rows)) {

 $dbsize += $row['Data_length'] + $row['Index_length'];

}

print "Database size is: $dbsize bytes

";

print 'or

';

$dbsize = file_size_info($dbsize);

print "Database size is: {$dbsize['size']} {$dbsize['type']}";

?>

I tried it on an existing database and came up with the following result for our MSTATEKS2011 MySQL database:

MySQL Database size

Database size is: 1298885483 bytes
or
Database size is: 2 GB
That sure beats downloading the SQLdump file you can make using a tool like PHPMyAdmin, SQLYog or NavicatLite.
You will have to know what values go here:

$db_server = 'put your server here';

$db_user = 'put your mysql user here';

$db_pwd = 'put your password here';

$db_name = 'put your db name here';
Those might look like this generally:

$db_server = 'localhost';

$db_user = 'superuser';

$db_pwd = 'superpwd';

$db_name = 'moodledbasename';

What a nifty trick! To do this before, I was simply doing a SQLDump using a MySQL dbase tool…this was MUCH faster. What would it take to display multiple database sizes in a list?

Get Blog Updates via Email!

Enter your email address:

Delivered by FeedBurner

PingIt!
pingthis();
Everything posted on Miguel Guhlin’s blogs/wikis are his personal opinion and do not necessarily represent the views of his employer(s) or its clients. Read Full Disclosure
Advertisements