Morgan Tocker ([info]mtocker) wrote,
@ 2008-02-13 09:36:00
Previous Entry  Add to memories!  Tell a Friend!  Next Entry
Entry tags:mysql

Pros and Cons of InnoDB's file per table
A customer recently asked what the pros and cons are of using the innodb_file_per_table. It's a good question - since the right answer will depend a lot on your data. I'm a fan of the file per table option, it's particularly helpful when you delete a lot of data and want to reclaim the space. So here is the list I came up with:

The pros:
* Normally the InnoDB tablespace never shrinks, but when using file per table you can run run an OPTIMIZE to compact/recreate it (this is a big win).
* You can symlink out individual tables[1] to be on different disk subsystems (can give some improvement, but the global tablespace is still shared).

The cons:
* You can't spread an individual table across several disks (without file per table, it's quite easy to create many segments of a few GB each and move them around).
* More file descriptors are used.
* Internally InnoDB will run more sync operations to disk every 10 seconds.
* It's not in the default configuration, and has not been around for as long (higher chance of bugs not being discovered).

The limited file descriptors problem is more aparent on Windows, since we have a hard limit of 2048. If your data doesn't grow/shrink that often and you have *many* small tables, then you will also likely be worse off with innodb_file_per_table under Linux.

[1] Be aware symlinks are only partially supported.




(Post a new comment)

jcftfhdgzi
(Anonymous)
2008-05-07 01:12 pm UTC (link)
valium pill

(Reply to this)


Create an Account
Forgot your login or password?
Login w/ OpenID
English • Español • Deutsch • Русский…