MySQL tablespaces: new InnoDB feature in 5.7 to spread I/O

Preamble

A new MySQL 5.7 has kept my attention while I attended the high level presentation: MySQL tablespaces. Yeaahh sounds like the nice flexibility we have with Oracle database…

I have always rated this InnoDB datafiles a bit cumbersome and limited to spread I/Os on multiple disks so interested to see what they have done in this area…

Testing has been done on Oracle Enterprise Linux 7.2 with MySQL Community edition 5.7.11 64 bits.

Installation and legacy situation

First noticeable change in 5.7.x is the deprecation of mysql_install_db initialization script that has been moved to mysqld. In order to inherit all InnoDB parameters I have set in my my.cnf file:

[mysql]
# CLIENT #
port                           = 3322
socket                         = /mysql/software/mysql01/conf/mysql01.sock
 
 
[mysqld]
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /mysql/software/mysql01/conf/mysql01.sock
pid-file                       = /mysql/software/mysql01/conf/mysql01.pid
port                           = 3322
 
# MyISAM #
key-buffer-size                = 32M
 
# DATA STORAGE #
datadir                        = /mysql/data01/mysql01/
 
# BINARY LOGGING #
log-bin                        = /mysql/logs/mysql01/mysql-bin
expire-logs-days               = 14
server-id                      = 1
 
# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 16M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = OFF
innodb-buffer-pool-size        = 100M
innodb_data_home_dir           =
innodb_data_file_path          = /mysql/data01/mysql01/ibdata1:10M:autoextend:max:100M
innodb_autoextend_increment    = 10
 
# LOGGING #
log-error                      = /mysql/dump/mysql01/mysql-error.log
log-queries-not-using-indexes  = ON
slow-query-log                 = ON
slow-query-log-file            = /mysql/dump/mysql01/mysql-slow.log

And I have used below command:

[mysql@server1 bin]$ ./mysqld --defaults-file=/mysql/software/mysql01/conf/my.cnf --initialize --user=mysql --datadir=/mysql/data01/mysql01

With 5.7.4 and upper, root account is created with a default password that you can find in file defined by log-error parameter. The first action is to change it with:

mysql> alter user 'root'@'localhost' identified by 'secure_password';
Query OK, 0 rows affected (0.00 sec)

I also create an account for myself to be able to connect from my desktop client and a test database:

mysql> grant all on *.* to 'yjaquier'@'%' identified by 'secure_password' with grant option;
Query OK, 0 rows affected, 1 warning (0.06 sec)
 
mysql> create database test character set utf32 collate utf32_general_ci;
Query OK, 1 row affected (0.00 sec)

The only flexibility you have with what is called InnoDB system tablespace is to define a directory and a datafile with a configuration like:

innodb_data_home_dir           =
innodb_data_file_path          = /mysql/data01/mysql01/ibdata1:10M:autoextend:max:100M
innodb_autoextend_increment    = 10

You can spread I/Os using a second datafile, only last datafile can be in autoextend mode:

innodb_data_home_dir           =
innodb_data_file_path          = /mysql/data01/mysql01/ibdata1:20M;/mysql/data02/mysql01/ibdata2:10M:autoextend:max:100M
innodb_autoextend_increment    = 10

Issue is that you do not control where you tables will be… Means not as flexible as tablespaces where you specify destination tablespaces at table creation.

No need to say that innodb_file_per_table parameter let you create an InnoDB datafile per table. Even more cumbersome to manage in my opinion as everything will be store in database directory (inside datadir).

MySQL tablespaces

In MySQL 5.7 Oracle has introduced tablespace logical space management like in Oracle database.

My InnoDB system tablespace is in /mysql/data01/mysql01 and I will start by creation of an InnoDB general tablespace in /mysql/data02/mysql01. Default storage engine is already InnoDB so no need to specify it and tablespace will not be able to store compressed table as FILE_BLOCK_SIZE will be equal to innodb_page_size:

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)
 
mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

Strangely filename extension must be .ibd:

mysql> create tablespace tbs1 add datafile '/mysql/data02/mysql01/tbs101.ibd';
Query OK, 0 rows affected (0.01 sec)
 
mysql> \! ls -l /mysql/data02/mysql01/tbs101.ibd
-rw-r-----. 1 mysql dba 65536 Feb 12 14:33 /mysql/data02/mysql01/tbs101.ibd

As you can see we cannot choose the size and there is no autoextend feature, so I suppose it is activated by default and cannot be inactivated…

As stated in documentation INFORMATION_SCHEMA.TABLESPACES is not the place to look for information. You have to use INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES, INFORMATION_SCHEMA.INNODB_SYS_DATAFILES and as of MySQL 5.7.8 INFORMATION_SCHEMA.FILES.

mysql> select * from information_schema.INNODB_SYS_TABLESPACES where name='tbs1';
+-------+------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+
| SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE            | ALLOCATED_SIZE |
+-------+------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+
|    27 | tbs1 | 2048 | Any         | Any        |     16384 |             0 | General    |             0 | 18446744073709551615 |              2 |
+-------+------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+
1 row in set (0.00 sec)
 
mysql> select * from information_schema.INNODB_SYS_DATAFILES where space=27;
+-------+----------------------------------+
| SPACE | PATH                             |
+-------+----------------------------------+
|    27 | /mysql/data02/mysql01/tbs101.ibd |
+-------+----------------------------------+
1 row in set (0.00 sec)
 
mysql> select * from information_schema.FILES where file_id=27\G
*************************** 1. row ***************************
             FILE_ID: 27
           FILE_NAME: /mysql/data02/mysql01/tbs101.ibd
           FILE_TYPE: TABLESPACE
     TABLESPACE_NAME: tbs1
       TABLE_CATALOG:
        TABLE_SCHEMA: NULL
          TABLE_NAME: NULL
  LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
              ENGINE: InnoDB
       FULLTEXT_KEYS: NULL
        DELETED_ROWS: NULL
        UPDATE_COUNT: NULL
        FREE_EXTENTS: 1
       TOTAL_EXTENTS: 16
         EXTENT_SIZE: 1048576
        INITIAL_SIZE: 65536
        MAXIMUM_SIZE: NULL
     AUTOEXTEND_SIZE: 1048576
       CREATION_TIME: NULL
    LAST_UPDATE_TIME: NULL
    LAST_ACCESS_TIME: NULL
        RECOVER_TIME: NULL
 TRANSACTION_COUNTER: NULL
             VERSION: NULL
          ROW_FORMAT: NULL
          TABLE_ROWS: NULL
      AVG_ROW_LENGTH: NULL
         DATA_LENGTH: NULL
     MAX_DATA_LENGTH: NULL
        INDEX_LENGTH: NULL
           DATA_FREE: 4194304
         CREATE_TIME: NULL
         UPDATE_TIME: NULL
          CHECK_TIME: NULL
            CHECKSUM: NULL
              STATUS: NORMAL
               EXTRA: NULL
1 row in set (0.00 sec)

mysql> select * from information_schema.INNODB_SYS_TABLESPACES where name='tbs1'; +-------+------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+ | SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | +-------+------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+ | 27 | tbs1 | 2048 | Any | Any | 16384 | 0 | General | 0 | 18446744073709551615 | 2 | +-------+------+------+-------------+------------+-----------+---------------+------------+---------------+----------------------+----------------+ 1 row in set (0.00 sec)mysql> select * from information_schema.INNODB_SYS_DATAFILES where space=27; +-------+----------------------------------+ | SPACE | PATH | +-------+----------------------------------+ | 27 | /mysql/data02/mysql01/tbs101.ibd | +-------+----------------------------------+ 1 row in set (0.00 sec)mysql> select * from information_schema.FILES where file_id=27\G *************************** 1. row *************************** FILE_ID: 27 FILE_NAME: /mysql/data02/mysql01/tbs101.ibd FILE_TYPE: TABLESPACE TABLESPACE_NAME: tbs1 TABLE_CATALOG: TABLE_SCHEMA: NULL TABLE_NAME: NULL LOGFILE_GROUP_NAME: NULL LOGFILE_GROUP_NUMBER: NULL ENGINE: InnoDB FULLTEXT_KEYS: NULL DELETED_ROWS: NULL UPDATE_COUNT: NULL FREE_EXTENTS: 1 TOTAL_EXTENTS: 16 EXTENT_SIZE: 1048576 INITIAL_SIZE: 65536 MAXIMUM_SIZE: NULL AUTOEXTEND_SIZE: 1048576 CREATION_TIME: NULL LAST_UPDATE_TIME: NULL LAST_ACCESS_TIME: NULL RECOVER_TIME: NULL TRANSACTION_COUNTER: NULL VERSION: NULL ROW_FORMAT: NULL TABLE_ROWS: NULL AVG_ROW_LENGTH: NULL DATA_LENGTH: NULL MAX_DATA_LENGTH: NULL INDEX_LENGTH: NULL DATA_FREE: 4194304 CREATE_TIME: NULL UPDATE_TIME: NULL CHECK_TIME: NULL CHECKSUM: NULL STATUS: NORMAL EXTRA: NULL 1 row in set (0.00 sec)

We can see that initial size is 65536 (proven by ls -l command) and extend size is 1MB, autoextend see to be unlimited or at least limited by your underlining filesystem…

I create a table inside:

CREATE TABLE customers (
id mediumint,
firstname varchar(255) default NULL,
lastname varchar(255) default NULL
)
tablespace tbs1;

And same as test table created for Memcached testing I load it with 100,000 rows using script provided by generatedata.com… The tablespace datafile ends up with a size of 16MB.

To know which tables have been created in your tablespace use below command (27 is my tablespace identifier found in INNODB_SYS_TABLESPACES:

mysql> select * from information_schema.INNODB_SYS_TABLES where space='27';
+----------+----------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+----------------+------+--------+-------+-------------+------------+---------------+------------+
|       44 | test/customers |  161 |      6 |    27 | Barracuda   | Dynamic    |             0 | General    |
+----------+----------------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.00 sec)

There is no ALTER TABLESPACE command to add multiple datafile in multiple directories and you also cannot act on datafiles… They have anyway set a check for non-empty tablespace drop:

mysql> drop tablespace tbs1;
ERROR 1529 (HY000): Failed to drop TABLESPACE tbs1
 
mysql> drop table test.customers;
Query OK, 0 rows affected (0.14 sec)
 
mysql> drop tablespace tbs1;
Query OK, 0 rows affected (0.04 sec)