InnoDB tables are stored in storage areas called
tablespaces, which contain both data and indexes. In older MariaDB versions, by default, InnoDB used only one tablespace called the system tablespace. It also contains the change buffer, the doublewrite buffer, and by default, the undo log. This tablespace is physically stored in the ibdata
file in the data
directory of MariaDB (ibdata1
, ibdata2
, and so on). While the system tablespace still exists on the latest versions of MariaDB and can still contain all the tables, it is now possible to store each new table in a dedicated tablespace. Each tablespace created in this way is a separate file with the .ibd
extension, which is located in a database subdirectory. This storage method is called the
file-per-table mode. It is enabled by default since MariaDB 10.0.
The file-per-table mode is enabled by default. To enable or disable it, we can use the innodb_file_per_table
system variable. When a table is created, InnoDB checks this variable to determine whether the new table should be created in the system tablespace or in a separate tablespace.
Let's see an example of how it works. Let's create some tables in MariaDB:
MariaDB [test_innodb]> CREATE TABLE haon (col INT NOT NULL PRIMARY KEY) ENGINE = InnoDB; Query OK, 0 rows affected (0.27 sec) MariaDB [test_innodb]> SET @@global.innodb_file_per_table = ON; Query OK, 0 rows affected (0.00 sec) MariaDB [test_innodb]> CREATE TABLE do (col INT NOT NULL PRIMARY KEY) ENGINE = InnoDB; Query OK, 0 rows affected (0.46 sec) MariaDB [test_innodb]> SET @@global.innodb_file_per_table = OFF; Query OK, 0 rows affected (0.00 sec) MariaDB [test_innodb]> CREATE TABLE tri (col INT NOT NULL PRIMARY KEY) ENGINE = InnoDB; Query OK, 0 rows affected (0.26 sec)
Now, let's check the files in the data
directory and in the database
subdirectory:
root@this:/usr/local/mysql# ls data/ibdata* -1 data/ibdata1 root@this:/usr/local/mysql# ls data/test_innodb -1 db.opt do.frm do.ibd haon.frm tri.frm
Of course, the system tablespace exists (the ibdata1
file). In the test_innodb
subdirectory, we can see:
db.opt
).frm
files)do.ibd
When the
haon and
tri tables were created, the innodb_file_per_table
variable was off, and so, no separate tablespace has been created for them: they are stored in the system tablespace. When the
do table was created, innodb_file_per_table
was set to ON
, and so, it is stored in a file called do.ibd
.
Older versions of InnoDB use a file format called Antelope. This format is still used by default for backward compatibility, and the system tablespace uses it. A new file format called Barracuda supports more features. Table compression is only available with the Barracuda file format.
When the file-per-table mode is enabled and a new table is created, InnoDB checks the value of the innodb_file_format
server variable. It contains the name of the file format that will be used for the new tablespace. Both the INNODB_SYS_TABLES
and INNODB_SYS_TABLESPACES
tables in the information_schema
database contain a column called FILE_FORMAT
, which specify the file format used.
Let's see an example by creating two InnoDB tables with two different file formats:
MariaDB [test_innodb2]> SET @@global.innodb_file_per_table = ON; Query OK, 0 rows affected (0.00 sec) MariaDB [test_innodb2]> SET @@global.innodb_file_format = 'Antelope'; Query OK, 0 rows affected (0.00 sec) MariaDB [test_innodb2]> CREATE TABLE um (col INT NOT NULL PRIMARY KEY) ENGINE = InnoDB; Query OK, 0 rows affected (0.37 sec) MariaDB [test_innodb2]> SET @@global.innodb_file_format = 'Barracuda'; Query OK, 0 rows affected (0.00 sec) MariaDB [test_innodb2]> CREATE TABLE dois (col INT NOT NULL PRIMARY KEY) ENGINE = InnoDB ROW_FORMAT = DYNAMIC; Query OK, 0 rows affected (0.39 sec)
Remember that different file formats support different row formats. To use Barracuda, we must specify one of its file formats: DYNAMIC
or COMPRESSED
. The default row format is COMPACT
, which is handled by the Antelope file format, so in this case Barracuda will not be used.
Now, let's check the INNODB_SYS_TABLESPACES
table:
MariaDB [test_innodb2]> SELECT NAME, FILE_FORMAT, ROW_FORMAT FROM information_schema.INNODB_SYS_TABLESPACES WHERE NAME LIKE 'test_innodb2/%'; +-------------------+-------------+----------------------+ | NAME | FILE_FORMAT | ROW_FORMAT | +-------------------+-------------+----------------------+ | test_innodb2/um | Antelope | Compact or Redundant | | test_innodb2/dois | Barracuda | Dynamic | +-------------------+-------------+----------------------+ 2 rows in set (0.00 sec)
When the Antelope format is used, the information_schema
tables do not tell us which row format is used by the tables, as shown in the previous example. Compressed tables use the COMPRESSED
row format.
The innodbfile_per_table
and innodb_file_format
variables are also used when altering a table with the copy algorithm (described in Chapter 4, Transactions and Locks). This means that a table can be moved from the system tablespace to a separate file or vice versa, and the file format may change. Thus, it is necessary to check the values of these variables before issuing ALTER TABLE
, which requires a table copy. Also, when we think that the statement does not require a copy, it is a good idea to specify ALGORITHM = INPLACE
for extra safety. This clause specifies that no table copy must be created. If the value of innodb_file_per_table
or innodb_file_format
is changed, InnoDB will try to rebuild the table, but since this operation requires a table copy, the statement will fail with an error.
Note that the file_per_table
and innodb_file_format
server variables only exist at the global level. This exposes us to a risk, if the current thread is not the only one with the SUPER
privilege. In fact, thread 1 could change one of these variables a fraction of a second before thread 2 executes an ALTER TABLE
command.
The Barracuda file format has been introduced with MariaDB 5.5 and MySQL 5.5. For this reason, it is not possible to replicate table compression or use a physical backup of the tables that use the Barracuda format on a server earlier than 5.5.