Bug #80431 InnoDB-File-Per-Schema supported by a configuration option
Submitted: 18 Feb 2016 15:26 Modified: 29 Sep 2017 6:27
Reporter: Jörg Brühe (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: file, innodb, storage, Tablespace
Triage: Needs Triage: D5 (Feature request)

[18 Feb 2016 15:26] Jörg Brühe
Description:
The original InnoDB design to have all table data in one common tablespace
has its limits when the data become large.  They are avoided by the new 
default "innodb-file-per-table" which fits most installations well.

However, in systems with very many tables this approach leads to a huge
number of files, so the server daemon "mysqld" must have many files open
simultaneously. It may reach the system's limit on open file descriptors.
Also, a restore operation requires the creation of many files, currently
2 per table (".ibd" and ".frm") which is much slower than just the data
transfer.
With the new "general tablespaces" of 5.7, a solution is appearing.

We have a customer with 6400+ database of 70+ tables each, so a restore 
includes the creation of 1 million files.
Even more extreme is an installation with 15,200 databases of 250 tables
each which means 7.6 million files in total.

Obviously, having shared tablespaces holding the data of several files
will help. This also applies to the schema information.

Such installations are typically serving many tenants and create one database
(schema) per tenant, all schemas having the same structure.

For such installations, we would like to have one tablespace per database
for the data. Considering that the MySQL team is also working on abolishing 
the .frm files and using a tablespace for the schema information, we propose
to also have that per database: It doesn't seem good to keep the information
about millions of tables in one file.
Also, the tenant (= the database) seems to be the best unit of import/discard/export in such a system, so it seems best to make that the storage unit for
both the data and the schema information.

Currently, the DBA gets automated handling only for the extreme cases of
"one single tablespace for all data" (innodb-file-per-table = 0) and
"separate tablespace for every table" (innodb-file-per-table = 1).
With the new general tablespaces, it seems appropriate to get a config option
that gives automated handling for "one tablespace per database".
We don't care whether that would be a new option or just another value
for innodb-file-per-table.

The tablespace file should by default be stored in the database directory,
as that is what MySQL DBAs expect. It would be good if the name were somehow
derived from the database name, and a potential conflict with a table name
solved by inserting some otherwise illegal character.

It would be good if the following operations were possible on such a
"database tablespace":
- optimize (in the meaning of "optimize table": getting rid of holes),
- discard and import (like for tables with their own .ibd file, but on
      database level).

How to repeat:
Look at the restore operation of a multi-tenant installation.
[18 Jun 2016 21:36] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0
[20 May 2017 8:58] SIVAKUMAR M
Is this available in 8.0.1-dmr MySQL Community Server?
I could not see any configuration key in variables.
[29 Sep 2017 6:27] Umesh Shastry
Hello Jörg,

Thank you for the reasonable feature request!

Thanks,
Umesh