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.
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.