Instructions for mysql5.7 General tablespace

1, Advantages of General tablespace

1) Similar to system table space, it can store multiple tables under multiple schema s.
2) Compared with file per table tablespace, General tablespace has potential advantages of memory. In this way, the metadata of table space is stored in memory, which can reduce memory consumption.
3) General tablespace can store data files on other disks. The advantage of this is that, for example, I need to put several hotspot tables on a faster storage disk, so that I can create a general tablespace on the faster storage disk and put the hotspot tables in the past.
4) General supports all row formats and related features.
5) Data tables can move back and forth between the general tablespace and the per file table tablespace. Similar to move table of Oracle database.

2, Syntax for creating a general tablespace

CREATE TABLESPACE tablespace_name ADD DATAFILE 'file_name' [FILE_BLOCK_SIZE = value] [ENGINE [=] engine_name]

be careful:
In order to avoid data directory conflicts, it is not supported to establish a table space under the data directory subdirectory. Because the subdirectory of datadir will be considered database by mysql.
But General tablespace can be built under datadir. After the General tablespace is established in a non datadir directory, an. isl file will be generated in the data directory, which contains the detailed path.

for example
Create a general tablespace under datadir. The page size is 8k. The default engine innodb:

mysql> create tablespace tbs01 add datafile 'tbs01.ibd' file_block_size=8192 engine=innodb; Query OK, 0 rows affected (0.02 sec)

--tbs01.ibd file will be generated for tbs01 in datadir

Create a general tablespace in a non datadir location:

mysql> create tablespace tbs02 add datafile '/u01/mysql/3306/general_tbs/tbs02.ibd' engine=innodb; Query OK, 0 rows affected (0.01 sec)

--tbs02.isl file will be generated for tbs02 in datadir

isl is actually the path:

# cat tbs02.isl /u01/mysql/3306/general_tbs/tbs02.ibd

The InnoDB sys tablespaces table under information schema has metadata information of these tablespaces:

mysql> select * from information_schema.INNODB_SYS_TABLESPACES where NAME like 'tbs%'; +-------+-------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ | SPACE | NAME | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | +-------+-------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ | 41 | tbs01 | 2089 | Barracuda | Compressed | 16384 | 8192 | General | 4096 | 65536 | 53248 | | 42 | tbs02 | 2048 | Any | Any | 16384 | 0 | General | 4096 | 131072 | 135168 | +-------+-------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+ 2 rows in set (0.00 sec)

3, Use of General tablespace

be careful:
1) tbs01 specifies file block size = 8K when creating tablespace; tbs02 does not specify file block size when creating tablespace;
If file block size is used when creating a table space, only the compressed table can be stored in the table space. Otherwise, an error will be reported when creating a non compressed table. That is, compressed and uncompressed tables cannot be in the same tablespace.
2) When the table uses the General table space, there are only. frm files and no ibd files in the corresponding database directory. When the table is moved to file per table, the. ibd file appears;

1. Creating tables in the General table space

To build a non compressed table on tbs01:

mysql> create table tt(a int) tablespace=tbs01; ERROR 1478 (HY000): InnoDB: Tablespace `tbs01` uses block size 8192 and cannot contain a table with physical page size 16384

Compression table built on tbs01:

mysql> create table tt(a int) tablespace=tbs01 ROW_FORMAT=COMPRESSED; Query OK, 0 rows affected (0.02 sec)

Similarly, compression tables cannot be built in tbs02:

mysql> create table tt01(a int) tablespace=tbs02 ROW_FORMAT=COMPRESSED; ERROR 1478 (HY000): InnoDB: Tablespace `tbs02` cannot contain a COMPRESSED table

Only non compressed tables can be created:

mysql> create table tt01(a int) tablespace=tbs02; Query OK, 0 rows affected (0.01 sec)

2. Move table

Move the table in the General tablespace to the file per table tablespace:

mysql> alter table tt tablespace=innodb_file_per_table; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table tt01 tablespace=innodb_file_per_table; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0

Move a 2.5 million table from file_per table tablespace to general tablespace

mysql> alter table test02 tablespace=tbs02; Query OK, 0 rows affected ( 9.41 sec ) Records: 0 Duplicates: 0 Warnings: 0

It took more than 9 seconds to move the large watch, which means it still takes a lot of time.

Attach, move table to SYSTEM tablespace
ALTER TABLE test01 TABLESPACE innodb_system;

Delete the tablespace. Note that deletion fails when there are tables in the tablespace.

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

4, Limitations of General tablespace

1) General tablespace does not support temporary tables.
2) Similar to system tablespaces, truncate or drop table simply marks spaces as unavailable, but does not return them to the operating system.
3) MySQL 5.7.24 and later does not support partition in general tablespace.
Alter table... Discard tablespace and alter table... Import tablespace does not support general tablespace.

~
~
complete!

Add new comment

0 comments