MySQL: Why InnoDB Engine stores all databases in one file (ibdata1)?

In this post, I am going to discuss one of the important topic related to InnoDB Storage Engine of MySQL.

What is a role of the InnoDB ibdata1 file (.idb file)?

The ibdata1 is a file which InnoDB is used for the purpose of shared tablespace.
I have noticed that, this is one of the most important and heavily loaded file of MySQL server.
If we delete this file, we will not be able to get MySQL after restart.

What is the role format file (.frm file) ?
FRM stands for FoRMat and it is storing data dictionary information for tables in .frm files. It describes the table’s format like: table definition, including fields and structure. The name of this file is same as the table name.

If we look at the architecture of InnoDB, it stores the different type of info pages into ibdata1 file.

  • Table Data Pages.
  • Table Index Pages.
  • Table Metadata.
  • MVCC Data.
  • Other buffer related pages.

Actually, there are two basic parameters associated with it.

innodb_file_per_table (disabled):
This is a default setting and because of this InnoDB stores all pages into a single file called ibdata1.
This is recommended setting for the InnoDB storage engine because it is storing everything into one file and compare to managing all table different files it is easy to manage everything with the single file.

innodb_file_per_table (enabled):
If we enable this parameter, it creates individual .frm file and .idb file for all InnoDB tables. The ibdata1 will never contain InnoDB data and indexes, it stores only meta data of the tables. This is simply separated table and index pages from the meta data pages.

InnoDB is storing everything into one file because of this default setting innodb_file_per_table disabled.

Many people are trying to separate this file by creating different .idb files, but actually sometimes it leads to more fragmentation of data and index pages.

To clean up the InnoDB infrastructure using a default ibdata1 file is reasonable, compared to cleaning multiples .idb files.
But as per our requirements We can change this default setting.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

Be the First to Comment!

Notify of