Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

One of my MySQL databases has been running on a single PC from the last four years.

Here is my setup:

  • MySQL 5.5.14
  • Windows XP on NTFS partition
  • All of my tables are using InnoDB engine

Here are two things I want to determine:

  • the maximum database and table size supported by this version and engine on the platform I mentioned above
  • the current database size and table sizes of all the tables in a database.
share|improve this question

1 Answer 1

up vote 3 down vote accepted

Here are some nice queries I have used for years:

This reports the amount of data and indexes for each storage engine:

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 3 pw) A ORDER BY TSize;

This reports the amount of data and indexes for each database:

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 3 pw) BB ORDER BY (SDSize+SXSize);

This reports the amount of data and indexes for each database by storage engine:

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 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

Please notice at the end of each query there is an inline query (SELECT 3 pw). The pw is simply an exponent used as a power of 1024. Here is how to use this:

  • (SELECT 0 pw) reports in bytes
  • (SELECT 1 pw) reports in kilobytes
  • (SELECT 2 pw) reports in megabytes
  • (SELECT 3 pw) reports in gigabytes
  • (SELECT 4 pw) reports in terabytes
  • (SELECT 5 pw) reports in petabytes (If you got this much data, write me or at least post the display in DBA.SE. I'm sure we'd all love to see that)

Give it a Try !!!

As for table limits, here are those limits for MyISAM and InnoDB.

share|improve this answer
    
thanks for the queries. I tried them and they are working fine. I am confused on the maximum table size supported. The link you provided says maximum tablespace size for InnoDB is 64TB? –  RPK Sep 14 '11 at 4:11
    
@RPK - That is correct. However, please remember that if you use innodb_file_per_table, then the max table size is 64TB. –  RolandoMySQLDBA Sep 14 '11 at 10:54
    
This means each InnoDB table max size is 64 TB and that itself determines the max. database size? –  RPK Sep 14 '11 at 13:40
1  
@RPK - If innodb_file_per_table is disabled, then that limits the ibdata1 file 64TB since ibdata1 is a shared tablespace file. If innodb_file_per_table is enabled, each table's size limit is 64TB. –  RolandoMySQLDBA Sep 14 '11 at 13:48

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.