← Back to team overview

maria-discuss team mailing list archive

R: MariaDb tablespaces?



In current MariaDB versions, InnoDB has 2 types of tablespaces:
* system tablespace
* per-table tablespace

There is a variable called innodb_file_per_table, you can change it at runtime. When a new table is created with innodb_file_per_table=1, the table has its own tablespace (an .ibd file in the database directory, which contains both data and indexes). When a table is created while innodb_file_per_table=0, it is written into the system tablespace. The system tablespace also contains other information, like the InnoDB data dictionary, un logs, etc.

I see no particular advantage in creating new tables into the system tablespace - I consider it a feature inherited from old versions, when there were no alternatives. Remembers that the system tablespace never shrinks, even if you drop a table. Also, when using a dedicated tablespace, you can decide where the new table will be placed (see CREATE TABLE options). And you can backup only some tables, if you want to. This also enables table compression and encryption.

In MySQL 5.7, you can group multiple tables into one tablespace - which means, not necessarily the system tablespace. From what I see in this page, I think (and hope) and this feature will also be in MariaDB 10.2:

Other storage engines don't have tablespaces: a table is stored in a file (or in 2 files, if data and indexes are stored separately).


Lun 18/4/16, Ghazi Btissam <btissam.ghazi@xxxxxxxxx> ha scritto:

 Oggetto: [Maria-discuss] MariaDb tablespaces?
 A: maria-discuss@xxxxxxxxxxxxxxxxxxx
 Data: Lunedì 18 Aprile 2016, 12:38
 Is there tablespace in MariaDb like Oracle?if yes
 which storage engine use them and how?
 -----Segue allegato-----
 Mailing list: https://launchpad.net/~maria-discuss
 Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp