Bug #71058 ibdata segment breakdown

• Submitted: 3 Dec 2013 12:41
• Modified: 3 Dec 2013 15:32
• Reporter: Dave Vaughan
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 5.5.33
• OS: Any
• CPU Architecture: Any
• Tags: innodb, undo

[3 Dec 2013 12:41] Dave Vaughan

Description:
I'm seeing a very large ibdata1 file even with innodb_file_per_table enabled. I've tried searching and can find no way of knowing what is taking up the space. My hypothesis is that it is UNDO as I'm doing consistent backups but I can't confirm this. Innodb_file_per_table was enabled before the user databases were created so it's normal row/index data.

How to repeat:
N/A

Suggested fix:
I would like a table to query that would show a break down of the ibdata1 file, DD UNDO etc. with the sizes etc.

[3 Dec 2013 12:45] MySQL Verification Team

http://dev.mysql.com/doc/refman/5.7/en/innochecksum.html

innochecksum from 5.7.2 can print a page type summary, maybe that will help?

[3 Dec 2013 15:32] Dave Vaughan

Hi Shane,

It sort of helps but is only any good when the system is down - I get lock errors whilst mysqld is up. A dynamic table in the information_schema would be good, like INNODB_TABLESPACE_DATA with a row for each tablespace, much like the innochecksum --page-file-summary output:

+-------+---------+------------+-------------+-------------+----------------+-----------
| SPACE | NAME	  | UNDO_PAGES | INDEX_PAGES | INODE_PAGES | FRESH_AL_PAGES | PAGE_SIZE 
+-------+---------+------------+-------------+-------------+----------------+-----------
|     1 | ibdata1 |    401     |      19     |      6      |      4328      |   16384   

Cheers,
Dave