22

I would like to understand these ibdata files as these play vital role in the crash recovery procedure. I could not find proper resources over the web for this.

1 Answer 1

21

ibdata1

The file 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)

Click Here to see a Pictorial Representation

You can divorce 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 .ibd file.

Example

  • datadir is /var/lib/mysql
  • CREATE TABLE mydb.mytable (...) ENGINE=InnoDB;, creates /var/lib/mysql/mydb/mytable.frm
  • innodb_file_per_table enabled, Data/Index Pages Stored in /var/lib/mysql/mydb/mytable.ibd
  • innodb_file_per_table disabled, Data/Index Pages Stored in ibdata1

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

iblog files (a.k.a. ib_logfile0, ib_logfile1)

If you want to know what the ib_logfile0 and ib_logfile1 are for, they are the InnoDB Redo Logs. They should never be erased or resized until a full normal shutdown of mysqld has taken place. If mysqld ever crashes, just start up mysqld. It will read across ib_logfile0 and ib_logfile1 to check for any data changes that were not posted to the the double write buffer in ibdata1. It will replay (redo) those changes. Once they are replayed and stored, mysqld becomes ready for new DB Connections.

6
  • How the space will be retained in these log files... ? I mean to ask... "for new innodb transactions how the same log files be used if these are filled up completely...."
    – Uday
    Commented Oct 18, 2012 at 8:44
  • My previous comment is about ib_log files....
    – Uday
    Commented Oct 18, 2012 at 8:46
  • 1
    @ Uday The space will not be retained on the iblog files until the logbuffer becomes empty. It is a dynamic process, where the log space get occupied and released based on the stalled transaction changes recorded in the log buffer. These log files get occupied completly, when ever you perform some huge data loads, which leads to Innodb errors stating "InnoDB: which exceeds the log group capacity"., which mean Log file size should be bigger to do things. Hope my explanation cleared your doubt. Commented Nov 11, 2012 at 23:00
  • innodb_file_per_table disabled, Data/Index Pages Stored in /var/lib/mysql/mydb/mytable.ibd and innodb_file_per_table enbled, Data/Index Pages Stored in ibdata1 should be vice versa, shouldn't it?
    – rabudde
    Commented Sep 21, 2016 at 18:26
  • 1
    @rabudde It's fixed now. Thank you again !!! Commented Sep 21, 2016 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.