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:
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.
@anonymous
Thanks for pointers, i completely forgot about the innodb plugin :). Updated the post.
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.
thanks a lot for information and details
Post a Comment