Transportable Tablespace Internals

Transportable Tablespace Internals

以下信息描述了InnoDB的传输表空间复制过程的内部原理和error log中输出的信息

当在目标端执行ALTER TABLE ... DISCARD TABLESPACE命令时:

  • The table is locked in X mode.
  • 表空间会与表分离.The tablespace is detached from the table.

当在源端执行FLUSH TABLES ... FOR EXPORT命令时:

  • The table being flushed for export is locked in shared mode.
  • 清除协调程序线程已停止The purge coordinator thread is stopped.
  • 脏块会被写入磁盘
  • 表的元数据信息会被写入二进制文件.cfg中

此操作的预期错误日志消息:

1
2
3
4
2013-09-24T13:10:19.903526Z 2 [Note] InnoDB: Sync to disk of '"test"."t"' started.
2013-09-24T13:10:19.903586Z 2 [Note] InnoDB: Stopping purge
2013-09-24T13:10:19.903725Z 2 [Note] InnoDB: Writing table metadata to './test/t.cfg'
2013-09-24T13:10:19.904014Z 2 [Note] InnoDB: Table '"test"."t"' flushed to disk

当在源端执行UNLOCK TABLES命令时:

  • 二进制文件.cfg会被删除
  • The shared lock on the table or tables being imported is released ,并且清除协调程序线程会重启purge coordinator thread is restarted.

此操作的预期错误日志消息:

1
2
2013-09-24T13:10:21.181104Z 2 [Note] InnoDB: Deleting the meta-data file './test/t.cfg'
2013-09-24T13:10:21.181180Z 2 [Note] InnoDB: Resuming purge

当在目标端执行ALTER TABLE ... IMPORT TABLESPACE时,导入算法会执行如下操作:

  • 将检查每个表空间页是否损坏.
  • 每个页面上的空间ID和日志序列号(LSN)都会更新
  • 标志被验证,LSN被更新为头页.
  • Btree页面更新.
  • 页面状态设置为dirty,以便它将被写入磁盘.

此操作的预期错误日志消息:

1
2
3
4
5
6
2013-07-18 15:15:01 34960 [Note] InnoDB: Importing tablespace for table 'test/t' that was exported from host 'ubuntu'
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase I - Update all pages
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk - done!
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase III - Flush changes to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase IV - Flush complete

注意
您还可能会收到一个警告,表明丢弃了表空间(如果您丢弃了目标表的表空间)和一条消息,指出由于缺少.ibd文件而无法计算统计信息:

1
2
3
2013-07-18 15:14:38 34960 [Warning] InnoDB: Table "test"."t" tablespace is set as discarded.
2013-07-18 15:14:38 7f34d9a37700 InnoDB: cannot calculate statistics for table "test"."t" because the .ibd file is missing. For help, please refer to
http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html

Transportable Tablespace示例

Transportable Tablespace示例

http://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html

例1:将InnoDB表从一个服务器复制到另一个服务器

此过程演示如何将InnoDB表从正在运行的MySQL服务器实例复制到另一个正在运行的实例.经过一些小调整相同过程可用于在同一实例上执行完整表恢复.
1.source端

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
(mysql@localhost) [test]> show table status like 'dept'\G
*************************** 1. row ***************************
Name: dept
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2016-08-26 14:25:50
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
(mysql@localhost) [test]> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.01 sec)

2.target端

1
2
3
4
5
6
CREATE TABLE `dept` (
`deptno` int(11) NOT NULL,
`dname` varchar(14) DEFAULT NULL,
`loc` varchar(13) DEFAULT NULL,
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.在目标端discard tablespace.Before a tablespace can be imported, InnoDB must discard the tablespace that is attached to the receiving table

1
mysql> alter table dept discard tablespace;

阅读全文

Transportable Tablespaces

迁移File-Per-Table Tablespaces

Transportable Tablespace示例
Transportable Tablespace Internals
本节介绍如何将file-per-table tablespace从一个server迁移到另一个server,也称为可Transportable Tablespaces功能。 在MySQL 5.7.4之前,只支持非分区的InnoDB表。 从MySQL 5.7.4开始,还支持分区的InnoDB表和各个InnoDB表分区和子分区。

There are many reasons why you might copy an InnoDB file-per-table tablespace to a different database server:

  • 生成报告而不影响生产库
  • 为备库初始化数据
  • 用于数据恢复
  • 相对于mysqldump,传输表空间有个更快的速度
  • 将每个文件的表空间移动到具有更适合系统要求的存储介质的服务器.例如,您可能希望在SSD设备上拥有繁忙的表,或在大容量HDD设备上使用大型表.

限制和使用注意事项

  • 仅当innodb_file_per_table设置为ON(这是MySQL 5.6.6的默认设置)时,传输表空间功能才可以使用。 驻留在共享系统表空间中的表不能使用此功能。
  • 在传输过程中,只有只读操作可以执行
  • page size要相同.When importing a tablespace, the page size must match the page size of the importing instance.
  • Prior to MySQL 5.7.4, DISCARD TABLESPACE is not supported for partitioned tables meaning that transportable tablespaces is also unsupported. If you run ALTER TABLE … DISCARD TABLESPACE on a partitioned table, the following error is returned: ERROR 1031 (HY000): Table storage engine for ‘part’ doesn’t have this option. As of MySQL 5.7.4, ALTER TABLE … DISCARD TABLESPACE is supported for partitioned InnoDB tables, and ALTER TABLE … DISCARD PARTITION … TABLESPACE is supported for InnoDB table partitions.
  • 当foreign_key_checks设置为1时,对于父 - 子(主键 - 外键)关系的表空间不支持DISCARD TABLESPACE。在放弃父子表的表空间之前,请设置foreign_key_checks = 0。 分区的InnoDB表不支持外键。
  • ALTER TABLE … IMPORT TABLESPACE不会对导入的数据实施外键约束。 如果表之间存在外键约束,则应在相同(逻辑)时间点导出所有表。 分区的InnoDB表不支持外键。
  • ALTER TABLE … IMPORT TABLESPACE和ALTER TABLE … IMPORT PARTITION … TABLESPACE不需要.cfg元数据文件来导入表空间。 但是,在不使用.cfg文件导入时,不会执行元数据检查,并且将发出类似于以下内容的警告:
    1
    2
    3
    Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\
    test\t.cfg', will attempt to import without schema verification
    1 row in set (0.00 sec)

没有.cfg文件导入的能力可能更方便,当不需要模式不匹配。 此外,无法使用.cfg文件导入的能力在无法从.ibd文件收集元数据的崩溃恢复方案中很有用。

The ability to import without a .cfg file may be more convenient when no schema mismatches are expected. Additionally, the ability to import without a .cfg file could be useful in crash recovery scenarios in which metadata cannot be collected from an .ibd file.

  • 由于.cfg元数据文件限制,在为分区表导入表空间文件时,不会报告分区类型或分区定义差异的模式不匹配。但会报告列差异。
  • 在子分区表上运行ALTER TABLE … DISCARD PARTITION … TABLESPACE和ALTER TABLE … IMPORT PARTITION … TABLESPACE时,允许使用分区表和子分区表名。指定分区名称时,该分区的子分区将包括在操作中。

  • 在MySQL 5.6或更高版本中,如果两个服务器都具有GA(通用可用性)状态并且它们的版本在同一系列中,则从另一个服务器导入表空间文件是有效的。否则,该文件必须在导入它的服务器上创建。

  • 在复制方案中,在主节点和从节点上必须将innodb_file_per_table设置为ON。

  • 在Windows上,InnoDB以小写形式内部存储数据库,表空间和表名。要避免区分大小写操作系统(如Linux和UNIX)上的导入问题,请使用小写名称创建所有数据库,表空间和表。一种方便的方法是在创建数据库,表空间或表之前,在my.cnf或my.ini文件的[mysqld]节中添加以下行:

    阅读全文

调整InnoDB系统表空间大小

调整InnoDB系统表空间大小

本文介绍如何增大或缩小InnoDB system tablespace

增大InnoDB system tablespace

最简单的增大InnoDB system tablespace大小的方法是在一开始配置的时候就指定为自动扩展. 为innodb_data_file_path参数中的最后一个数据文件指定autoextend选项. InnoDB在空间不足时以64MB为单位自动增加该文件的大小. 可以通过设置innodb_autoextend_increment系统变量的值(以兆字节为单位)来更改增量大小.

您可以通过添加另一个数据文件来扩展系统表空间:

1.关闭MySQL
2.如果上一个数据文件是使用关键字autoextend定义的,则根据实际增长的大小将其定义更改为使用固定大小. 检查数据文件的大小,将其舍入到1024×1024字节(= 1MB)的最接近的倍数,并在innodb_data_file_path中显式指定舍入后的大小.
3.将新的数据文件添加到innodb_data_file_path的末尾,可以指定该文件为自动扩展. 注意,只能将innodb_data_file_path中的最后一个数据文件指定为自动扩展.
4.启动MySQL

实际例子:

初始只有一个ibdata1,现在我们想增加一个数据文件

1
2
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

假设ibdata1此时已经增长到988M,那么修改配置为

1
2
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

启动MySQL后,ibdata2会被初始化

1
2
3
2017-08-11T10:27:06.014446+08:00 0 [Note] InnoDB: Need to create a new innodb_system data file 'ibdata2'.
2017-08-11T10:27:06.014567+08:00 0 [Note] InnoDB: Setting file './ibdata2' size to 50 MB. Physically writing the file full; Please wait ...
2017-08-11T10:27:06.182464+08:00 0 [Note] InnoDB: File './ibdata2' size is now 50 MB.

缩小InnoDB system tablespace

您不能从系统表空间中删除数据文件. 要减少系统表空间大小,请使用以下过程:

1.使用mysqldump来转储所有的InnoDB表,包括位于MySQL数据库中的InnoDB表.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
+---------------------------+
| TABLE_NAME |
+---------------------------+
| engine_cost |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
+---------------------------+

2.关闭MySQL
3.删除所有现有的表空间文件( .ibd),包括ibdata和ib_log文件. 不要忘记删除位于MySQL数据库中的表的 .ibd文件.
4.删除InnoDB表的任何.frm文件.
5.配置新的表空间.
6.重启MySQL
7.导入dump文件

Note
如果您的数据库仅使用InnoDB引擎,可能会更容易地转储所有数据库,停止服务器,删除所有数据库和InnoDB日志文件,重新启动服务器以及导入转储文件.

MySQL数据库设计规范

MySQL数据库设计规范

MySQL数据库与Oracle、sqlserver等数据库相比,有其内核上的优势与劣势。我们在使用MySQL数据库的时候需要遵循一定规范,扬长避短。本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范,从而为公司业务系统稳定、健康地运行提供保障。

数据库设计

以下所有规范会按照【高危】、【强制】、【建议】三个级别进行标注,遵守优先级从高到低。
对于不满足【高危】和【强制】两个级别的设计,DBA会强制打回要求修改。

库名

1.【强制】库的名称必须控制在32个字符以内,相关模块的表名与表名之间尽量提现join的关系,如user表和user_login表。
2.【建议】库的名称格式:业务系统名称子系统名,同一模块使用的表名尽量使用统一前缀。
**3.【强制】一般分库名称命名格式是“库通配名
编号”,编号从“0”开始递增,比如“wenda001” **
以时间进行分库的名称格式是“库通配名
时间”
4.【强制】创建数据库时必须显式指定字符集,并且字符集只能是utf8或者utf8mb4

创建数据库SQL举例:

1
Create database db1 default character set utf8;

表结构

1.【强制】表和列的名称必须控制在32个字符以内,表名只能使用字母、数字和下划线,一律小写。
2.【建议】表名要求模块名强相关,如师资系统采用”sz”作为前缀,渠道系统采用”qd”作为前缀等。
3.【强制】创建表时必须显式指定字符集为utf8或utf8mb4。
4.【强制】创建表时必须显式指定表存储引擎类型,如无特殊需求,一律为InnoDB。

当需使用除InnoDB以外的存储引擎时,必须通过DBA审核才能在生产环境中使用

因为Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库重要特性,为业界使用最多的MySQL存储引擎。而这是其他大多数存储引擎不具备的,因此首推InnoDB

阅读全文

数据分布不均匀走HASH JOIN导致的性能问题

这个案例是JAVA开发说一个存储过程跑的很慢,之后我跑这个过程,然后通过脚本抓出了慢的SQL

表大小

1
2
3
tb_user_channel --1W
tb_channel_info --1W
base_data_login_info 19W

就是这条SQL,跑完要7分钟。base_data_login_info本来是@db_link,但是我在本地建了一个同样的表发现还是7分钟左右,所以排除了可能是由于db_link造成问题的可能性

1
2
3
4
5
6
select
count(distinct a.user_name),count(distinct a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

看一下这个sql返回多少行,结果秒杀,瞬间就出结果了

1
2
3
4
5
6
select
count(*)
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

45122行
之后单独跑

1
2
3
4
5
6
select
count(distinct a.user_name),count( a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform


1
2
3
4
5
6
select
count( a.user_name),count(distinct a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

都是秒杀
单独count distinct user_name 或 invest_id 都很快 ,一起count distinct就很慢了
那么这时候其实已经可以通过改写SQL提示性能了,改写如下

阅读全文


Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#