Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm told that show table status is useful in finding the size of a database, but I get an unreadable mess. Is there any way to alter this command to get less information, or is there another way to find the size of a database or table?

share|improve this question

4 Answers 4

up vote 3 down vote accepted

The two fields you want are.

  • Data_length

    This is the size of the data within the table in bytes.

  • Index_length

    This is the size of the table's index in bytes.

You can limit the output to one table with a database with.

SHOW TABLE STATUS LIKE 'tablename';

You can't remove the other columns from the output. You might find the output easier to read from a console by printing the output rows vertically. Just replace the ";" for "\G".

share|improve this answer
    
Thanks much! I guess \G is all I needed, really. –  Eric Wilson Sep 7 '09 at 15:09

I have a rather wild set of queries that would address this nicely. I wrote these about 2 years ago and they work just great. I still use them for reporting to clients.

Query to Give You Database Size Grouped By Storage Engine in MB

SELECT IFNULL(B.engine,'Total') "Storage Engine",         
  CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR('KMGTP',pw+1,1),'B') "Data Size",
  CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
  CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" 
FROM (
  SELECT engine,SUM(data_length) DSize,
    SUM(index_length) ISize,
    SUM(data_length+index_length) TSize 
  FROM information_schema.tables 
  WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
    AND engine IS NOT NULL 
  GROUP BY engine WITH ROLLUP) B,
(SELECT 2 pw) A 
ORDER BY TSize;

Query to Give You Database Size Grouped By Database in MB

SELECT DBName,
  CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
  CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
  CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" 
FROM (
  SELECT IFNULL(DB,'All Databases') DBName,
  SUM(DSize) SDSize,
  SUM(XSize) SXSize,
  SUM(TSize) STSize 
  FROM (
    SELECT table_schema DB,
      data_length DSize,
      index_length XSize,
      data_length+index_length TSize 
    FROM information_schema.tables 
    WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA 
    GROUP BY DB WITH ROLLUP
) AA,
(SELECT 2 pw) BB 
ORDER BY (SDSize+SXSize);

Query to Give You Database Size Grouped By Database and Storage Engine in MB

SELECT Statistic,DataSize "Data Size",
  IndexSize "Index Size",
  TableSize "Table Size" 
FROM (
  SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
    IF(ISNULL(engine)=1,10,0) engine_score,
    IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
    IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,
    "Storage for All Databases",
    IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
    CONCAT("Storage for ",B.table_schema),
    CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
    CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
    CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
    CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') TableSize 
  FROM (SELECT table_schema,engine,
    SUM(data_length) DSize,
    SUM(index_length) ISize,
    SUM(data_length+index_length) TSize 
    FROM information_schema.tables 
    WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') 
      AND engine IS NOT NULL 
    GROUP BY table_schema,engine WITH ROLLUP) B,
    (SELECT 2 pw) A) AA 
ORDER BY schemaname, schema_score,engine_score;

All three queries have one thing in common: A simple SELECT query (SELECT 2 pw).

The pw stands for power, the exponent used against the number 1024. You can adjust the query to give you Database Sizes with different units:

(SELECT 0 pw) --reports the Database Size in Bytes
(SELECT 1 pw) --reports the Database Size in Kilobytes
(SELECT 2 pw) --reports the Database Size in Megabytes
(SELECT 3 pw) --reports the Database Size in Gigabytes
(SELECT 4 pw) --reports the Database Size in Terabytes
(SELECT 5 pw) --reports the Database Size in Petabytes (email me if you reach this size)

Give Them a Try !!!

share|improve this answer
    
Too bad I can't give you 2 points up. Thanks for this! –  Mauro Dec 27 '12 at 19:24

Found a script should do what you want -

http://www.modwest.com/help/kb6-199.html

<?

mysql_connect("db.modwest.com", "username", "password");
mysql_select_db("yourdb");

$result = mysql_query("show table status");

$size = 0;
$out = "";
while($row = mysql_fetch_array($result)) {
    $size += $row["Data_length"];
    $out .= $row["Name"] .": ". 
               round(($row["Data_length"]/1024)/1024, 2) ."<br>\n";
}

$size = round(($size/1024)/1024, 1);

echo $out ."<br>\n";
echo "Total MySQL db size: $size";
?>
share|improve this answer

If you're looking for the actual size the database takes up on disk just find the size of the data/dbname directory.

share|improve this answer
    
This is specifically for MyISAM tables –  Andy Sep 7 '09 at 15:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.