mguhlin

MoodleTip – How to Discover the Size of Your Moodle Database

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

Discover more from Another Think Coming

Subscribe to get the latest posts sent to your email.

Leave a comment