转载

MySQL 5.7 新特性 共享临时表空间及临时表改进



〇 前言:

在MySQL 5.6引用了独立undo tablespace之后,MySQL 5.7在temporary tablespace上做了改进。
已经实现将temporary tablespace从ibdata(系统表空间文件)中分离。
并且可以重启重置大小,避免出现像ibdata难以释放的问题。

下面所有的讨论只针对InnoDB,并且指定了innodb_file_per_table,所用版本为MySQL 5.7.x


〇 新特性 · 共享临时表空间(shared temporary tablespace):

共享临时表空间出现于MySQL 5.7.1,为的是将临时表空间从系统表空间(system tablespace)文件中独立出来。该共享临时表空间用于存储非压缩InnoDB临时表(non-compressed InnoDB temporary tables)、关系对象(related objects)、回滚段(rollback segment)等数据。更多信息可以参考【MySQL 5.7 Reference Manual 8.4.4 Internal Temporary Table Use in MySQL

因为存放的数据特殊性,不会参与crash recovery,因此无需记录redo log。


该共享临时表空间默认大小为12MB。在实例关闭之后,将会被删除。在实例启动时则会被创建。

默认的,该共享临时表空间存放在innodb_data_home_dir中的ibtmp1里,而innodb_data_home_dir默认为datadir。
所以一般该ibtmp1存放在datadir下,显然,其路径与共享表空间的路径一样,取决于innodb_data_home_dir。



新增参数innodb_temp_data_file_path,通过修改其值可以将该共享临时表空间的文件名,扩展大小做修改。


比如在配置文件中加上innodb_temp_data_file_path = temp_tablespace:64M:autoextend
那么在启动实例之后,会生成一个大小为64MB的temp_tablespace文件
-rw-r----- 1 root root   67108864 Jun 20 17:29 temp_tablespace

该参数默认出现于5.7.1,静态,默认值为ibtmp1:12M:autoextend。


〇 新特性 · InnoDB临时表统计信息优化

因为临时表特性,是无法在SHOW TABLES;与通过information_schema.TABLES查询到其元数据信息的。
老版本可能只能通过一些比较麻烦的方法来查看:
比如SHOW CREATE TABLE tmp_a/G

5.7版本之后,在I_S里增加了一个表来统计该表的元数据信息INNODB_TEMP_TABLE_INFO。
可以通过I_S来查看该表的定义:
    
  1. SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO;
  2. +----------+---------------+--------+-------+----------------------+---------------+
  3. | TABLE_ID | NAME          | N_COLS | SPACE  | PER_TABLE_TABLESPACE | IS_COMPRESSED |
  4. +----------+---------------+--------+-------+----------------------+---------------+
  5. | 68       | #sql2b79_35_0 | 4      | 37    | FALSE                | FALSE         |
  6. +----------+---------------+--------+-------+----------------------+---------------+
  7. 1 row in set (0.00 sec)

字段介绍:
TABLE_ID:表id
NAME:表名,这个名字对应的表结构为$NAME.frm,若该表为压缩临时表,对应的数据文件为$NAME.ibd,反之则无。
N_COLS:列的数量,1个被我显示创建的列,其他3个为InnoDB的隐藏列(DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR)
SPACE:临时表的表空间id,总是非0,并且随实例重启动态变化,
PER_TABLE_TABLESPACE:如果为TRUE,则表明该临时表有自己的临时表空间(有自己的ibd文件),如果为FALSE,则表明该临时表用共享表空间。
IS_COMPRESSED:如果为TRUE,则表明该表被压缩,反之则未压缩。


〇 新特性 · innodb_tmpdir

出现在5.7.11以后的版本,用于在做某些Online DDL时存放临时数据。
innodb_tmpdir的值覆盖tmpdir,此特性只针对于Online DDL生效。


〇 共享临时表空间与tmpdir对比:

通过CREATE TEMPORARY TABLE ... 创建的表,该表定义会放在tmpdir下,默认为/tmp

tmpdir不是个新参数,一般也不需要指定,默认值为/tmp,此处还是提及并与共享临时表空间做一个对比。
tmpdir参数用于指定临时文件(temporary files)和临时表(temporary tables)的存放目录。
可以设定为一个集合并做轮询调度(用:分割),如果要用,建议指定多个磁盘目录以提高性能。

此外,对于显式创建的临时表(create temporary table):
与共享临时表空间不同的是,tmpdir存储的是compressed InnoDB temporary tables临时独立表空间

以下做一个测试,验证一下:

参数检查:

  1. SELECT @@innodb_temp_data_file_path, @@innodb_file_per_table, @@tmpdir, @@innodb_data_home_dir;
  2. +--------------------------------+-------------------------+----------+------------------------+
  3. | @@innodb_temp_data_file_path    | @@innodb_file_per_table | @@tmpdir  | @@innodb_data_home_dir |
  4. +--------------------------------+-------------------------+----------+------------------------+
  5. | ibtmp1:12M:autoextend          | 1                       | /tmp     | NULL                     |
  6. +--------------------------------+-------------------------+----------+------------------------+
  7. 1 row in set (0.00 sec)

先创建两张临时表,引擎均为默认的InnoDB,其中第一张指定行格式为COMRESSED,第二张不压缩:

  1. root@localhost [test]> CREATE TEMPORARY TABLE compress_table (id int, name char(255)) ROW_FORMAT=COMPRESSED;
  2. Query OK, 0 rows affected (0.02 sec)

  3. root@localhost [test]> CREATE TEMPORARY TABLE uncompress_table (id int, name char(255)) ;
  4. Query OK, 0 rows affected (0.00 sec)

  5. root@localhost [test]> SHOW CREATE TABLE compress_table/G
  6. *************************** 1. row ***************************
  7.        Table: compress_table
  8. Create Table: CREATE TEMPORARY TABLE `compress_table` (
  9.   `id` int(11) DEFAULT NULL,
  10.   `name` char(255) DEFAULT NULL
  11. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
  12. 1 row in set (0.00 sec)

  13. root@localhost [test]> SHOW CREATE TABLE uncompress_table/G
  14. *************************** 1. row ***************************
  15.        Table: uncompress_table
  16. Create Table: CREATE TEMPORARY TABLE `uncompress_table` (
  17.   `id` int(11) DEFAULT NULL,
  18.   `name` char(255) DEFAULT NULL
  19. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  20. 1 row in set (0.00 sec)

检查一下两张临时表的表定义:

  1. root@localhost [test]> SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO;
  2. +----------+-------------+--------+-------+----------------------+---------------+
  3. | TABLE_ID | NAME        | N_COLS | SPACE  | PER_TABLE_TABLESPACE | IS_COMPRESSED |
  4. +----------+-------------+--------+-------+----------------------+---------------+
  5. | 73       | #sqlb48_3_1 | 5      | 58    | FALSE                | FALSE         |
  6. | 72       | #sqlb48_3_0 | 5      | 59    | TRUE                 | TRUE          |
  7. +----------+-------------+--------+-------+----------------------+---------------+
  8. 2 rows in set (0.00 sec)

根据TABLE_ID和IS_COMPRESSED和PER_TABLE_TABLESPACE参数
可得知,#sqlb48_3_0为compress_table,#sqlb48_3_1为uncompress_table

创建好了之后,检查/tmp目录,也就是tmpdir。

  1. # ll /tmp/
  2. total 88
  3. -rw-r----- 1 root root 8586 Jun 20 16:38 #sqlb48_3_0.frm
  4. -rw-r----- 1 root root 65536 Jun 20 16:38 #sqlb48_3_0.ibd
  5. -rw-r----- 1 root root 8586 Jun 20 16:39 #sqlb48_3_1.frm
  6. -rw-------. 1 root root 0 Jan 3 2014 yum.log

可以发现,两张显式创建的临时表的【表定义文件】都被放到了tmpdir下。
此外,#sqlb48_3_0也就是IS_COMPRESSED为TRUE的那张压缩表,ibd文件也放在了tmpdir文件中。

那么理论上,#sqlb48_3_1这张未压缩的表的数据放到了ibtmp1中,也就是放到了共享临时表空间中。

简单验证一下,验证思路为两张表插入大量数据。
并分别检查ibtmp1文件和#sqlb48_3_0.ibd文件的大小变化:

对compress_table表:

  1. root@localhost [test]> INSERT INTO compress_table SELECT id, name FROM a limit 50000;
  2. Query OK, 50000 rows affected (1.20 sec)
  3. Records: 50000 Duplicates: 0 Warnings: 0
  4. (a表为一个测试数据用表)

  5. root@localhost [test]> /! ls -l /tmp
  6. -rw-r-----   1 root root 11534336 Jun 20 16:54 #sqlb48_3_0.ibd

  7. root@localhost [test]> INSERT INTO compress_table SELECT id, name FROM a limit 20000; 
  8. Query OK, 20000 rows affected (0.53 sec)
  9. Records: 20000  Duplicates: 0  Warnings: 0
  10. (a表为一个测试数据用表)

  11. root@localhost [test]> /! ls -l /tmp/*.ibd
  12. -rw-r-----  1 root root 14680064 Jun 20 16:55 #sqlb48_3_0.ibd

可以发现,针对压缩的InnoDB临时表,其数据放在tmpdir下的ibd文件中


再简单测试一下非压缩的InnoDB临时表:

  1. root@localhost [test]> /! ls -l /data/mysql-data/mysql57-3357/datadir/ibtmp1
  2. -rw-r----- 1 root root 12582912 Jun 20 16:57 /data/mysql-data/mysql57-3357/datadir/ibtmp1

  3. root@localhost [test]> INSERT INTO uncompress_table SELECT id, name FROM a limit 50000;
  4. Query OK, 50000 rows affected (0.53 sec)
  5. Records: 50000 Duplicates: 0 Warnings: 0

  6. root@localhost [test]> /! ls -l /data/mysql-data/mysql57-3357/datadir/ibtmp1
  7. -rw-r----- 1 root root 79691776 Jun 20 17:02 /data/mysql-data/mysql57-3357/datadir/ibtmp1

显然,非压缩的InnoDB临时表将数据存放在了共享临时表空间。

tmpdir下的东西和共享临时表空间最大的共同点以及特性就是,实例关闭之后,将会被删除。


〇 slave_load_tmpdir

该参数也不是5.7的新伙计,默认值取决于tmpdir的参数。
用于存放slave上产生的特殊的临时文件:
在master上出现LOAD DATA INFILE ... 时,被记录到binlog并发送给slave,在SQL thread从relaylog提取数据时,写入指定的目录下,然后执行LOAD DATA LOCAL INFILE ...,结束之后则会删掉这个文件。
增加这个参数是为了复制的可靠性和数据一致性。
如果默认放在tmpdir下,如果此时遭遇重启,文件丢失,则会导致复制失败。
如果master有使用这样的语句,建议将该目录指定在基于可靠存储设备上。


〇 可能遇到的问题:

MySQL 5.7.6以后,开始支持32KB和64KB的page size,若将page size修改为32或者64KB,则不能使用ROW_FORMAT=COMPRESSED,该行格式能支持的最大page size为16KB。

若要保证ROW_FORMAT=COMPRESSED生效,innodb_file_format必须设置为Barracuda。



〇 参考文档:

MySQL 5.7 Reference Manual 5.1.3 Server Option and Variable Reference
MySQL 5.7 Reference Manual 14.4.12 Temporary Tablespace
MySQL 5.7 Reference Manual 14.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Information Table






正文到此结束
Loading...