MySQL 5.7 New Support--------universal table space Combat

1. Background

* A common tablespace is a shared innodb table space.

* similar to the system table space, the general tablespace is a shared table space that can store data from multiple tables

* The General tablespace has a potential memory advantage over the table space of the file table.

* MySQL saves tablespace metadata to the life cycle of a table space.    In fewer general table spaces, more than one table has less memory than the same number of tables in a separate file tablespace table space for table space metadata.

* The general tablespace data file may be placed in a directory relative to the MySQL data directory, which gives you a lot of file table space data file and storage management functions. As with the table space in the file table, the ability to place data files outside the MySQL data directory allows you to manage the performance of key tables individually, set up raid or DRBD for a specific table, or bind a table to a specific disk.

* MySQL 5.7 starts to support the universal Table space management feature.

2. MySQL Environment

Welcome to the mysql monitor.  commands end with ; or \ g.your mysql connection id is 4server version: 5.7.18 mysql  community server  (GPL) copyright  (c)  2000, 2017, oracle and/or its  affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or  Itsaffiliates. other names may be trademarks of their respectiveowners . type  ' help; '  or  ' \h '  for help. Type  ' \c '  to clear the current input  statement.mysql> show variables like  ' version '; +---------------+--------+|  variable_name | value  |+---------------+--------+| version        | 5.7.18 |+---------------+--------+1 row in set  (0.01 sec) mysql> show variables like  ' DataDir '; +-- -------------+-------------------+| variable_name | value              |+---------------+-------------------+| datadir        | /data/mysql_data/ |+---------------+-------------------+1 row  in set  (0.04 SEC)

3. Create a universal table space

* CREATE TABLE space file storage directory

[Email protected] mytest]# mkdir-v/mysql_general_datamkdir:created directory '/mysql_general_data '

* View mysqld running users

[[Email protected] mytest]# PS aux | grep mysqld |        Grep-v greproot 1468 0.0 0.0 110400 1532? S 16:00 0:00/bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/data/mysql_data--PID-FILE=/DATA/MYSQL_DATA/MYSQL.P      Idmysql 1614 0.1 5.0 1309380 196656? Sl 16:00 0:06/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/data/mysql_data--plugin-dir=/usr/ Local/mysql/lib/plugin--user=mysql--log-error=/data/mysql_data/error.log--pid-file=/data/mysql_data/mysql.pid

* Modify tablespace file to store directory of user and group for MySQL running user [This step must]

[Email protected] mytest]# chown-v Mysql.mysql/mysql_general_data changed ownership of '/mysql_general_data ' to mysql:m Ysql

* create a common table space

ADD datafile: Specify a common tablespace file storage path

File_block_size: Specify file block size, recommended for innodb_page_size parameter size

Engine: Specify the storage engine

mysql> CREATE tablespace ts1 ADD datafile '/mysql_general_data/ts1.ibd ' file_block_size=16384 ENGINE=InnoDB; Query OK, 0 rows affected (0.06 sec) mysql> Show variables like ' innodb_page_size '; +------------------+-------+| variable_name | Value |+------------------+-------+| Innodb_page_size | 16384 |+------------------+-------+1 row in Set (0.02 sec)

* View Universal tablespace files

mysql> system ls-l/mysql_general_data;total 64-rw-r-----1 mysql mysql 65536 Jul 5 17:15 ts1.ibd

4. Test the Universal tablespace file

* Create a table using a common tablespace as a data store

Mysql> CREATE TABLE test_general (id BIGINT PRIMARY KEY not NULL auto_increment, name VARCHAR NULL) Engine=innodb tablespace=ts1 DEFAULT charset=utf8mb4; Query OK, 0 rows affected (0.04 sec)

* View table information

mysql> show create table test_general;+--------------+--------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------+| table         | Create Table                                                                                                                                                                                                |+--------------+--------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------+| test_general | create table  ' test_general '   (   ' id '   bigint  NOT NULL AUTO_INCREMENT,   ' name '  varchar ( not null),   PRIMARY KEY  (' id '))  /*!50100 TABLESPACE  ' Ts1 '  */ engine=innodb  default charset=utf8mb4 |+--------------+------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -----+1 row in set  (0.06 SEC)

* View Table file

Mysql> Select Database (); +------------+| Database () |+------------+|  MyTest |+------------+1 row in Set (0.01 sec) mysql> system ls-l/data/mysql_data/mytest;total 16-rw-r-----1 mysql MySQL 5 16:30 db.opt-rw-r-----1 mysql mysql 8586 Jul 5 17:19 test_general.frm

5. Deleting a tablespace file

* directly deleted when a table is occupied

mysql> drop tablespace ts1; ERROR 1529 (HY000): Failed to drop tablespace ts1

* Delete the Occupation table first, then delete

mysql> drop table test_general; Query OK, 0 rows affected (0.04 sec) mysql> drop tablespace ts1; Query OK, 0 rows affected (0.03 sec)

6. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1945446

MySQL 5.7 New Support--------universal table space Combat