MySQL 5.7 InnoDB Tablespace

By default for MySQL server, InnoDB Engine is getting used widely due it’s ACID support, optimized read-write performance and for many other reasons which are great significance for the database server.

In this blog post, we are going to cover the InnoDB tablespace and its features like,

  • InnoDB engine tablespaces
  • Tablespace Data Encryption
  • Tablespace related Configuration

InnoDB engine tablespaces

System tablespace:  

Common tablespace for MySQL server operations. Apart from the table data storage, InnoDB’s functionality requires looking for table metadata, storing and retrieving MVCC info to support ACID compliance and Transaction Isolation. It contains several types of information for InnoDB objects.

  • Contains:
    Table Data Pages
    Table Index Pages
    Data Dictionary
    MVCC Control Data
    Undo Space
    Rollback Segments
    Double Write Buffer (Pages Written in the Background to avoid OS caching)
    Insert Buffer (Changes to Secondary Indexes)
  • Variables:
    innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

    By enabling innodb_file_per_table (the default) option, we can store each newly created table (data and index) in a separate tablespace. Advantage for this storage method is less fragmentation within disk data file.

InnoDB data dictionary:
Storage area in system tablespace made up of internal system tables with metadata information for objets[tables, index, columns etc.]

Double write buffer:
Storage area in system tablespace where innodb writes pages from innodb buffer pool, before writing to their proper location in the data files.
In case mysqld process crash in the middle of a page writes, at the time of crash recovery InnoDB can find a good copy of the page from doublewrite buffer.

Variable: inndb_doublewrite (default enable)

REDO logs:
Use for crash recovery. At the time of mysqld startup, InnoDB performs auto recovery to correct data written by incomplete transactions. Transactions that not finish updating data files before an unexpected mysqld shutdown are replayed automatically at the time of mysqld startup even before taking any connection. It uses LSN(Log Sequence Number) value.
Plenty of data changes can not get written to disk quickly, so it will go under redo and then to the disk.

Why we need a redo for recovery?
Let’s take an example, User changing data in innodb buffer and commit, somewhere it needs to go before writing into a disk. Because in the case of crash buffer data will lost, that’s why we need redo logs.

– In redo, all changes will go with info like row_id, old column value, new column value, session_id and time.
– One commit complete data will under disk in a data file.
– Variables:
Innodb_log_file_in_group= [# of redo file groups]

innodb_log_buffer_size= [ Buffer size ] (Set greater value to hold large transactions in memory. Start from 10-20% of total log files size)

Innodb_log_file_size= [Size for each redo file]  (Should be set to a greater value greater for BLOB data types in database )

UNDO tablespace and logs:

UNDO tablespace contains one or more undo logs files.
UNDO manages consistent reads by keeping modified uncommitted data for active transaction [MVCC]. Unmodified data is retrieved from this storage area.Undo logs also called as rollback segments
By default, UNDO logs are part of system tablespace, MySQL allows to store undo logs in separate UNDO tablespace/s [Introduce in MySQL 5.6]. Need to configure before initializing mysqld server.

– When we configure separate undo tablespace, the undo logs in the system tablespace become inactive.
– Need to configure before initializing mysqld server and can not change after that.
– We truncate undo logs, but can not drop.
– The default initial size of an undo tablespace file is 10MB.
– Variables :
innodb_undo_tablespace : Number of undo tablespaces, default 0 , max 95
innodb_undo_directory : Location for undo tablespace,default is data_dir with 10MB initial size.
innodb_undo_logs : Number of undo logs in a single undo tablespace, default and max value is ‘128’ [ Deprecated in 5.7.19 , innodb_rollback_segments variable will control this]
innodb_undo_log_truncate: truncate undo tablespace, Default OFF  [When enabled, undo tablespaces that exceed the threshold value defined by innodb_max_undo_log_size are marked for truncation. ]

Key Points:

  • Truncating undo logs need separate undo logs. This means undo in system tablespace can not be truncated.
  • innodb_undo_tablespaces must be set to a value equal to or greater than 2.
  • innodb_rollback_segments must set to a value equal to or greater than 35.

Benefits: Reduce the size of the single tablespace (system tablespace), since we are storing long-running transactions into a sperate single/multiple UNDO tablespaces.

Temporary tablespace:

Storage to keep and retrieve modified uncommitted data for temporary tables and related objects.Introduce in MySQL 5.7.2 and use for rollback temp table changes while a server is running.

– Undo logs for temporary tables reside in the temp tablespace.
– Default tablespace file ibtmp1 getting recreated on server startup.
–  Not getting used for crash recovery.
– Advantage: Performance gain by avoiding redo logging IO for temp tables and related objects.
– Variable:
innodb_temp_data_file_path = ibtmp1:12M:autoextend (default)

General tablespace:

Shared tablespace to store multiple table data. Introduce in MySQL 5.7.6. A user has to create this using CREATE TABLESPACE syntax. TABLESPACE option can be used with CREATE TABLE to create a table and ALTER TABLE to move a table in general table.

– Memory advantage over innodb_file_per_table storage method.
– Support both Antelope and Barracuda file formats.
–  Supports all row formats and associated features.
–  Possible to create outside data directory.

Tablespace Data Encryption:

InnoDB supports data encryption for InnoDB tables stored in file-per-table tablespaces using mysql keyring. MySQL 5.7.11 and higher includes a keyring plugin.

keyring_file: Stores keyring data in a file local to the server host. keyring_file must be loaded at each server startup using the –early-plugin-load option

keyring_okv: Back end keyring storage products such as Oracle Key Vault, This plugin is available in MySQL Enterprise Edition distributions.

early-plugin-load : Settings ensure that plugin is available prior to initialization of the InnoDB storage engine.
keyring_file_data : keyring file path.

Tablespace related Configuration and operations:

MySQL InnoDB Configuration:


## InnoDB Configuration ##

# InnoDB Memory
innodb_buffer_pool_size = 2000M

# System Tablespace configuration
innodb_data_file_path= ibdata1:512M;ibdata2:512M:autoextend

# Redo log and buffer configuration

#InnoDB file formate
innodb_file_format = Barracuda

# UNDO Tablespace Configuration
innodb_undo_directory =  /var/lib/mysql/
innodb_undo_tablespaces = 3
innodb_undo_logs = 128
innodb_undo_log_truncate = ON
innodb_rollback_segments = 128

# Temp Tablespace Configuration
tmpdir = /var/lib/mysql/
innodb_temp_data_file_path = ibtmp1:20M:autoextend

# Keyring configuration


MySQL Server Initialization Logs:

[Note] InnoDB: Using Linux native AIO
[Note] InnoDB: Number of pools: 1
[Note] InnoDB: Using CPU crc32 instructions
[Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
[Note] InnoDB: Opened 4 undo tablespaces
[Note] InnoDB: 4 undo tablespaces made active
[Note] InnoDB: Highest supported file format is Barracuda.
[Note] InnoDB: Creating shared tablespace for temporary tables
[Note] InnoDB: Setting file './ibtmp1' size to 20 MB. Physically writing the file full; Please wait ...
[Note] InnoDB: File './ibtmp1' size is now 20 MB.
[Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
[Note] InnoDB: 32 non-redo rollback segment(s) are active.
[Note] InnoDB: Waiting for purge to start
[Note] InnoDB: 5.7.19 started; log sequence number 2454162
[Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
[Note] Plugin 'FEDERATED' is disabled.
[Note] InnoDB: Buffer pool(s) load completed at 170828 12:03:52

UNDO and Temporary tablespaces:


General tablespace Example:

General Tablespace can be created inside mysql datadir [ Default ] or outside of the MySQL data directory.


# Create General tablespace
mysql> CREATE TABLESPACE gen_tblsp ADD DATAFILE 'gen_tlbsp.ibd' ENGINE = INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from INFORMATION_SCHEMA.FILES  where TABLESPACE_NAME ='gen_tblsp'\G
*************************** 1. row ***************************
             FILE_ID: 27
           FILE_NAME: ./gen_tlbsp.ibd
     TABLESPACE_NAME: gen_tblsp

# Create table inside general tablespace.
mysql> CREATE TABLE gen_ts_tbl (id int(11), c_desc varchar(100), c_comments text ) TABLESPACE gen_tblsp;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO gen_ts_tbl values (1, 'test' , 'General tablespace testing');
Query OK, 1 row affected (0.01 sec)

mysql> select * from gen_ts_tbl;
| id   | c_desc | c_comments                 |
|    1 | test   | General tablespace testing |
1 row in set (0.00 sec)

# Move Existing table into general tablespace.

mysql> create table innodb_table (id int (11), uname varchar(78));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into innodb_table values(1,'moving to gen_tblsp');
Query OK, 1 row affected (0.01 sec)

mysql> ALTER TABLE innodb_table TABLESPACE gen_tblsp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from innodb_table;
| id   | uname               |
|    1 | moving to gen_tblsp |
1 row in set (0.00 sec)

# DROP General Tablespace [ We need to drop all table in general tablespace before dropping it]

mysql> show tables;
| Tables_in_test |
| gen_ts_tbl     |
| innodb_table   |
2 rows in set (0.00 sec)

mysql> drop table gen_ts_tbl;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table innodb_table;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.01 sec)

mysql> drop tablespace gen_tblsp;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.FILES  where TABLESPACE_NAME ='gen_tblsp'\G
Empty set (0.00 sec)


InnoDB TDE using a keyring_file plugin:

        WHERE PLUGIN_NAME LIKE 'keyring%';
| keyring_file | ACTIVE        |
1 row in set (0.00 sec)

mysql> show variables like '%keyring%';
| Variable_name     | Value                          |
| keyring_file_data | /var/lib/mysql-keyring/keyring |
1 row in set (0.00 sec)

 mysql> CREATE TABLE innodb_tde (id int(11), c_desc varchar(100), c_comments text ) ENCRYPTION='Y';
Query OK, 0 rows affected (0.01 sec)

| test         | innodb_tde | ENCRYPTION="Y" |
1 row in set (0.01 sec)

mysql> INSERT INTO innodb_tde values (1, 'test tde' , 'innodb tde testing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from innodb_tde;
| id   | c_desc   | c_comments         |
|    1 | test tde | innodb tde testing |
1 row in set (0.01 sec)</pre>
# Disable - Enable ENCRYPTION from table

mysql> ALTER TABLE innodb_tde ENCRYPTION='N';
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE innodb_tde ENCRYPTION='Y';
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from innodb_tde;
| id | c_desc | c_comments |
| 1 | test tde | innodb tde testing |
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.01 sec)

mysql> select * from innodb_tde;
| id | c_desc | c_comments |
| 1 | test tde | innodb tde testing |
1 row in set (0.00 sec)

Refer MySQL doc for more: