homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

MySQL Database Size
Code snippet; looking for improvements

 4:10 am on Nov 14, 2003 (gmt 0)

I have often noticed folks asking how to determine their MySQL database size.

As ergophobe once said [webmasterworld.com], "size matters too, but we won't go there" ;)

My understanding of database size has been to add the table(s) size plus the index(es) size. I've tested on my own servers and found the results to confirm my understanding, at least in regards to the MySQL database.

I've created a simple code snippet that will calculate and report.

  1. Using a text editor, create a new file and paste the following snippet into your file. You need to replace the italicized/bold parameters with your own information.
  2. FTP the file to the server (assuming the server has PHP installed).
  3. Open a web browser, request this script and it should return the desired results


<html><head><title>MySQL Database Size</title></head><body>
<h1>MySQL Database Size</h1>
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 = 'localhost';
$db_user = 'userid';
$db_pwd = 'password';
$db_name = 'databasename';
$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<br />";
print 'or<br />';
$dbSize = file_size_info($dbSize);
print "Database size is: {$dbSize['size']} {$dbSize['type']}";

I'd like to find out from the community how accurate this snippet is. Together we should be able to develop an extremely accurate MySQL-database-sizing-tool. If you have time to test and offer input, suggestions, improvements, please, by all means, do so.

Looking forward to your input -- coopster

[edited by: jatar_k at 7:28 pm (utc) on Nov. 14, 2003]
[edit reason] added coopster's change from post 4 [/edit]



 4:32 pm on Nov 14, 2003 (gmt 0)

Very cool coopster,

I just did a quick test on a small database and it looks spot on.

Can't figure out why I get this though...
Notice: Undefined variable: dbSize in c:\inetpub\wwwroot\sqlsize.php on line 26

which is this line
$dbSize += $row['Data_length'] + $row['Index_length'];


 4:41 pm on Nov 14, 2003 (gmt 0)



How about aggregating all db's on a surver. Get the requests in early. :)


 6:13 pm on Nov 14, 2003 (gmt 0)

Can't figure out why I get this though...
Notice: Undefined variable: dbSize in c:\inetpub\wwwroot\sqlsize.php on line 26

Timotheos --
Assignment copies the original variable to the new one (assignment by value) so I'm guessing that since $dbSize is not set yet in the first invocation of the
while loop PHP is issuing a notice/warning message.

Confirmation. From the Manual [us4.php.net]:
// Reporting E_NOTICE can be good too (to report uninitialized
// variables or catch variable name misspellings ...)

Simply add:

$dbSize = 0;

above the
while loop and it should get eliminate the message. I'll ask the moderator to insert that line into the original post.


 6:43 pm on Nov 14, 2003 (gmt 0)

Thanks coopster. That fixed it.


 12:18 pm on Nov 15, 2003 (gmt 0)

The results match the actual filesize of the data and the index files (the .MYD and .MYI files). Would there be a way to add the sizes of the .frm files? Each myisam table has a .frm file of about 9K.

Or would that require the use of filesystem commands that are bound by privileges?


 1:09 pm on Nov 15, 2003 (gmt 0)


I've just tested this on two of my databases and both are reporting under the total file sizes of the databases (using du). I've tested to see if the results match the data and index files only (as RonPK pointed out) but they are not the same size.

I'm certainly no expert but maybe this is down to the different way du and mysql calculate file sizes? I see that SHOW TABLE STATUS was added in 3.23, I'm running 3.23.52, maybe I'll get more accurate results if I upgrade to a later release?

Don't know if that's of any use, just my 2 cents.


 11:02 pm on Nov 15, 2003 (gmt 0)

mogwai --

>>maybe I'll get more accurate results if I upgrade to a later release?

I doubt it. I checked the MySQL Change History [mysql.com] and to the best of my knowledge there was no issue with SHOW TABLE STATUS that would cause this type of inaccuracy. There were a few glitches repaired over the next couple releases, but nothing that seems to justify the inaccuracies you describe.

mogwai, using du...what is du?


 12:37 am on Nov 16, 2003 (gmt 0)


du = file space usage command (using on a Suse Linux 8.1 box)

I'm just using it with the -cb options to output the file space usage in bytes + the total.



 1:31 pm on Nov 17, 2003 (gmt 0)

OK, I thought maybe you were referring to a canned program or script package. Well, you're right about possible discrepancies in the way your operating system calculates file sizes with any disk usage commands, so be sure to check your operating system manual (man pages) for details and see the support pages for any issues with those commands.

That said, I thought maybe we would add file size detail via this script, replace the while loop with this code (all I did was add print commands to the loop):

while ($row = mysql_fetch_array($rows)) {
$dbSize += $row['Data_length'] + $row['Index_length'];
print '<pre>Table: <strong>' . $row['Name'] . '</strong><br />';
print 'Database size. . .: ' . $row['Data_length'] . '<br />';
print 'Index Size . . . .: ' . $row['Index_length'] . '<br />';
print 'Total size . . . .: ' . ($row['Data_length'] + $row['Index_length']) . '<br /></pre>';

[edited by: jatar_k at 4:10 pm (utc) on Dec. 2, 2003]
[edit reason] updated by coopster's request [/edit]

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved