File Per Table Mode in InnoDB

After very long time a tutorial on MySQL, earlier I have written few articles on MySQL in sequence so I have taken a break from MySQL and was concentrating on other section to write new articles.

Now again its time for MySQL and in this article we will discuss about the storage pattern of one of the MySQL database storage engine “InnoDB“. Let’s assume you have default settings placed in configuration file of MySQL and you have done nothing in your MySQL configuration.

The place where data of the MySQL tables are stored is called as “tablespace“. With InnoDB all the data are stored in system tablespace which is also defalut place to store the data for MySQL. This means all the data are stored in one single file rather than separate files for each table. This system tablespace file is placed under your data folder with the name “ibdata1“.

The place where data of the MySQL tables are stored is called as “tablespace”.

Now assume that your system tablespace file (ibdata1) has grown up and reach to the size around 2-3 GB. Each operation will have to manipulate this BIG file.

To overcome this problem we have one configuration option available in MySQL for InnoDB which is File Per Table Mode in MySQL. So when you enable this mode, MySQL will create a separate file for each of the table with InnoDB storage engine under respective database folder name. Name of this file will be tbl_name.ibd.

Unlike MyISAM, InnoDB will have only one file for data and indexes but the .frm file for structure of the table will be creates as usual.

Enable File Per Table Mode

To enable this mode you just need to place one line in your MySQL configuration file which is my.cnf or my.ini based on OS which you are using. Have a look at below block for the same:

[cc lang=”mysql”]
innodb_file_per_table = 1
[/cc]

Another way to make this happen is execute a query which sets that variable for that session.

[cc lang=”mysql”]
SET GLOBAL innodb_file_per_table=1;
[/cc]

After placing above code you need to restart the MySQL service to apply the changes we have made just now. After restarting the MySQL service all new tables are created with their separate tablespace under respective database folder.

It is recommoded to have this option enabled to get the better performance from MySQL.

What about the old tables?

You might think after activating this mode all the InnoDB tables will have separate file for each table. But this is not true. The tables which are created before activating this mode will still be there in system tablespace. This mode will only affect to tables which are created after activating this mode.

But don’t worry there will be no problem in reading and writing data from the tables which are in system tablespace. But what if you want all old tables to be have separate files just like new table?

There are several ways to get this done but I will provide here the simplest one. You have to rebuild all the older tables to have separate data files for each tables under respective database folder. You just need to execute below command for each table.

[cc lang=”mysql”]
ALTER TABLE tablename ENGINE=InnoDB;
[/cc]

Conclusion

I would recommend to have this option enabled because you will definately gain the performance with this. Also you will have less change to loose your data because if you have not enabled this option and just imagine that your system tablespace file (ibdata1) gets corrupted? Isn’t it HORRIBLE?

So enable this feature in your configuration if it is not done yet and subscribe to our RSS Feed by Email to get more updates on MySQL and other nice stuffs for FREE right into your inbox. Also don’t forget to Follow us on Twitter and Like us on Facebook. Keep sharing your thoughts too via comments.