ibdata1 is the system tablespace for the InnoDB infrastructure.
It contains several classes for information vital for InnoDB
- Table Data Pages
- Table Index Pages
- Data Dictionary
- MVCC Control Data
- Undo Space
- Rollback Segments
- Double Write Buffer (Pages Written in the Background to avoid OS caching)
- Insert Buffer (Changes to Secondary Indexes)
Please note ibdata1's place in the InnoDB Universe (on Right Side)
You can separate Data and Index Pages from
ibdata1 by enabling
innodb_file_per_table. This will cause any newly created InnoDB table to store data and index pages in an external
- datadir is
CREATE TABLE mydb.mytable (...) ENGINE=InnoDB;, creates
innodb_file_per_table enabled, Data/Index Pages Stored in
innodb_file_per_table disabled, Data/Index Pages Stored in
No matter where the InnoDB table is stored, InnoDB's functionality requires looking for table metadata and storing and retrieving MVCC info to support ACID compliance and Transaction Isolation.
Here are my past articles on separating table data and indexes from ibdata1
WHAT TO DO NEXT
You can continue having
ibdata1 stored everything, but that makes doing LVM snapshots real drudgery (my personal opinion).
You need to use my StackOverflow post and shrink that file permanently.
Please run this query:
((POWER(1024,3)*94 - InnoDBDiskDataAndIndexes))/POWER(1024,3) SpaceToReclaim
(SELECT SUM(data_length+index_length) InnoDBDiskDataAndIndexes
FROM information_schema.tables WHERE engine='InnoDB') A;
This will tell how much wasted space can be reclaimed after applying the InnoDB Cleanup.