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 6:53	Modified:	8 Oct 2004 15:13
Reporter:	John David Duncan
Status:	Open
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:	4.1	OS:
Assigned to:	Heikki Tuuri	Target Version:
Triage:	D5 (Feature request)

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.