DBAs are always try to determine the best way to manage their storage for InnoDB. The three main options include:
- Having one shared InnoDB tablespace data file.
- Setting up individual files per InnoDB table.
- Setting up a shared tablespace across multiple files.
This is fine if you have a simple and small MySQL database. Great for new DBAs to get comfortable with managing MySQL storage.
Option 2: Setting up individual files per InnoDB table.
I like this option when I need to create large transaction tables. This option works well with partitioned objects. Separating your core tables offers more flexibility in administration, backup and recovery and from a storage perspective makes good common sense. I however, do not like using this for all of my InnoDB tables. The more individual files I have to manage the more a DBA will get nickel and dimed from an administration perspective.
Option 3: Setting up a shared tablespace across multiple files.
I like this option because this gives me more flexibility in separating out my I/O if I do not have a storage array doing the striping for me. This allows me to get a lot of my core tables and separate them from other tables by defining their storage in separate data files where I control their location by defining the path of the tablespace data files.
Obviously having a storage array that does your striping and mirroring makes disk I/O management for databases a lot easier. :)
2 comments:
One advantage of file-per-table is that it makes it easier to shrink your disk usage when your data usage shrinks. That is, when you delete a lot of rows, an InnoDB file does not shrink automatically. The file stays big but has "free" space that is allocated as new row versions are created. But occasionally, you end up with a tablespace file that is larger than you anticipate needing, so you'd rather shrink it.
To actually shrink an InnoDB tablespace file, you have to back up *all* your InnoDB tables, delete the tablespace file, and then restore the tables.
If you use file-per-table, you only have to do this for the table(s) you shrank.
Bill,
You're right, this is a nice feature of the InnoDB file per table option when you know you will be need to do this on certain tables.
Good addition!
George.
Post a Comment