本文主要分为三个部分,第一部分是看文档时的笔记;第二部分使用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_FORMAT property |
Yes | Yes | Yes | Yes | Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. |
ChangeKEY_BLOCK_SIZE property |
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 TABLE statement. |
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,先删除,再插入。