homepage Welcome to WebmasterWorld Guest from 54.167.41.199
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
coopster




msg:1288440
 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.


instructions
  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



snippet

<html><head><title>MySQL Database Size</title></head><body>
<h1>MySQL Database Size</h1>
<?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 = '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']}";
?>
</body></html>

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]

 

Timotheos




msg:1288441
 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'];

ukgimp




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

Nice

Cheers

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

coopster




msg:1288443
 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.

<edit>
Confirmation. From the Manual [us4.php.net]:
// Reporting E_NOTICE can be good too (to report uninitialized
// variables or catch variable name misspellings ...)
error_reporting (E_ERROR E_WARNING E_PARSE E_NOTICE);
<edit>

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.

Timotheos




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

Thanks coopster. That fixed it.

RonPK




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

Cool.
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?

mogwai




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

Hi,

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.

coopster




msg:1288447
 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?

mogwai




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

coopster,

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.

Cheers

coopster




msg:1288449
 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