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
Triage: Triaged: D5 (Feature request)

[19 Nov 2009 10:55] Peter Laursen
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.
[19 Nov 2009 12:02] Peter Laursen
corrrected 'MyISAM' >> 'non-MyISAM' in synopsis.
[19 Nov 2009 12:15] Peter Laursen
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.
[19 Nov 2009 19:40] Sveta Smirnova
Thank you for the report.

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