MySQL Bugs: #5968: Allow InnoDB tablespace to be specified in CREATE TABLE

Bug #5968	Allow InnoDB tablespace to be specified in CREATE TABLE
Submitted:	8 Oct 2004 4:53	Modified:	17 Feb 2016 21:42
Reporter:	John David Duncan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.1	OS:	Any
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
Suppose a server is restarted with innodb_file_per_table changed
from 0 to 1, and then new tables are created.  Some tables will 
be in the system tablespace, and others will be in .ibd files.

You might even want to do this deliberately.  After all, the system
tablespace has some advantages over .ibd files: it can be spread over
multiple files, and it can be placed on a raw disk.  

For example, this was suggested to me by a customer with about 15 
tables.  One table is 60 GB in size, and they would like to leave it in 
the system tablespace spread out over several files; the other 14 
tables range from 16KB to 4 GB, and they would prefer to put these 
in .ibd files.

Unfortunately this setup has some problems.  If you run mysqldump,
for instance, there is no way to restore the dump with the same physical
on-disk structure as before.  Either all tables are in the system tablespace,
or all tables are in .ibd files, or you have to bounce the server (and change 
the my.cnf file) in the middle of loading the data!

How to repeat:
n/a

Suggested fix:
One possibility is something like this:
  CREATE TABLE t () [ TABLESPACE=SYSTEM | FILE ] 

(Another possibility might be to let innodb_file_per_table be set as a 
session variable, but I don't see how mysqldump could be made to  
handle that correctly.)

John,

yes, we are considering even a full Oracle-like syntax where one can assign individual tables to individual named tablespaces.

Your simple suggestion is reasonably easy to implement, and will probably be implemented much earlier than the full Oracle-like setup.

However, with RAID disks, the restriction of placing a single table in a single file is not bad from a performance viewpoint. Modern file systems support files > 1000 GB.

Regards,

Heikki

Tablespaces with smaller files are easier for administrative management and provides flexibility 
for non-RAID i/o optimization.  Ideal flexibility would have tablespaces be logical constructs with 
datafiles assigned to them. Of course to keep the defaults simple for the simple cases, the 
present functionality is fine but providing more control over the physical layout a la Oracle would 
fullfill the more complex use cases.

This is now in a 5.7 labs release:
http://mysqlserverteam.com/innodb-general-tablespaces-preview/

Posted by developer:
 
This was fixed in mysql 5.7 with the introduction of General Tablespaces.

Specifically, You can use TABLESPACE=innodb_system or TABLESPACE=innodb_file_per_table in CREATE TABLE or ALTER TABLE to put a table in either tablespace.