Thursday, August 25, 2011

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

'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!

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

No comments:

Genuine Leadership #4: Gratitude