[MySQL 5.6] MySQL 5.6 online ddl 使用、测试及关键函数栈

本文主要分为三个部分,第一部分是看文档时的笔记;第二部分使用sysbench简单测试了下性能损耗;第三部分阐述了关键函数栈,但未做深入

前言

Online DDL是MySQL 5.6的重要特性之一,特别是对于不可间断的互联网服务而言意义非凡。尽管我们已经通过工具来实现了在线DDL,但由于借助了触发器来获取增量数据,很难保证不会触发BUG,我们在5.1版本上广泛使用了内部开发的myddl,曾经触发了mysql6个以上的bug。

Innodb允许你通过设置LOCK=EXCLUSIVE | SHARED | DEFAULT/NONE 来进行完全阻塞的DDL、只阻塞DML不阻塞查询、以及完全在线DDL,这有助于你能够在性能和速度之间进行权衡

以下是从官方文档拷贝的一张关于Online ddl对于当前ddl操作的支持:

Operation In-Place? Copies Table? Allows Concurrent DML? Allows Concurrent Query? Notes
CREATE INDEX,ADD INDEX Yes* No* Yes Yes Some restrictions for FULLTEXT index; see next row. Currently, the operation is not in-place (that is, it copies the table) if the same index being created was also dropped by an earlier clause in the sameALTER TABLE statement.
ADD FULLTEXT INDEX Yes No* No Yes Creating the first FULLTEXT index for a table involves a table copy, unless there is a user-supplied FTS_DOC_ID column. Subsequent FULLTEXT indexes on the same table can be created in-place.
DROP INDEX Yes No Yes Yes
Set default value for a column Yes No Yes Yes Modifies .frm file only, not the data file.
Change auto-increment value for a column Yes No Yes Yes Modifies a value stored in memory, not the data file.
Add a foreign key constraint Yes* No* Yes Yes To avoid copying the table, disableforeign_key_checks during constraint creation.
Drop a foreign key constraint Yes No Yes Yes The foreign_key_checks option can be enabled or disabled.
Rename a column Yes* No* Yes* Yes To allow concurrent DML, keep the same data type and only change the column name.
Add a column Yes Yes Yes* Yes Concurrent DML is not allowed when adding an auto-increment column. Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Drop a column Yes Yes Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Reorder columns Yes Yes Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
ChangeROW_FORMATproperty Yes Yes Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
ChangeKEY_BLOCK_SIZEproperty Yes Yes Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Make columnNULL Yes Yes Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Make column NOT NULL Yes* Yes Yes Yes When SQL_MODE includesstrict_all_tables orstrict_all_tables, the operation fails if the column contains any nulls. Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Change data type of column No Yes No Yes
Add primary key Yes* Yes Yes Yes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. ALGORITHM=INPLACE is not allowed under certain conditions if columns have to be converted to NOT NULL. See Example 5.9, “Creating and Dropping the Primary Key”.
Drop primary keyand add another Yes Yes Yes Yes ALGORITHM=INPLACE is only allowed when you add a new primary key in the same ALTER TABLE; the data is reorganized substantially, so it is still an expensive operation.
Drop primary key No Yes No Yes Restrictions apply when you drop a primary key primary key without adding a new one in the same ALTER TABLEstatement.
Convert character set No Yes No Yes Rebuilds the table if the new character encoding is different.
Specify character set No Yes No Yes Rebuilds the table if the new character encoding is different.
Rebuild withFORCE option No Yes No Yes Acts like the ALGORITHM=COPY clause or the setting old_alter_table=1.

从官方提供的这个表格来看,还是有很多操作不支持完全的在线DDL,包括增加一个全文索引,修改列的数据类型,删除一个主键,修改表的字符集等。

但对于大多数我们日常常用的DDL而言,是可以做到在线DDL的。

通常情况下,可以使用默认的语法来进行在线DDL,但你也可以通过选项来改变DDL的行为,有两个选项

LOCK=

ALGORITHM=[INPLACE|COPY] 

官方文档给出了一些使用的例子

另外有一个参数 innodb_online_alter_log_max_size  需要注意,它表示在做在线DDL的过程中,并发DML产生的日志最大允许的大小。如果负载很高,这个值应该尽量的调大,否则可能导致DDL失败。


当对主键进行操作时,可以选择ALGORITHM=INPLACE 比设置为COPY更有效率,因为前者不会去记录UNDO LOG或者为其记录REDO LOG;二级索引被预先排序,能够进行有序的加载;change buffer也没有被使用到,因为没有涉及到对二级索引记录的随机插入操作

你可以通过观察执行完DDL后的输出: XX rows affected,来判断是IN-PLACE 还是COPY数据,为0的话就是in-place。

关于ONLINE DDL的具体使用,这里不做阐述,可以看看文档;这里只是简要阐述下其涉及到的函数堆栈

性能损耗

这里使用sysbench来测试,配置如下:

innodb_sort_buffer_size=2M

innodb_online_alter_log_max_size=2G  

sysbench command:

sysbench/sysbench –debug=off –test=sysbench/tests/db/update_index.lua  –oltp-tables-count=1  –oltp-point-selects=0 –oltp-table-size=1000000 –num-threads=20 –max-requests=10000000000 –max-time=7200 –oltp-auto-inc=off –mysql-engine-trx=yes –mysql-table-engine=innodb  –oltp-test-mod=complex –mysql-db=sbtest   –mysql-host=$HOST –mysql-port=$PORT –mysql-user=xx run 

a.

alter table sbtest1 drop key k;

tps :20,200

b.

alter table sbtest1 add key(k);

tps:大部分聚集在11,000~13,000,有抖动到7,000~9,000;最后出现12秒左右的TPS降低为0

time cost:4 min 8.13 sec)

完成DDL后,TPS稳定在13,000~14,000

alter table sbtest1 drop key k;  //TPS恢复至20,200

c.

set session old_alter_table = 1;

alter table sbtest1 add key(k);

tps:0

time cost:28.39 sec

总结:

1. online ddl耗时问题,相比老的ddl方式要更耗时

2. 存在性能抖动,最后阶段的锁表时间可能比较长,这取决于具体的负载,sysbench本身的压力已经比较高了,正常情况下的线上实例不会有这么大压力。

PS

无压力负载测试:

 

 

mysql> set session old_alter_table = OFF;

Query OK, 0 rows affected (0.00 sec)

 

mysql> alter table sbtest1 add  key (k);

Query OK, 0 rows affected (10.44 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> alter table sbtest1 drop key k;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> alter table sbtest1 add key(k), ALGORITHM=COPY;

Query OK, 1000000 rows affected (27.72 sec)

Records: 1000000  Duplicates: 0  Warnings: 0

 

mysql> alter table sbtest1 drop key k;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> alter table sbtest1 add key(k), LOCK=SHARED;

Query OK, 0 rows affected (9.89 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> alter table sbtest1 drop key k;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> alter table sbtest1 add key(k), LOCK=EXCLUSIVE;

Query OK, 0 rows affected (10.07 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

内部实现

这里我们以给一个普通的表增加一个普通二级索引为例

CREATE TABLE t1 (a int primary key, b int, c int);

INSERT INTO t1 values (1,2,3),(2,3,4),(3,4,5);

ALTER TABLE t1 ADD key(b);

1.DDL 线程

DDL的入口函数是mysql_alter_table,这里我们只谈涉及到Innodb层的函数。

大部分ALTER的接口函数都定义在文件hander0alter.cc中,关于ONLINE DDL主要分为四个阶段

a.检查存储引擎是否支持in-place 的DDL操作

8028     // Ask storage engine whether to use copy or in-place

8029     enum_alter_inplace_result inplace_supported=

8030       table->file->check_if_supported_inplace_alter(altered_table,

8031                                                     &ha_alter_info);

通常in-place操作比copy table的方式效率要高,如果不确定即将做的DDL是In-place的,可以拷贝一个完全一样的表,写入一两条数据,然后再做alter table,看输出是否有affected rows.没有的话说明就是in-place的。

是否支持in-place操作请参照上表,返回三个值:

HA_ALTER_INPLACE_NOT_SUPPORTED  Not supported

HA_ALTER_INPLACE_NO_LOCK        Supported

HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE Supported, but requires lock during main phase and exclusive lock during prepare phase

HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE  Supported, prepare phase requires exclusive lock (any transactions that have accessed the table must commit or roll back first, and no transactions can access the table while prepare_inplace_alter_table() is executing 

例如如上操作,从函数返回的值为HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE,表示支持in-place,但在准备阶段需要排他锁,也就是说在准备阶段需要确保当前任意操作该表的事务提交或回滚。当执行ha_innobase::prepare_inplace_alter_table时,所有事务会被阻塞。

当确认支持in-place操作后,就会进入另外一个函数分支mysql_inplace_alter_table

否则继续下面的逻辑(暂且不论)

b.准备阶段

mysql_inplace_alter_table:

6388   if (table->file->ha_prepare_inplace_alter_table(altered_table,

6389                                                   ha_alter_info))6390   {

6391     goto rollback;

6392   }

在准备阶段之前,已经加了表级别锁,这时候所有并发DML会被阻塞掉。

对应innodb层的函数是ha_innobase::prepare_inplace_alter_table,主要做以下动作:

b.1. DDL合法性检查,例如索引名是否是系统保留名(innobase_index_name_is_reserved),检查索引键(innobase_check_index_keys),禁止将列rename成一个已经存在的列名,检查索引列的长度以保证其不超过限制,检查外键、全文索引、自增列等操作.

这是一段冗长的代码,涉及大量的细节

b.2.在函数的最后调用函数prepare_inplace_alter_table_dict

这也是准备阶段,在完成检查后的一个重要函数,主要做以下事情:

b.2.1.先锁住Innodb数据词典(row_mysql_lock_data_dictionary,给dict_operation_lock加排他X锁,并加上dict_sys->mutex),再确认没有后台线程操作该表(dict_stats_wait_bg_to_stop_using_tables),随后调用的online_retry_drop_indexes_low暂时没搞清楚,先留着

如果是新建一个聚集索引,还需要drop掉原始表,再重新创建索引(很长一段逻辑,后续跟进)

b.2.2.更新数据词典信息,在系统表SYS_INDEXES中创建索引(row_merge_create_index)。然后在持有新建的索引的锁的情况下,为其分配行的增量日志(row_log_allocate).

增量日志主要用于在DDL的过程中,存储DML对数据的修改,其对应的控制结构体为row_log_t,挂在index->online_log上面,初始分配的内存大小为:

       2 * srv_sort_buf_size + sizeof *log 

其中srv_sort_buf_size对应的参数为innodb_sort_buffer_size,这也是增量日志每次扩展的块大小,另外它也是创建索引时做Merge排序时,一个缓冲块的大小,在老版本中被hardcode为1M,Percona在5.5中也将其设置成可配置,在一定程度上能提升FAST INDEX CREATITION的效率。说到这个,就不得不提到另外一个变量innodb_online_alter_log_max_size,它用于限制增量日志区域的最大限制,根据文档的描述,如果超过了限制,就会导致DDL失败,并且当前所有并发未提交的事务都会回滚。

b.2.3.提交对数据词典操作的事务,然后释放数据词典锁

trx_commit_for_mysql(trx);

row_mysql_unlock_data_dictionary(trx);  

c.执行DDL阶段

6419   if (table->file->ha_inplace_alter_table(altered_table,

6420                                           ha_alter_info))

6421   {

6422     goto rollback;

6423   }

在执行真正的DDL之前,首先要对mdl锁做降级(MDL_SHARED_UPGRADABLE),以确保并发DML能够执行。

上述调用对应Innodb层为ha_innobase::inplace_alter_table

首先读取聚集索引记录,使用Merge排序生成二级索引记录,并将数据插入到新创建的索引中

函数row_merge_build_indexes除了完成上述行为,随后还会调用row_log_apply应用增量日志

这里不深入,后续再展开讨论增量日志是如何生产和应用的,这里实际上也是online ddl的核心

在完成上述步骤后,回到MySQL层,会将mdl锁升级为排他锁,这意味着在下面的commit阶段将会阻塞对该表的DML操作

注意,该步骤如果等待超时,可能会引起DDL回滚。因此最好确认在DDL的时候没有逻辑备份业务

但不管是回滚还是提交,都会进入下一个阶段来完成

d.提交或回滚DDL阶段

6446   if (table->file->ha_commit_inplace_alter_table(altered_table,

6447                                                  ha_alter_info,

6448                                                  true))

6449   {

6450     goto rollback;

6451   }

对应innodb层函数:ha_innobase::commit_inplace_alter_table ,又是一段近800行的冗长函数代码,在该阶段决定是回滚DDL还是提交DDL操作;也会在该阶段执行DROP INDEX,RENAME COLUMN,增加或删除外键等操作,以及最终完成表的重建或索引创建的最后工作。该阶段会阻塞对表的DML操作。

对于drop index操作,会将其先在数据词典中rename掉(row_merge_rename_index_to_drop),以TEMP_INDEX_PREFIX作为命名前缀,然后在随后的row_merge_drop_indexes_dict函数再做真正的删除, 并从dict cache中删除(dict_index_remove_from_cache)。注意,只要index在数据词典中被rename掉,在crash recovery后,也会被删除掉。

函数太长了,有空再按照不同的DDL类型来跟踪其流程。

2.DML线程

最后一个问题是,在做DDL的过程中,DML在哪里记录row log呢?

相关函数被定义在文件row0log.cc中。

所有对索引的修改,通过函数row_log_online_op来记录

当表需要进行rebuilt时,通过函数row_log_table_delete、row_log_table_update、row_log_table_insert来记录更改

例如对二级索引的update操作,调用栈为:

row_update_for_mysql->row_upd_step->row_upd->row_upd_sec_index_entry->row_log_online_op 

update操作会调用两次row_log_online_op,先删除,再插入。

时间: 2024-11-05 06:15:59

[MySQL 5.6] MySQL 5.6 online ddl 使用、测试及关键函数栈的相关文章

mysql双主问题-紧急求助:生产环境,mysql双主结构,备机同步DDL语句成功,但是同步DML语句失败

问题描述 紧急求助:生产环境,mysql双主结构,备机同步DDL语句成功,但是同步DML语句失败 问题描述:双主架构环境,最近在主机上执行DDL语句能成功同步到备机,但是在主机上执行DML语句失败,请各位帮忙看一下,谢谢! Linux版本: Red Hat Enterprise Linux Server release 5.4 (Tikanga) mysql版本 +----------------------------+ | @@version | +---------------------

MySQL数据库SQL语句的C++ ODBC接口类测试结果 (转载)

c++|mysql|odbc|数据|数据库|语句 发信人: engineer (剑胆琴心~还是得走,sigh...), 信区: Linux 标  题: MySQL数据库SQL语句的C++ ODBC接口类测试结果 (转载) 发信站: BBS 水木清华站 (Mon Aug  9 18:03:47 1999)   [ 以下文字转载自 Database 讨论区 ] [ 原文由 engineer 所发表 ]         MySql数据库SQL语句的C++ ODBC接口类测试结果            

不能通过mysql.sock连接MySQL问题的解决办法

    这个问题主要提示是,不能通过'/tmp/mysql.sock'连到服务器,而php标准配置正是用过'/tmp/mysql.sock',但是一些mysql安装方法将mysql.sock放在/var/lib/mysql.sock或者其他的什么地方,你可以通过修改/etc/my.cnf文件来修正它,打开文件,可以看到如下的东东: [mysqld] socket=/var/lib/mysql.sock 改一下就好了,但也会引起其他的问题,如mysql程序连不上了,再加一点: [mysql] so

使用MySQL Proxy解决MySQL主从同步延迟

  MySQL的主从同步机制非常方便的解决了高并发读的应用需求,给Web方 面开发带来了极大的便利.但这种方式有个比较大的缺陷在于MySQL的同步机制是依赖Slave主动向Master发请求来获取数据的,而且由于服务器负 载.网络拥堵等方面的原因,Master与Slave之间的数据同步延迟是完全没有保证的.短在1秒内,长则几秒.几十秒甚至更长都有可能. 由于数据延迟问题的存在,当应用程序在Master上进行数据更新,然后又立刻需要从数据库中读取数据时,这时候如果应用程序从Slave上取数据(这也

Mtop,Mysql Top,一个Mysql的监控工具

mtop 实时监控 MySQL Requires the following perl modules:       Module             Available At     ------------     --------------------------------------------------------     Curses              http://cpan.linuxforum.net/authors/id/G/GI/GIRAFFED     D

快速插表-MYSQL 如果提升 MYSQL 插入数据库表的速度

问题描述 MYSQL 如果提升 MYSQL 插入数据库表的速度 我在插入 MYSQL 数据库表时 800 多个表 插入时间大 45 秒钟 速度太慢了. 解决方案 你的意思是如何插入数据, 还是创建表?感觉意思不是很清楚 解决方案二: 不明白你要创建那么多表做什么如果你需要创建那么多表, 而且表的结构都是统一的, 不妨每天晚上创建好一万个备用表(有就不用, 不够了才加).在需要创建表时,把备用表改一下名就得.

mysql linux-linux上mysql可以正常启动,却打mysql提示错误进不去

问题描述 linux上mysql可以正常启动,却打mysql提示错误进不去 service mysqld start Starting mysqld: [ OK ] [root@chanct201 etc]# [root@chanct201 etc]# mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '--default-character-set=utf8' (2) [root@chanc

[MySQL 学习] Innodb锁系统(2)关键函数路径

前提: 以下分析基于标准的配置选项: tx_isolation = REPEATABLE-READ innodb_locks_unsafe_for_binlog = OFF lock->type_mode用来表示锁的类型,实际上lock->type_mode包含了几乎所有锁的模式信息,例如锁类型判断是X锁还是S锁 lock->type_mode &LOCK_TYPE_MASK LOCK_MODE_MASK 0xFUL 用于表示锁模式掩码 LOCK_TYPE_MASK 0xF0UL

【MySQL基础】MySQL安装第三弹-mariadb-10.2.8

MySQL安装第二弹-Percona5.7安装 一.操作系统选择 Centos/RHEL/ORACLE LIUNX 5.X/6.X/7.X x86_64 发行版 二.操作系统参数调整 2.1 selinux设置 [root@localhost ~]# cat /etc/selinux/config SELINUX=disabled #关闭selinux  2.2调整最大文件数限制 [root@localhost ~]# ulimit -a core file size (blocks, -c)