How do you extend the tablespace when using innodb_file_per_table?

Question

With innodb_file_per_table off, you can create multiple tablespaces on multiple devices if necessary to manage growth, balance I/O, etc.

With the option on, how do you control growth of the files? Do they autoextend? And can you set a maximum and then extend the tablespace for a given table onto another filesystem if necessary?

Answer 1

This is interesting because last month (July 25, 2012 15 days ago) some asked a similar question about extending the system tablespace file ibdata1 : Database Design - Creating Multiple databases to avoid the headache of limit on table size

Please read that link and see if you would like to extend ibdata1 the way I detailed

To be totally honest with you, storing ibdata across separate volumes from the datadir is a bad idea , and storing .ibd files is even worse, because of

• MetaData Issues : I wrote about this as well
  • Mar 25, 2012 : Why does InnoDB store all databases in one file? (See WARNING)
  • Oct 16, 2011 : Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql
  • Sep 28, 2011 : How to Recover an InnoDB table whose files were moved around
• Performance Issues : I wrote about that back in August 2011
  • MySQL InnoDB data file directly on a disk slice (fixed-size)?

You will improve things if you go with innodb_file_per_table. Why?

• You only need one ibdata1 file
• No data would reside in ibdata1
• All data and index pages would not spread across multiple tablespaces

Controlling individual tablespace growth is rather simple. You must schedule proper maintenance windows for this:

For example, to shrink an InnoDB table named mydb.mytable simply run

ALTER TABLE mydb.mytable ENGINE=InnoDB;

With innodb_file_per_table on, the table shrinks.

With innodb_file_per_table off, ibdata1 just grows rapidly.

There is no autoextend feature for .ibd files. That only applies to ibdata1. In light of this, the maximum size of an InnoDB table stored in a .ibd file would be OS dependent:

• In ext3, an InnoDB table can go to 2TB
• In ext4, an InnoDB table can go to 16TB

To be more blunt, you should never attempt to spread .ibd to other volumes. Percona has strongly denounced this : http://www.mysqlperformanceblog.com/2010/12/25/spreading-ibd-files-across-multiple-disks-the-optimization-that-isnt/