Monday, May 12, 2008

Variable's Day Out #10: innodb_file_per_table

Properties:

Applicable To InnoDB
Server Startup Option --innodb-file-per-table
Scope Global
Dynamic General InnoDB Engine: No
InnoDB plug-in: Yes
Possible Values Enable|Disable Flag
Default Value Disabled
Category Maintenance

Description:

This variable if enabled, makes InnoDB to create a separate .ibd file for storing indexes and data. Setting this variable makes it easy to manage disks with huge tables. Having this option enabled, makes it a lot easier to move bigger (or smaller) .ibd files to separate physical disks and/or have backups of certain tables without affecting others.

As I have mentioned in my earlier post, that InnoDB does not return back the space once claimed for a tablespace, this variable comes handy in managing such situations. Though I have not seen any performance gain while using this option, people do cite such metrics. Again, I would say as always that it may be case dependent.

It should be noted that this option only effects tables that are created after this option is enabled (and server is restarted). Tables that are created with this option disabled are created in the shared tablespace and remain there even if this option is enabled in between. Also, even if all the tables are created with this option enabled, InnoDB still needs the shared tablespace to store its internal data dictionary and undo logs.

Speical case: InnoDB plug-in

With the introduction of InnoDB plug-in, this variable becomes dynamic in nature and holds much more importance than just disk maintenance. You should have this variable enabled to be able to use the new file format. But even in InnoDB plug-in the restriction on tablespaces not returning disk space is intact.

Read More:

4 comments:

Anonymous said...

In the recent release of InnoDB plugin (http://www.innodb.com/innodb_plugin/), --innodb-file-per-table becomes dynamic. You do not need to restart the the server after changing this variable.

Tom said...

Also, if you so desire you can do an optimize table to get the table switched to the innodb-file-per-table format. Again, you won't gain anything back in your ibdata* file.

Another issue with innodb-file-per-table is that you still need the ibdata* file. It can be smaller, but it is still needed.

Parvesh Garg said...

@anonymous
Thanks for pointers, i completely forgot about the innodb plugin :). Updated the post.

Parvesh Garg said...

Tom,
thanks for the switching pointer. I intend to do another post on tablespace and will include this there.

Yes, ibdata* files (shared tablespace) as mentioned in the post is needed for InnoDB to store its internal data dictionary and undo logs.