Bug #59659 Information schema reports in memory and disk size correctly for InnoDB tables

• Submitted: 21 Jan 2011 15:17
• Modified: 24 Dec 2012 9:46
• Reporter: Kyle Joiner
• Status: Duplicate
• Category: MySQL Server: Information schema
• Severity: S4 (Feature request)
• Version: 5.1,5.5
• OS: Any
• CPU Architecture: Any
• Tags: disk, information schema, InnoDB plugin, Memory, size

[21 Jan 2011 15:17] Kyle Joiner

Description:
when possible (perhaps only if innodb_file_per_table is enabled?) that the "in memory size" and the "on disk sizes" are both visible
and reflect reality in the reporting of the Enterprise Monitor.

How to repeat:
Compare reported size(data+index) versus disk size of InnoDB tables.

Suggested fix:
see description.

[21 Jan 2011 16:36] Kyle Joiner

Example difference:

mysql> select TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, (DATA_LENGTH+INDEX_LENGTH) AS total from information_schema.tables where table_name =
'XXXX_XXXXXXXXXXXXXX_000201';
+---------------------------------------+------------+-------------+--------------+----------+
| TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | total |
+---------------------------------------+------------+-------------+--------------+----------+
| XXXX_XXXXXXXXXXXXXX_000201 | 242558 | 11026432 | 6832128 | 17858560 |
+---------------------------------------+------------+-------------+--------------+----------+
1 row in set (0.00 sec)

mysql> ALTER TABLE XXXX_XXXXXXXXXXXXXX_000201 ENGINE=MyISAM;Query OK, 240074 rows affected (0.86 sec)
Records: 240074 Duplicates: 0 Warnings: 0

mysql> select TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, (DATA_LENGTH+INDEX_LENGTH) AS total from information_schema.tables where table_name =
'XXXX_XXXXXXXXXXXXXX_000201';
+---------------------------------------+------------+-------------+--------------+---------+
| TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | total |
+---------------------------------------+------------+-------------+--------------+---------+
| XXXX_XXXXXXXXXXXXXX_000201 | 240074 | 2640814 | 4913152 | 7553966 |
+---------------------------------------+------------+-------------+--------------+---------+
1 row in set (0.00 sec)

this table had been converted to Innodb previously, converting back
mysql> select TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, (DATA_LENGTH+INDEX_LENGTH) AS total from information_schema.tables
where table_name = 'XXXX_XXXXXXXXXXXXXX_000134';
+---------------------------------------+------------+-------------+--------------+--------+
| TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | total |
+---------------------------------------+------------+-------------+--------------+--------+
| XXXX_XXXXXXXXXXXXXX_000134 | 8680 | 376832 | 180224 | 557056 |
+---------------------------------------+------------+-------------+--------------+--------+
1 row in set (0.00 sec)

mysql> ALTER TABLE XXXX_XXXXXXXXXXXXXX_000134 ENGINE=MyISAM;Query OK, 8934 rows affected (0.10 sec)
Records: 8934 Duplicates: 0 Warnings: 0

mysql> select TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, (DATA_LENGTH+INDEX_LENGTH) AS total from information_schema.tables where table_name =
'XXXX_XXXXXXXXXXXXXX_000134';
+---------------------------------------+------------+-------------+--------------+--------+
| TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | total |
+---------------------------------------+------------+-------------+--------------+--------+
| XXXX_XXXXXXXXXXXXXX_000134 | 8934 | 98274 | 187392 | 285666 |
+---------------------------------------+------------+-------------+--------------+--------+
1 row in set (0.01 sec)

# du -sh /mysql/exp/data/exp/XXXX_XXXXXXXXXXXXXX_000134*
12K	/mysql/exp/data/exp/XXXX_XXXXXXXXXXXXXX_000134.frm
96K	/mysql/exp/data/exp/XXXX_XXXXXXXXXXXXXX_000134.MYD
184K	/mysql/exp/data/exp/XXXX_XXXXXXXXXXXXXX_000134.MYI

[24 Dec 2012 9:46] Erlend Dahl

Fixed in 5.1.59 under the heading of an internally filed bug.