MySQL Tablespaces

MySQL Tablespace

Theories - InnoDB Tablespace general knowledge

InnoDB tablespaces store data, metadata, buffered data, and logs on the file system.

1. Data tablespaces

• System tablespace
  • Stores metadata and buffers in system tablespace.
  • Data dictionary: Table, index, and column metadata
  • Undo log: Transactional rollback information
  • Change buffer : Changes to secondary index pages
  • Double Write buffer : Ensures crash-safe writes
  • Typically ibdata1, ibdata2
  • Check system tablespace :

Example 1 :

mysql> show variables where variable_name='innodb_data_file_path';

+-----------------------+------------------------+

| Variable_name | Value |

+-----------------------+------------------------+

| innodb_data_file_path | ibdata1:12M:autoextend |

+-----------------------+------------------------+

Indicates that one file called ibdata1, 12MB in size, and autoextending

Example 2 :

ibdata1:20M;/ext/ibdata2:10M:autoextend

Indicates that there are two files,

ibdata1 fixed at 20M and ibdata2 in /ext directory, 10MB, autoextending

• File-per-table tablespaces
  • Innodb_file_per_table specifies to store tables in their own tablespaces
  • CREATE TABLE fpt_table(a INT PRIMARY KEY, b CHAR(4)) TABLESPACE=Innodb_file_per_table;
• General tablespaces
  • Create a tablespace with datafile
  • CREATE TABLESPACE myts ADD DATAFILE ‘myts_data1.ibd’;
  • Add new table and assign to tablespace
  • CREATE TABLE gen_table(a INT PRIMARY KEY, b CHAR(4)) TABLESPACE=myts;
  • Move a table to tablespace.
  • ALTER TABLE fpt_table TABLESPACE=myts;
• Locating Tablespaces Outside Database Directory
  • Use data directory clause
  • CREATE TABLE gen_table(a INT PRIMARY KEY, b CHAR(4)) DATA DIRECTORY=’/datadir2’;
  • Create tablespace at specific directory and add datafile
  • CREATE TABLESPACE myts ADD DATAFILE ‘/datadir2/ext_ts_data1.ibd’;
  • DROP TABLESPACE myts;

2) Undo tablespaces

3) Temporary table tablespaces

• Uncompressed temporary tables
  • innodb_temp_data_file
  • In ibtmp1 data directory
  • Default value

mysql> show variables where variable_name='innodb_temp_data_file_path';

+----------------------------+-----------------------+

| Variable_name | Value |

+----------------------------+-----------------------+

| innodb_temp_data_file_path | ibtmp1:12M:autoextend |

+----------------------------+-----------------------+

• Compressed temporary tables
  • In file-per-table tablespaces in MySQL’s temporary directory

Tablespace related configurations :

MySQL InnoDB Configuration:

## DATA STORAGE ##

datadir=/var/lib/mysql

## InnoDB Configuration ##

innodb_file_per_table=1

# 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-log-files-in-group=3

innodb_log_file_size=100M

innodb_log_buffer_size=30M

#InnoDB file format

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

early-plugin-load=keyring_file.so

keyring_file_data=/var/lib/mysql-keyring/keyring

Tablespace Physical datafiles

Check datadirectory

mysql> show variables where variable_name='datadir';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| datadir | /var/lib/mysql/ |

+---------------+-----------------+

root@okprd5328:/var/lib/mysql# ls

auto.cnf ibdata1 my.cnf private_key.pem

ca-key.pem ib_logfile0 mysql public_key.pem

ca.pem ib_logfile1 mysqld scripts

client-cert.pem ibtmp1 mysql.sock server-cert.pem

client-key.pem IGNITE mysql.sock.lock server-key.pem

conts_data1.ibd itun_rest okptun5 sys

ib_buffer_pool log performance_schema

Database directories : mysql, performance_schema, sys

System tablespace : ibdata1

Temporary tablespace : ibtmp1

InnoDB redo logs: ib_logfile0, iblogfile1