Bug #60637 InnoDB option to have a file per database
Submitted: 25 Mar 2011 13:14 Modified: 25 Mar 2011 14:02
Reporter: Victor Ferrada Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.5.9 OS:Any
Assigned to: Kevin Lewis CPU Architecture:Any
Tags: innodb_file_per_database, innodb_file_per_table

[25 Mar 2011 13:14] Victor Ferrada
Description:
Hello,

I have been unable to find any information on this subject so perhaps it hasn't already been suggested?

In any case, here goes:

Currently (5.1.5X, 5.5.X) there is the option to turn on the innodb_file_per_table option but unfortunately, it's been shown that performance begins to suffer on larger queries or when numerous tables are present (fsynch, i/o etc).

However, keeping a single ibdata file with all data has its own problems, not easy to recover space for the filesystem (not a big deal since it'll be used again anyway), but mostly, if a corruption occurs within a database, there is high probability that it will affect all other databases sharing the file (first-hand experience, restore from slave only way to recover). This is particularly troublesome on a server that serves a large number of databases to different teams.

Is there any middle ground? Such as having the option to create one innodb file per database (innodb_file_per_database if you will). This would serve both purposes, including smaller data files and a single file opened for queries within the same database. It would also make using the binary backups (Enterprise Backup) for specific databases smaller and faster, without forcing the system to specifically use the innodb_file_per_table option.

Thank you. I apologize if it's already been requested.

How to repeat:
Change the my.cnf to include the option innodb_file_per_database to have each database use its own ib* file.

Suggested fix:
Add an option to have one innodb data file per database (with the possibility of adding more per db?).
[25 Mar 2011 14:02] Valeriy Kravchuk
This feature may be useful in some cases.
[14 Nov 2012 10:04] Rolf Neuberger
Seconding the request. This is a good middle ground between default behaviour and the existing file-per-table option. Usually tables within a database are the highest-priority candidates for joins, so keeping them together in one file provides useful semantics to the OS, to help with caching and allocation strategies based on natural data locality.

Nice little (side) benefits:
*Reduction in the sheer amount of open files vs file-per-table
*Reduced inode usage
*simplified maintenance
[25 Apr 2013 7:23] Juanjo Garcia
I also ask for this feature.

Having one file (with extents) per database is worth because of ease of management, ability to shrink, and to define data size of databases.

And I think that having the logic to create a file per table, implementing the file creation at "create database" command will not be difficult to implement.
[15 Apr 2016 20:26] Bill Karwin
FWIW, MySQL 5.7 now has an option to create general tablespaces with a separate .ibd datafile per tablespace. When you create or alter an InnoDB table, you can declare the tablespace in which to create the table.

See:

http://mysqlserverteam.com/general-tablespaces-in-mysql-5-7-details-and-tips/

https://dev.mysql.com/doc/refman/5.7/en/create-tablespace.html

Does this resolve the need for this feature request?