MySQL8 表空间缓解磁盘压力
一 、背景
- 云上磁盘io饱和,为了缓解MySQL8压力,可以把指定表迁移到指定表空间目录,创建语法如下:
CREATE [UNDO] TABLESPACE tablespace_name
[ADD DATAFILE 'file_name']
[AUTOEXTEND_SIZE [=] value]
[FILE_BLOCK_SIZE = value]
[ENCRYPTION [=] {'Y' | 'N'}]
[ENGINE [=] engine_name]
- ADD DATAFILE:指定表空间的关联文件和路径,注意必须已.ibd结尾
- AUTOEXTEND_SIZE:8.0.13以后有用,当表空间满时扩容的大小,必须是4MB的倍数,最大为4G(8.0.24开始)。默认值为0,默认规则为:表空间小于1M,每次增加1页;表空间大于1M小于32M,每次增加1M;表空间大于32M,每次增加4M;
- FILE_BLOCK_SIZE:定义表空间数据文件的块大小。默认为innodb_page_size,如使用默认值则只能存储未压缩的行格式(COMPACT, REDUNDANT, and - DYNAMIC)。如存储压缩的表(ROW_FORMAT=COMPRESSED)则表的KEY_BLOCK_SIZE需等于FILE_BLOCK_SIZE/1024;
ENCRYPTION:是否静态加密
二、配置格外磁盘目录
- 查询可以增加建表空间的目录:
SELECT @@datadir,@@innodb_data_home_dir,@@innodb_directories;
- 默认没有增加,需要在my.cnf 增加新增磁盘的目录,我们这边以/data1,/data2 为新增磁盘
- 在[mysqld]新增如下配置
innodb_directories="/data1/mysql3306/data;/data2/mysql3306/data"
重启数据库
- 查询是否成功
三、创建例子
root@localhost [(none)]>CREATE TABLESPACE test ADD DATAFILE '/data1/mysql3306/data/test.ibd' ENGINE=INNODB;
Query OK, 0 rows affected (0.31 sec)
root@localhost [(none)]>create database test;
Query OK, 1 row affected (0.05 sec)
root@localhost [(none)]>use test
Database changed
root@localhost [test]> create table test (id int) tablespace=test;
Query OK, 0 rows affected (0.20 sec)
root@localhost [test]>create table test (id int ,name varchar(255)) tablespace=test;
Query OK, 0 rows affected (0.18 sec)
root@localhost [test]> select * from information_schema.innodb_tablespaces where name='test'\G
*************************** 1. row ***************************
SPACE: 431
NAME: test
FLAG: 18432
ROW_FORMAT: Any
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: General
FS_BLOCK_SIZE: 4096
FILE_SIZE: 131072
ALLOCATED_SIZE: 131072
AUTOEXTEND_SIZE: 0
SERVER_VERSION: 8.0.28
SPACE_VERSION: 1
ENCRYPTION: N
STATE: normal
1 row in set (0.38 sec)
root@localhost [test]>select * from information_schema.files where tablespace_name='test'\G
*************************** 1. row ***************************
FILE_ID: 431
FILE_NAME: /data1/mysql3306/data/test.ibd
FILE_TYPE: TABLESPACE
TABLESPACE_NAME: ts_test
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: 0
TOTAL_EXTENTS: 0
EXTENT_SIZE: 1048576
INITIAL_SIZE: 114688
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: 0
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
STATUS: NORMAL
EXTRA: NULL
1 row in set (0.00 sec)
- 至此,可以吧老表的数据迁移到新增的表空间中