MySQL Bugs: #48899: storage requirement for non-MyISAM poorly documented and 'instrumentated'

Bug #48899	storage requirement for non-MyISAM poorly documented and 'instrumentated'
Submitted:	19 Nov 2009 10:55	Modified:	19 Nov 2009 19:40
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0, all	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
1) documentation

This page 
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

.. documents detailed storage requirements for MyISAM but for other engines it says "Different storage engines handle the allocation and storage of this data in different ways, according to the method they use for handling the corresponding types. See Chapter 13, Storage Engines, for more information."

.. but whatever ENGINE I look up I do not find similar information

2) 'instrumentation'

Data and Index size information in SHOW TABLE STATUS as well as SELECT `table_name`, SUM(`DATA_LENGTH`+ `INDEX_LENGTH`) FROM information_schema.TABLES
WHERE `TABLE_SCHEMA`='******' AND `table_name`='****';

seem to be designed for MyISAM only.

How to repeat:
see above

Suggested fix:
Provide documentation and 'instrumentation' for getting realistic storage estimates for other Engines.

corrrected 'MyISAM' >> 'non-MyISAM' in synopsis.

a small example:

/*
CREATE TABLE `lmt_datastore` (
  `title` CHAR(50) NOT NULL DEFAULT '',
  `data` MEDIUMTEXT,
  `unserialize` SMALLINT(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`title`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8
*/
SHOW VARIABLES LIKE 'innodb_file_per_table'; -- returns ON

SELECT `table_name`, SUM(`DATA_LENGTH`+ `INDEX_LENGTH`) FROM information_schema.TABLES
WHERE `TABLE_SCHEMA`='stortest' AND `table_name`='lmt_datastore'; 
-- returns '49972'
-- file system info; MYD: 47 KB. MYI: 2KB - all this is reasonable

ALTER TABLE lmt_datastore ENGINE INNODB;  -- (28 row(s) affected)

SELECT `table_name`, SUM(`DATA_LENGTH`+ `INDEX_LENGTH`) FROM information_schema.TABLES
WHERE `TABLE_SCHEMA`='stortest' AND `table_name`='lmt_datastore'; 
-- returns 'SELECT `table_name`, SUM(`DATA_LENGTH`+ `INDEX_LENGTH`) FROM information_schema.TABLES
WHERE `TABLE_SCHEMA`='stortest' AND `table_name`='lmt_datastore'; 
-- returns '98304'
-- file system info; lmt_datastore.ibd = 144 KB - everything completely inconsistent.

Thank you for the report.

Verified as described. Although you can find more information in chapter 13 (as suggested).