Quote:
Originally Posted by fryer
Greate writeup! I'm not sure if you mentioned this but one thing to note about InnoDB that you would want to set is doing InnoDB per table. This way all of your tables that are set to InnoDB are not jumbled up in 1 innodb file. This is also a problem when you want to delete data from a table it will not shrink the InnoDB file. So doing InnoDB per table is a good idea.
|
I did not mention that, thanks for adding it
As with INNODB vs. InnoDB though, "one file" vs. "file per table" each have their own set of Pros and Cons and should be used depending on your situation.
"file per table" PROS:
- Ability to use OPTIMIZE to compact/re-create the tablespace
- Can symlink individual tables out to separate disk sub-systems, boosting performance (symlink functionality isn't perfect yet though for this)
"file per table" CONS:
- It's not default configuration and is a newer feature, thus more possibility of bugs occurring.
- InnoDB will need to run more sync to disk operations (every 10 seconds)
- You cannot spread a large table across several disks
- Uses more file descriptors (only a Windows-problem really as they have a hard-capped descriptors limit).
So, if you have a database that doesn't grow and shrink very often (say you almost never delete records), but which only grows and grows, you would be better off with using a single large file. If you have a ton of small tables, you'll also likely be better off with a single file.
If your database does do a lot of inserts/deletes and doesn't have hundreds of tables, then yes, use "file per table".