This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am going to discuss one of the important topics related to InnoDB Storage Engine of MySQL.
What is a role of the InnoDB ibdata1 file (.idb file)?
The ibdata1 is a file which is using for the purpose of shared tablespace.
I have noticed that this is one of the most important and heavily loaded files 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 an ibdata1 file.
like:
- Table Data Pages.
- Table Index Pages.
- Table Metadata.
- MVCC Data.
- Other buffer related pages.
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 a recommended setting for the InnoDB storage engine because it is storing everything into one file and compare to managing all different table 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 separates 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 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.
