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. :)