Mysql – Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB

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.

The answers to your questions are complex, because they vary by InnoDB file format. Today, there are two formats, called Antelope and Barracuda.

The central tablespace file (ibdata1) is always in Antelope format. If you use file-per-table, you can make the individual files use Barracuda format by setting innodb_file_format=Barracuda in my.cnf.

Basic points:

• One 16KB page of InnoDB data must hold at least two rows of data. Plus each page has a header and a footer containing page checksums and log sequence number and so on. That's where you get your limit of a bit less than 8KB per row.

• Fixed-size data types like INTEGER, DATE, FLOAT, CHAR are stored on this primary data page and count toward the row size limit.

• Variable-sized data types like VARCHAR, TEXT, BLOB are stored on overflow pages, so they don't count fully toward the row size limit. In Antelope, up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page. Barracuda supports a dynamic row format, so it may store only a 20-byte pointer on the primary data page.

• Variable-size data types are also prefixed with 1 or more bytes to encode the length. And InnoDB row format also has an array of field offsets. So there's an internal structure more or less documented in their wiki. [EDIT] Dead link - here looks better now.

Barracuda also supports a ROW_FORMAT=COMPRESSED to gain further storage efficiency for overflow data.

I also have to comment that I've never seen a well-designed table exceed the row size limit. It's a strong "code smell" that you're violating the repeating groups condition of First Normal Form.