MySQL tablespaces: new InnoDB feature in 5.7 to spread I/O
Table of contents
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)
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)
This tablespaces feature looks promising on the paper even if I’m expecting much more from it…



