精华内容
下载资源
问答
  • mysql DDL如何执行

    2021-09-21 17:26:07
    注意:本文参考 三歪连MySQL大表怎么DDL变更都不懂 DDL概述 MySQL中的DDL语句形式比较多,概括一下有以下几类:CREATE,ALTER,DROP,RENAME,TRUNCATE。 这些操作都是隐式提交且原子性,要么成功,要么失败,在...

    目录

    DDL概述

    MetaData元数据

    MetaData Lock

    DDL执行方式

    LOCK选项

    COPY

    INPLACE

    INSTANT

    ONLINE DDL

    大表DDL方案

    ONLINE DDL方式

    pt-osc工具

    MySQL 8.0变更方式

    监控DDL执行进度


    ​​​​​​​

    注意:本文参考   三歪连MySQL大表怎么DDL变更都不懂

    DDL概述

    MySQL中的DDL语句形式比较多,概括一下有以下几类:CREATE,ALTER,DROP,RENAME,TRUNCATE。

    这些操作都是隐式提交且原子性,要么成功,要么失败,在MySQL 8.0之前DDL操作是不记录日志的。

    今天就聊一下跟系统版本发布相关的数据库结构变更,主要就是ALTER TABLE变更了,DDL变更流程普通的DML变更是类似的,如下所示

    注:这里涉及MySQL基础知识,还不知道的朋友翻看下我MySQL基础章节即可。

    在早期的MySQL版本,DDL变更都会导致全表被锁,阻塞表上的DML操作,影响业务正常运行,好的一点就是,随着MySQL版本的迭代,DDL的执行方式也在变化。

    MetaData元数据

    MySQL的元数据(MetaData)跟其他的RDBMS数据库一样的,描述的对象的结构信息,存储在information_schema架构下,例如常见的TABLES、COLUMNS等,下面例子是创建一个表crm_users,MySQL会自动往Information_schema.tables和columns等相关数据字典表中插入数据,这些数据称为元数据,一般都是静态化,只有表上发生了DDL操作才会实时更新。

     

    MetaData Lock

    MySQL利用MetaData Lock来管理对象的访问,保证数据的一致性,对于一些核心业务表,表上DML操作比较频繁,这个时候添加字段可能会触发MetaData Lock。

    可以看到Waiting for table metadata lock等待事件,thread 155正在执行alter table等待thread 154执行的select释放锁,因为DML在执行期间会持有SHARED_READ锁,要执行DDL时获取SHARED_UPGRADABLE(共享可升级锁,缩写为SU,允许并发更新和读同一个表)锁成功,但是获取EXCLUSIVE MetaData Lock锁失败,处于暂挂PENDING状态。

    DDL执行方式

    从MySQL官方文档可以看到,ALTER TABLE的选项很多,跟性能相关的选项主要有ALGORITHM和LOCK。

    ALGORITHM OPTIONDESCRIPTION
    COPYMySQL早期的变更方式,需要创建修改后的临时表,然后按数据行拷贝原表数据到临时表,做rename重命名来完成创建,在此期间不允许并发DML操作,原表是可读的,不可写,同时需要额外一倍的磁盘空间。
    INPLACE直接在原表上进行修改,不需创建临时表拷贝数据及重命名,原表会持有Exclusive Metadata  Lock,通常是允许并发DML操作。
    INSTANTMySQL 5.8开始支持,只修改数据字典中的元数据,表数据不受影响,执行期间没有Exclusive Metadata  Lock,允许并发的DML操作。

    从这张表可以看到,MySQL对于DDL执行方式一直在做优化,目的就是为了提高DDL执行效率,减少锁等待,不影响表数据,同时不影响正常的DML操作。

    LOCK选项

    LOCK OPTiONDESCRIPTION
    DEFAULT默认模式:MySQL根据运行情况,在尽量不锁表的情况下自动选择LOCK模式。
    NONE无锁:允许Online DDL期间进行并发读写操作,如果Online DDL操作不支持对表并发DML操作,则DDL操作失败,对表修改无效。
    SHARED共享锁:Online DDL操作期间不影响读取,阻塞写入。
    EXCLUSIVE排它锁:Online DDL操作期间不允许对锁表进行任何操作。

    下面举例说明下这几种方式的执行过程,先创建测试表,制造一些数据。

    COPY

    COPY方式的变更流程如下:

    根据业务需要,需要在crm_users添加一个字段user_type,采用COPY方式执行变更。

    从执行过程及profile可以看出,通过COPY方式会创建临是表#sql-564_85,获取System Lock,拷贝数据到临时表,最后做rename表名切换,释放Lock资源,在执行期间不支持并发DML操作。

    INPLACE

    INPLACE方式是在原表上直接修改,对于添加索引、添加/删除列、修改字段NULL/NOT NULL属性等操作,需要修改MySQL内部的数据记录,需要重建表(Rebuild Table)。

    从执行过程可以看到,需要获取Exclusive Metadata  Lock,修改表数据,释放Lock,在执行期间支持并发DML操作。

    INSTANT

    MySQL 5.8开始推出的方式,DDL只修改数据字典中的元数据,表数据不受影响,没有Exclusive Metadata  Lock,允许并发的DML操作,支持的DDL变更是有限制的,目前主要包括添加字段,添加/删除生成列,修改ENUM或SET列,改变索引类型以及重命名表。

    比对下这三种方式的执行效率

    执行方式/项目数据量(w)执行时间(s)重建表修改MetaData修改Data允许并发DML
    COPY65029.89YESNoYesNo
    INPLACE65010.56YESNoYesYes
    INSTANT6500.19NoYesNoYes

    ONLINE DDL

    截止MySQL 8.0,OnLine DDL有三种方式COPY,INPLACE,INSTANT,MySQL会自动根据执行的DDL选择使用哪种方式,一般会优先选择INSTANT方式,如果不支持,就选择INPLANCE方式,再不支持就只能选择COPY方式了。

    MySQL官方文档也给出了Online DDL的支持矩阵,列下常用的DDL操作,对比项主要包括是否重建表,允许并发的DML操作以及只修改元数据,表数据不受影响。

    OperationInstantIn PlaceCopyRebuilds TablePermits Concurrent DMLOnly Modifies Metadata
    Adding a columnYesYes*YesNo*Yes*Yes
    Dropping a columnNoYesYesYesYesNo
    Renaming a columnNoYesYesNoYesYes
    Setting a column default valueYesYesYesNoYesYes
    Dropping the column default valueYesYesYesNoYesYes
    Changing the auto-increment valueNoYesYesNoYesNo
    Making a column NULLNoYesYesYes*YesNo
    Making a column NOT NULLNoYesYesYes*YesNo
    Adding a primary keyNoYes*YesYes*YesNo
    Dropping a primary keyNoNoYesYesNoNo
    Creating or adding a secondary indexNoYesYesNoYesNo
    Dropping an indexNoYesYesNoYesYes
    Renaming an indexNoYesYesNoNoNo
    Adding a FULLTEXT indexNoYes*YesNo*NoNo

    大表DDL方案

    在实际业务系统中,业务发展比较快,表的数据量比较大,业务层面又做了读写分离,同时会将MySQL数据实时同步到数据仓库(包括实时数仓和离线数仓),实际的数据库架构如下。

    假设这是一个交易系统数据库,订单表booking有8000w数据,且接入到了实时和离线仓库,根据业务需要,在订单表booking添加一个字段,在MySQL 5.7之前添加字段属于高危操作,需要充分考虑对业务的影响,主要存在于两个方面:

    在读写分离场景,主从同步延迟导致业务数据不一致

    实时数仓ADB不允许源端MySQL表重命名,如果通过COPY方式或者pt-osc、gh-ost等工具都会rename表名,那么就需要从数仓删除该表,重新配置同步(全量 + 增量),会影响数仓业务

    ONLINE DDL方式

    对于MySQL 5.6到5.7的版本,可以使用OnLine DDL的方式变更,对于大表来说,执行时间会很长,好处是在Master上DML操作不受影响,但是会导致主从延时。

    假如Master上添加字段执行了20分钟,相应的Slave也要执行20分钟,在这期间Slave一直处于延迟状态,会造成业务数据不一致,比如用户在Master下单成功,由于Slave延迟查询不到订单信息,用户误以为网络原因没有下单成功,又下了一单,导致重复下单的情况。

    这种方式会导致主从延迟,但是不会影响实时数仓的业务,根据业务情况,只能选择在业务低峰期执行了。

    pt-osc工具

    为了解决DDL变更导致主从延时对业务的影响,会想到用大表变更利器pt-osc(pt-online-schema-change)或者gh-ost工具来做,这两个工具执行过程及原理大同小异,变更流程如下(不考虑外键,按照MySQL规范不允许使用外键):

    创建一个新的表,表结构为修改后的数据表,用于从源数据表向新表中导入数据。

    在源表上创建触发器,用于记录从拷贝数据开始之后,对源数据表继续进行数据修改的操作记录下来,用于数据拷贝结束后,执行这些操作,保证数据不会丢失。

    拷贝数据,从源数据表中拷贝数据到新表中。

    修改外键相关的子表,根据修改后的数据,修改外键关联的子表。

    rename源数据表为old表,把新表rename为源表名,并将old表删除。

    删除触发器。

    执行pt-osc的时候也需要获取一个Exclusive Metadata  Lock,如果在此期间表上有DML操作正在进行,pt-osc操作会一直处于暂挂PENDING状态,这个时候表上正常DML操作都会被阻塞,MySQL活动连接数瞬间暴涨,CPU使用率100%,依赖的该表的接口都会报错,所以要选择在业务低峰期执行,同时做好MetaData Lock锁的监控以便业务不受影响,来看一个例子:

    D=trade, t=booking:数据库trade,表名booking。

    --chunk-size=1000:每次拷贝的数据行数。

    --max-log = 1:确保从库延迟不超过1s,超过就停止拷贝数据。

    --check-interval=2:表示等待2s之后继续拷贝数据。

    --recursion-method="hosts":如果不是使用默认端口3306,那么使用hosts方式来查找从库更可靠。

    一般MySQL binlog格式都是ROW,pt-osc在拷贝数据的过程也会产生大量的binlog,也可能导致主从延时,需要控制好每次拷贝数据的大小和频率,在执行期间,也会降低DML的并发度。

    MySQL 8.0变更方式

    用过Oracle的都知道,DDL变更都是修改元数据,上亿的表在Oracle中DDL变更都是瞬间完成。

    令人激动的是,MySQL 8.0也推出了INSTANT方式,真正的只修改MetaData,不影响表数据,所以它的执行效率跟表大小几乎没有关系。建议新系统上线用MySQL的话尽量使用MySQL 8.0,老的数据库也可以升级到MySQL 8.0获取更好的性能。

    官方文档对INSTANT的解释:

    INSTANT: Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous. Concurrent DML is permitted. (Introduced in MySQL 8.0.12)

    既要解决主从同步,又要解决rename数仓不同步的问题,目前只有INSTANT方式满足需求了。

    监控DDL执行进度

    在大表执行DDL变更的时候,非常关心它的执行进度,MySQL 5.7之前是没有好的工具去监控,基本只能坐等了。在MySQL 8.0可以通过开启performance_schema,打开events_stages_current事件进行监控。

    展开全文
  • MySQL 执行DDL语句 hang住了怎么办? 不要慌,先点支烟,听我娓娓道来! 前两天,早上7点多的时候,抓起手机忽然看到了圈内的一则DDL语句 hang住的案例,阅读到文末,发现文中留有一些疑问(当然,文章是7月份的,...
    • MySQL 执行DDL语句 hang住了怎么办? 不要慌,先点支烟,听我娓娓道来!
    • 前两天,早上7点多的时候,抓起手机忽然看到了圈内的一则DDL语句 hang住的案例,阅读到文末,发现文中留有一些疑问(当然,文章是7月份的,现在或许作者已经解决了这些疑问),于是一咕噜从床上爬起来,按照文中的复现方法操作了一遍(复现方法见文末),然后,按照自己的思路解决了该疑问,现在将整个过程整理出来分享给大家。

    环境

    • 数据库版本:MySQL 5.7.27
    • 数据信息
      • sysbench模拟2张1000W的表
    • 操作系统版本:CentOS Linux release 7.3.1611 (Core)
    • 服务器信息
      • CPU:Intel(R) Xeon(R) CPU E5-4627 v2 @ 3.30GHz * 2
      • 内存:248G
      • 磁盘:1.6T LSI Flash卡
      • 网卡:万兆网卡

    现象

    • 当我们发现一个故障问题时,首先需要做的事情,就是先确认现象,也就是先要自己亲眼瞅见故障长什么样子,而不是直接上去就是一通胡乱排查。尤其是故障是别人反馈过来的时候,一定要首先确认故障现象表现及其真实性(别人反馈过来的问题,很多时候根本就不是问题,而是他自己姿势不对)。
    • 在本文中,提到的故障现象是DBA侧自己执行DDL语句修改字段长度时自己发现的(当然,这里是模拟的DBA侧操作),那么,根据上述准则,我们先开启另外一个会话,查看执行DDL语句 hang住具体是什么情况?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    # 使用show processlist语句查看会话状态信息,发现DDL语句的state列值为Waiting for table metadata lock,表示在等待MDL元数据锁。根据MySQL 5.7及其之后的版本中的online ddl特性,该语句应该立即执行完成(它只会修改元数,因为这里只是修改了字段长度,并没有修改字段的其他属性),因此,故障现象确认

    admin@localhost : (none) 11:48:22> show processlist;

    +----+-------+-----------+--------+---------+------+---------------------------------+-----------------------------+

    | Id | User | Host | db | Command | Time | State | Info |

    +----+-------+-----------+--------+---------+------+---------------------------------+-----------------------------+

    | 27 | admin | localhost | sbtest | Sleep | 123 | | NULL |

    | 28 | admin | localhost | sbtest | Query | 102 | Waiting for table metadata lock | alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '' |

    | 29 | admin | localhost | NULL | Query | 0 | starting | show processlist |

    | 30 | admin | localhost | NULL | Sleep | 93 | | NULL |

    +----+-------+-----------+--------+---------+------+---------------------------------+-------------------------------+

    rows in set (0.00 sec)

    • 友情提示:确认故障现象的位置不要搞错,在什么位置发现故障问题,首先就在什么位置进行故障确认。举个反例,曾经我碰到一个同学,在一个“应用-->LVS-->分库中间件-->读写分离中间件-->数据库主从集群”的架构层级中,应用侧反馈数据库连接很慢,这位同学直接登录到数据库中去确认现象了,显然,确认故障现象时搞错了位置,在这个反例中确认故障现象的位置应该以应用侧反馈故障现象的位置为准(首先使用LVS的VIP尝试连接),逐层往下确认

    分析

    • 确认了故障现象,DDL语句hang住的原因是因为在等待MDL元数据锁,但不知道MDL元数据锁被谁持有了。接下来,就要围绕这个现象,推测可能导致该问题的一些原因了,哪些原因可能导致该问题呢?我们可以按照下面的思路进行逐个排除
      • 服务器的主机负载过高(CPU、内存、磁盘吞吐与IOPS、网卡带宽),有没有别的程序挤占了数据库进程的资源
        * 你也许会说,这里的现象很明显是因为在等MDL锁,跟服务器负载无关,肯定是有人加了锁没释放。但我想提醒的是,该现象真的是一个单纯的问题吗?会不会是一个链式反应导致的?等待MDL锁虽然是DDL语句被阻塞的原因,但也许它同时也是服务器高负载的现象与结果
      • 数据库进程的负载过高
        * 数据库中的活跃会话数量及其状态
        * 数据库的QPS/TPS
      • 存在其他会话正在执行DML语句,或执行了某些DML语句之后事务未及时提交、或者其他某个会话也同时在执行某个DDL语句修改sbtest1表的表结构信息

    排查

    • 有了思路,接下来就按照上文中提到的思路逐个进行排查
    • 首先,我们查看主机负载信息,通过下图我们可以看到,主机基本处于空载状态,毫无压力

    • 然后,我们查看数据库的活跃会话数量及其状态,我们可以看到数据库中并没有大量会话,也不存在正在执行的DML语句在操作表sbtest1,也不存在同时有其他会话同时使用DDL在操作相同的表,但这里无法确认是否存在未提交的事务

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    # 反复多执行几次show processlist语句

    admin@localhost : (none) 11:49:10> show processlist;

    +----+-------+-----------+--------+---------+------+---------------------------------+-----------------------------------+

    | Id | User | Host | db | Command | Time | State | Info |

    +----+-------+-----------+--------+---------+------+---------------------------------+------------------------------------+

    | 27 | admin | localhost | sbtest | Sleep | 149 | | NULL |

    | 28 | admin | localhost | sbtest | Query | 128 | Waiting for table metadata lock | alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '' |

    | 29 | admin | localhost | NULL | Query | 0 | starting | show processlist |

    | 30 | admin | localhost | NULL | Sleep | 119 | | NULL |

    +----+-------+-----------+--------+---------+------+---------------------------------+---------------------------------------+

    rows in set (0.00 sec)

    • 通过上述步骤,已确认在负载层面并没有问题,此时,我们需要重点确认是否存在某个会话执行了某些DML语句之后事务未及时提交,如何确认这些信息呢?我们可以通过performance_schema和information_schema中的锁和事务相关的表进行查询确认
    • 先查看information_schema中记录的事务信息

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    # 发现并没有事务存在...

    admin@localhost : sbtest 05:49:17> select from information_schema.innodb_trx\G

    Empty set (0.00 sec)

    # 也可以顺便使用sys.innodb_lock_waits视图确认是否存在一些事务锁等待

    admin@localhost : performance_schema 06:27:35> select from sys.innodb_lock_waits\G

    Empty set, 3 warnings (0.00 sec)  # 查询结果为空

    查看performance_schema下的MDL元数据锁记录信息 

    # WTF..居然为空

    admin@localhost : sbtest 06:00:21> select from performance_schema.metadata_locks;

    Empty set (0.00 sec)

    # 也可以顺便使用sys.schema_table_lock_waits视图查看表级别的锁等待

    admin@localhost : performance_schema 06:28:12> select from sys.schema_table_lock_waits\G

    Empty set (0.00 sec)  # 查询结果为空

    • 查看performance_schema下的handle持有信息

    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

    # 发现表sbtest1的handle被thread_id=70的线程持有

    admin@localhost : (none) 11:49:36> select from performance_schema.table_handles where OWNER_THREAD_ID!=0;

    +-------------+---------------+-------------+--------+-----------------+----------------+---------------+---------------+

    | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK |

    +-------------+---------------+-------------+---------+-----------------+----------------+---------------+---------------+

    TABLE | sbtest | sbtest1 | 140049018564288 | 70 | 6 | NULL NULL |

    +-------------+---------------+-------------+----------+-----------------+----------------+---------------+---------------+

    1 row in set (0.00 sec)

    # 通过performance_schema.threads表查看是哪个线程(thread_id是数据库内部的线程ID,我们需要看到与之对应的processlist id)

    admin@localhost : (none) 11:50:03> select thread_id,processlist_id,type from performance_schema.threads where processlist_id is not null and type='FOREGROUND';

    +-----------+----------------+------------+

    | thread_id | processlist_id | type |

    +-----------+----------------+------------+

    | 43 | 1 | FOREGROUND |

    | 69 | 27 | FOREGROUND |

    | 70 | 28 | FOREGROUND |  # 发现processlist id为28

    | 71 | 29 | FOREGROUND |

    | 72 | 30 | FOREGROUND |

    +-----------+----------------+------------+

    rows in set (0.00 sec)

    # 通过show processlist再次查看一下id号,额。。发现id列为28的居然就是执行DDL语句被hang住那个会话,好吧,白忙活了

    admin@localhost : (none) 11:50:26> show processlist;

    +----+-------+-----------+--------+---------+------+---------------------------------+------------------------------------+

    | Id | User | Host | db | Command | Time | State | Info |

    +----+-------+-----------+--------+---------+------+---------------------------------+--------------------------------------+

    | 27 | admin | localhost | sbtest | Sleep | 228 | | NULL |

    | 28 | admin | localhost | sbtest | Query | 207 | Waiting for table metadata lock | alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '' |

    | 29 | admin | localhost | NULL | Query | 0 | starting | show processlist |

    | 30 | admin | localhost | NULL | Sleep | 198 | | NULL |

    +----+-------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+

    rows in set (0.00 sec)

    • 通过show engine innodb status查看下锁与事务信息

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    # 仍然没有发现有效的锁信息

    admin@localhost : performance_schema 06:14:13> show engine innodb status;

    ......

    ------------

    TRANSACTIONS

    ------------

    Trx id counter 11559

    Purge done for trx's n:o < 11557 undo n:o < 0 state: running but idle

    History list length 60

    LIST OF TRANSACTIONS FOR EACH SESSION:

    ---TRANSACTION 421628104988048, not started

    0 lock struct(s), heap size 1136, 0 row lock(s)

    ---TRANSACTION 421628104987136, not started

    0 lock struct(s), heap size 1136, 0 row lock(s)

    ---TRANSACTION 421628104985312, not started

    0 lock struct(s), heap size 1136, 0 row lock(s)

    ---TRANSACTION 421628104984400, not started

    0 lock struct(s), heap size 1136, 0 row lock(s)

    ---TRANSACTION 421628104986224, not started

    0 lock struct(s), heap size 1136, 0 row lock(s)

    ......

    • 排查到这里,视乎已经没招了,也许我们还可以用mysqladmin debug命令试试看

    1

    2

    3

    4

    # 执行debug命令,执行该命令之后,一些锁信息可能会被debug出来打印到错误日志中

    [root@physical-machine ~]# mysqladmin debug

    # 很遗憾,在错误日志中仍然没有发现有效的锁信息

    [root@physical-machine ~]# vim /data/mysqldata1/log/error.log

    • 还有最后一招,可以使用pstack和gdb命令查看,但,生产系统不建议随随便便使用这类命令,这类命令会让整个实例级别发生一段时间的阻塞。且上述故障问题并不能说明整个数据库实例级别存在问题,也许只是发生MDL锁等待的两个会话之间的问题。so...咋办呢?要认怂吗?
    • 等等,我们好像忽略了点什么,既然DDL语句在等待MDL元数据锁,为啥在performance_schema.metadata_locks表中没有记录?查看一下MDL事件采集器试试看?

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    # 喔。。发现居然MDL锁信息的采集器开关并没有打开,难怪metadata_locks表中无法记录MDL元数据锁信息

    admin@localhost : performance_schema 06:30:16> select from performance_schema.setup_instruments where name like '%/mdl';

    +----------------------------+---------+-------+

    NAME | ENABLED | TIMED |

    +----------------------------+---------+-------+

    | wait/lock/metadata/sql/mdl | NO NO |

    +----------------------------+---------+-------+

    1 row in set (0.00 sec)

    # 现在,我们启用mdl的采集器

    admin@localhost : sbtest 07:18:52> call sys.ps_setup_enable_instrument('sql/mdl');

    +-----------------------+

    | summary |

    +-----------------------+

    | Enabled 6 instruments |

    +-----------------------+

    1 row in set (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    • 关于instruments采集器,如果事先是处于关闭状态,启用之后,需要新的请求进来才能够收集对应的事件信息,因此,如果要采集MDL锁的信息,我们得让故障现象重新复现一遍。读到这里,也许你会说,干嘛不早说?好吧,SORRY,我是故意的...
    • 现在,我们终止掉DDL语句,使用文末的步骤重新操作一遍
    • 然后,我们重新查询information_schema下的事务和锁信息

    1

    2

    3

    4

    5

    6

    # 查看information_schema.innodb_trx表,仍然没有事务信息

    admin@localhost : sbtest 07:17:03> select from information_schema.innodb_trx\G

    Empty set (0.00 sec)

    # 查看事务锁等待信息,仍然为空

    admin@localhost : sbtest 07:17:30> select from sys.innodb_lock_waits\G

    Empty set, 3 warnings (0.01 sec)

    • 查看performance_schema下的MDL元数据锁记录信息

    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

    33

    34

    35

    36

    37

    38

    39

    40

    41

    42

    43

    44

    45

    46

    47

    48

    49

    50

    51

    52

    53

    54

    55

    56

    57

    58

    59

    60

    61

    62

    63

    64

    65

    66

    # 查看metadata_locks表中的MDL锁信息,咦,有信息了!不过,有点乱啊(我们这里只查询sbtest库下的sbtest1表就可以了,因为我们的操作也只涉及到这张表)

    admin@localhost : (none) 11:52:46> select from performance_schema.metadata_locks where OBJECT_SCHEMA='sbtest' and OBJECT_NAME='sbtest1';

    +-------------+---------------+-------------+-----+--------+---------------+-------------+--------+-----------------+----------------+

    | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |

    +-------------+---------------+-------------+-----+--------+---------------+-------------+--------+-----------------+----------------+

    # 从这行信息我们可以看到,表sbtest1上有一把SHARED_WRITE锁处于GRANTED状态,为thread_id=69的线程所持有。SHARED_WRITE类型的MDL锁是一把意向排他IX锁,通常在执行MDL或select ... for update时产生

    TABLE | sbtest | sbtest1 | 140048817276288 | SHARED_WRITE | TRANSACTION | GRANTED | | 69 | 11 |

    # 从这行信息我们可以看到,表sbtest1上有一把SHARED_UPGRADABLE的锁处于GRANTED状态,为thread_id=70的线程持有,从上文中的信息我们可以知道,thread_id=70的process id为28,也就是执行DDL语句被阻塞的那个会话id。SHARED_UPGRADABLE类型的MDL锁是一把共享升级锁,一般在执行online DDL语句时会产生。它的作用是在执行online ddl期间允许相同表的DML但防止DDL

    TABLE | sbtest | sbtest1 | 140049018604784 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 70 | 6 |

    # 从这行信息我们可以看到,表sbtest1上有一把EXCLUSIVE处于PENDING状态,为thread_id=70的线程在等待,从上文中的信息我们可以知道,thread_id=70的process id为28,也就是执行DDL语句被阻塞的那个会话id。EXCLUSIVE类型的MDL锁是一把排它X锁,用于阻止其他线程读写元数据信息,一般在执行DDL时产生

    TABLE | sbtest | sbtest1 | 140049018564112 | EXCLUSIVE | TRANSACTION | PENDING | | 70 | 6 |

    +-------------+---------------+-------------+-----+---------+---------------+-------------+--------+-----------------+----------------+

    rows in set (0.00 sec)

    # 通过上述信息我们可以得知,thread_id=70的线程需要获取的EXCLUSIVE锁与thread_id=69的线程所持有的SHARED_WRITE锁冲突了,但thread_id=69线程的process id是多少呢?查看一下performance_schema.threads表

    admin@localhost : (none) 11:53:47> select thread_id,processlist_id,type from performance_schema.threads where processlist_id is not null and type='FOREGROUND';

    +-----------+----------------+------------+

    | thread_id | processlist_id | type |

    +-----------+----------------+------------+

    | 43 | 1 | FOREGROUND |

    | 69 | 27 | FOREGROUND |  # 发现thread_id=69的线程process id为27

    | 70 | 28 | FOREGROUND |

    | 71 | 29 | FOREGROUND |

    | 74 | 32 | FOREGROUND |

    +-----------+----------------+------------+

    rows in set (0.01 sec)

    # 到这里,我们知道了DDL语句就是被process id为27的线程阻塞的,但,还不是很直观,查看起来比较繁琐,我们还是直接使用sys.schema_table_lock_waits视图查看表级别的锁等待试试看吧,可以发现,该视图打印的信息看起来就很清晰了(可以清晰看到谁在等待,谁持有锁),不过,怎么有两行?以哪行为准呢?

    admin@localhost : (none) 11:59:04> select from sys.schema_table_lock_waits\G

    *************************** 1. row ***************************

                   object_schema: sbtest

                     object_name: sbtest1

               waiting_thread_id: 70

                     waiting_pid: 28

                 waiting_account: admin@localhost

               waiting_lock_type: EXCLUSIVE

           waiting_lock_duration: TRANSACTION

                   waiting_query: alter table sbtest1 modify col ... E utf8_bin NOT NULL DEFAULT ''

              waiting_query_secs: 744

     waiting_query_rows_affected: 0

     waiting_query_rows_examined: 0

              blocking_thread_id: 69

                    blocking_pid: 27

                blocking_account: admin@localhost

              blocking_lock_type: SHARED_WRITE

          blocking_lock_duration: TRANSACTION

         sql_kill_blocking_query: KILL QUERY 27 # 这一行表锁等待信息提示kill 掉process id为27的线程

    sql_kill_blocking_connection: KILL 27

    *************************** 2. row ***************************

                   object_schema: sbtest

                     object_name: sbtest1

               waiting_thread_id: 70

                     waiting_pid: 28

                 waiting_account: admin@localhost

               waiting_lock_type: EXCLUSIVE

           waiting_lock_duration: TRANSACTION

                   waiting_query: alter table sbtest1 modify col ... E utf8_bin NOT NULL DEFAULT ''

              waiting_query_secs: 744

     waiting_query_rows_affected: 0

     waiting_query_rows_examined: 0

              blocking_thread_id: 70

                    blocking_pid: 28

                blocking_account: admin@localhost

              blocking_lock_type: SHARED_UPGRADABLE

          blocking_lock_duration: TRANSACTION

         sql_kill_blocking_query: KILL QUERY 28  # 这一行表锁等待信息提示kill 掉process id为28的线程

    sql_kill_blocking_connection: KILL 28

    rows in set (0.01 sec)

    • 通过上述一翻折腾,我们终于找出了谁持有了MDL锁了,不过,遗憾的是,我们无法知道持有MDL锁的线程执行了什么操作,所以我们不能草率地直接将其杀掉,作为DBA侧的人员来说,或许我们可以推测一下,与应用的INSERT报错操作或许有关系,但这里找不出任何直接的关联证据,怎么办呢?请继续往下看
    • PS:
      • 在MySQL 5.7版本中,mdl的instruments采集器是默认关闭的,如果事先没有启用该采集器,则将会排查该问题带来巨大的麻烦,所以,建议大家在MySQL 5.7中启用该采集器
        * 启用这个采集器并不会有多大的性能损失,但是带来的便利大家都看到了
        * 更多的时候,我们也许会碰到语句正在正常执行,但是执行时间却非常长,无法看到MySQL内部此时正在执行具体的什么操作,此时,甚至建议大家将所有的等待事件都默认启用,在很多时候可以方便地查看每个会话正在执行什么具体的操作。不过,请注意,performance_schema在MySQL 5.7版本中默认启用,与关闭performance_schema相比,启用之后有1%~5%的性能损耗;与关闭performance_schema相比,启用所有的等待事件之后有1%~15%的性能损耗。具体损耗需要看具体的语句类型
      • 在MySQL 8.0版本中,mdl的instruments采集器是默认启用的

    解决

    • 在上文中我们找到了问题的原因,大致的解决方法也找到了,但陷入了两难的境地,这个时候,我们如何抉择解决这个问题的方法呢?这里我们罗列了如下几种解决方式供参考:
      • 方式一:杀死阻塞DDL语句的会话,但通常这不是明智之举,因为我们无法找出持有MDL锁的会话执行了什么操作,也无法判断什么原因导致没有释放MDL锁。且应用侧不知情的情况下操作,是存在一定风险的
      • 方式二:终止DDL语句,选择业务低峰期或其他时间段执行,显然,这种被动干等、且可能需要反复尝试,还不一定能解决问题的做法不是最高效的
      • 方式三:应用开发人员沟通,确认清楚INSERT语句是否必须要立即插入超长数据(另外,别忘记请应用开发人员协同排查process id为27的线程做了什么操作导致MDL锁未释放)
        * 如果不是,则建议应用开发人员自行调整数据长度以匹配表结构定义长度,这样DDL语句暂时也不需要执行了,DBA侧直接撤销DDL操作即可。后续如果有需求,则选择在一个业务低峰期或者一个维护窗口执行即可
        * 如果该INSERT语句必须要立即插入超长数据,则建议并指导应用开发人员处理可能存在的事务回滚相关事宜(这个时候事务并未提交,理论上回滚该事务在数据库层面不存在数据丢失的风险),然后,DBA侧再执行DDL语句修改字段定义长度
    • PS:也许有的同学会对方式二提出质疑,在生产环境中,执行DDL语句,DBA侧可以任性做尝试吗?当然不能,我们在上文中提到过,这里我们模拟的DDL操作只会修改元数据,为什么只会修改元数据呢,因为我们是将varchar类型列从60个字符定义长度修改为70个字符定义长度,而这里数据库环境中的字符集为utf8。至于为什么满足这些条件之后,在MySQL 5.7及其之后的版本中的online ddl只需要修改元数据就能够完成操作,本文不做赘述,有兴趣的同学请自行研究MySQL 5.7或MySQL 8.0的online ddl特性。

    预防

    • 如果MDL锁等待现象持续时间太长没有及时发现,在高并发业务场景下是比较危险的,一旦后续持续不断有该DDL涉及的表相关的DML请求进来,则可能造成大量的锁等待,甚至迅速将数据库的连接数打满。要预防该情况的发生,需要应用侧和DBA侧都各自做好预防策略
    • DBA侧可通过在执行DDL语句的会话中,会话级别设置lock_wait_timeout系统变量为一个较小的值,在超过该时间值之后,仍然无法获得所需的锁时,自动放弃DDL操作(请自行评估需求)

    1

    2

    3

    4

    root@localhost : sbtest 04:37:43> set lock_wait_timeout=10;

    Query OK, 0 rows affected (0.00 sec)

    root@localhost : sbtest 04:37:47> alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '';

    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

    • 应用侧对请求失败的事务,需要有相应的重试、回滚机制(以便在发生异常时及时释放事务相关的资源),以及记录每一笔请求的日志记录

    附录-复现方法

    • 1)在同一个数据库中,开启三个会话
    • 2)在会话1中,开启一个显式事务,并使用INSERT语句插入一行超过字段定义长度的数据行(模拟应用侧插入数据)

    1

    2

    3

    4

    admin@localhost : sbtest:13: > begin;

    Query OK, 0 rows affected (0.00 sec)

    admin@localhost : sbtest:17: > insert into sbtest1 values(2,2,'40393031789-25132409365-58213491013-66541287984-65586459874-05762316127-59922091522-12151119251-49498591378-18011532520','test-29736863337-73672352543-26439979097-89323822066-87557735686');

    ERROR 1406 (22001): Data too long for column 'pad' at row 1

    • 3)在会话2中,将报错字段pad的长度加长到70个字符(模拟DBA侧使用DDL语句修改列长度定义)

    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

    33

    34

    35

    # 查看表结构中的字段定义长度,可以发现,报错的pad列定义长度为varchar类型的60个字符长度

    root@localhost : sbtest 04:12:03> show create table sbtest1;

    +---------+------------------------------------------------------------------------------------------------------+

    Table Create Table |

    +---------+-------------------------------------------------------------------------------------------------------+

    | sbtest1 | CREATE TABLE `sbtest1` (

      `id` int(11) NOT NULL AUTO_INCREMENT,

      `k` int(11) NOT NULL DEFAULT '0',

      `c` char(120) COLLATE utf8_bin NOT NULL DEFAULT '',

      `pad` varchar(60) COLLATE utf8_bin NOT NULL DEFAULT '',  # pad字段的定义长度为60

      PRIMARY KEY (`id`),

      KEY `k_1` (`k`)

    ) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

    +---------+-------------------------------------------------------------------------------------------------------+

    1 row in set (0.01 sec)

    # 查看INSERT语句中,pad列给出数据字符串的字符长度与字节长度

    ## INSERT语句中给出的pad列值字符长度为64

    root@localhost : sbtest 11:01:33> select char_length('test-29736863337-73672352543-26439979097-89323822066-87557735686');

    +---------------------------------------------------------------------------------+

    | char_length('test-29736863337-73672352543-26439979097-89323822066-87557735686') |

    +---------------------------------------------------------------------------------+

    | 64 |

    +---------------------------------------------------------------------------------+

    1 row in set (0.00 sec)

    ## INSERT语句中给出的pad列值字节长度为64

    root@localhost : sbtest 11:02:19> select length('test-29736863337-73672352543-26439979097-89323822066-87557735686');

    +----------------------------------------------------------------------------+

    | length('test-29736863337-73672352543-26439979097-89323822066-87557735686') |

    +----------------------------------------------------------------------------+

    | 64 |

    +----------------------------------------------------------------------------+

    1 row in set (0.00 sec)

    # 使用alter语句修改pad列定义长度为70

    root@localhost : sbtest 04:12:47> alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '';   

    ## 发生阻塞

    • 4)在会话3中,查看数据库中的会话状态信息(模拟DBA侧排查故障问题)

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    # 可以看到info列的alter语句的state列值为Waiting for table metadata lock,表示在等待MDL锁

    admin@localhost : (none) 11:50:55> show processlist;

    +----+-------+-----------+--------+---------+------+---------------------------------+--------------------------------+

    | Id | User | Host | db | Command | Time | State | Info |

    +----+-------+-----------+--------+---------+------+---------------------------------+--------------------------------+

    | 27 | admin | localhost | sbtest | Sleep | 919 | | NULL |

    | 28 | admin | localhost | sbtest | Query | 898 | Waiting for table metadata lock | alter table sbtest1 modify column `pad` varchar(70) COLLATE utf8_bin NOT NULL DEFAULT '' |

    | 29 | admin | localhost | NULL | Query | 0 | starting | show processlist |

    | 32 | admin | localhost | NULL | Sleep | 154 | | NULL |

    +----+-------+-----------+--------+---------+------+---------------------------------+------------------------------------+

    rows in set (0.00 sec)

    • 5)通过上述模拟步骤,我们可以很容易地得出结论,导致DDL语句发生MDL锁等待的原因,就是因为执行INSERT语句的显式事务在报错之后,处于一个既未提交,也未回滚的状态,这个时候,需要应用自行处理这种情况,但如果在真实环境中,作为DBA侧的人员在排查这类问题时,就显得比较尴尬了。当然,如果使用的是自动提交的事务,则不存在这个问题,当执行INSERT语句失败时,事务会自动回滚。
      • 注意:在MySQL 5.7中,超过字段定义长度的语句发生报错,是因为sql_mode参数默认值设置了严格模式,在MySQL 5.6及其之前的版本中,sql_mode参数的默认值是未设置严格模式的,这种情况下,超过字段定义长度的INSERT操作仍然能执行成功,但成功插入数据库中的数据是被截断之后的数据(超长部分被丢弃了)
    • PS:MDL元数据锁的类型有很多种,根据官方手册中对performance_schema.metadata_locks表的LOCK_TYPE字段的描述可得知,一共有9种(INTENTION_EXCLUSIVE、SHARED、SHARED_HIGH_PRIO、SHARED_READ、SHARED_WRITE、SHARED_UPGRADABLE、SHARED_NO_WRITE、SHARED_NO_READ_WRITE、EXCLUSIVE),但,官方手册中并未找到每一种MDL锁的具体含义和发生的场景,关于MDL锁更详细的信息可参考如下这两个链接

    | 作者简介

    罗小波·沃趣科技高级数据库技术专家

    IT从业多年,主要负责MySQL 产品的数据库支撑与售后二线支撑。曾参与版本发布系统、轻量级监控系统、运维管理平台、数据库管理平台的设计与编写,熟悉MySQL体系结构,Innodb存储引擎,喜好专研开源技术,多次在公开场合做过线下线上数据库专题分享,发表过多篇数据库相关的研究文章。

    展开全文
  • 主要介绍了MySQL 5.7中如何定位DDL被阻塞的问题,在MySQL 5.7中,针对MDL,引入了一张新表performance_schema.metadata_locks,该表可对外展示MDL的相关信息,包括其作用对象,类型及持有等待情况。对此问题感兴趣的...
  • 数据库引擎从命令提示符执行MySQL类似的基本DDL,DML,VDL命令 该项目是使用Eclipse IDE for Java开发的。 有关运行应用程序的说明: 从Eclipse: 解压缩从eLearning下载的压缩文件夹。 将文件夹导入Eclipse ...
  • 记一次mysql执行DDL导致锁表

    千次阅读 2020-06-28 10:17:19
    背景 线上某数据库意外发现缺少索引,并且该表的数据量很少,只有几万条记录而已,因此很随意地尝试给该表添加索引。原本预期该表的记录很少...mysql从5.6版本起支持Online DDL,理论上执行DDL语句不会阻塞诸如INSERT、

    背景

    线上某数据库意外发现缺少索引,并且该表的数据量很少,只有几万条记录而已,因此很随意地尝试给该表添加索引。原本预期该表的记录很少,添加索引的耗时应该很短,结果却直接导致该表被锁,所有该表的增删改查操作全部阻塞,继而影响到了线上业务。

    发现锁表后,执行show processlist发现大量线程阻塞,状态显示Waiting for table metadata lock。通过命令终止了DDL线程,该表恢复正常。mysql从5.6版本起支持Online DDL,理论上执行DDL语句不会阻塞诸如INSERTUPDATEDELETE这类DML操作。

    事后排查发现,该表有个持续了3天未提交的事务,正是该事务导致DDL语句执行时锁表。

    在这里插入图片描述

    在这里插入图片描述

    复现

    新建表

    CREATE TABLE `lock_table` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `content` varchar(32) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    

    新建一个会话1,开启事务执行以下命令后不要提交事务

    begin;
    select * from lock_table limit 1;
    

    新建另一个会话2,执行DDL命令,发现DDL语句执行被阻塞

    ALTER TABLE `lock_table` ADD INDEX content_index(content);
    

    此时表被锁定,再新建一个会话3,执行查询语句发现该操作同样被阻塞住

    select id from lock_table limit 1;
    
    -会话1会话2会话3
    步骤1begin;
    步骤2select * from lock_table limit 1;
    步骤3-ALTER TABLE lock_table ADD INDEX content_index(content);
    步骤4--select * from lock_table limit 1;

    原理

    DDL上锁流程

    MySQL Online DDL的改进与应用

    1. prepare阶段:尝试获取MDL排他锁,禁止其他线程读写;
    2. ddl执行阶段:降级成MDL共享锁,允许其他线程读取;
    3. commit阶段:升级成MDL排他锁,禁止其他线程读写;
    4. finish阶段:释放MDL锁;

    DDL导致锁表的原因

    To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table’s structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

    mysql官方文档metadata-locking一节中指出,为了确保事务可序列化,mysql不允许一个会话对在另一会话中未完成的显式或隐式启动的事务中使用的表执行DDL语句。服务器通过获取事务中使用的表上的元数据锁并将这些锁的释放推迟到事务结束之前来实现。表上的元数据锁可防止更改表的结构。这种锁定方法的含义是,一个会话中事务正在使用的表在事务结束之前不能被其他会话在DDL语句中使用。

    mysql对申请MDL锁的操作会形成一个队列,队列中写锁获取优先级高于读锁。一旦出现写锁等待,不但当前操作会被阻塞,同时还会阻塞后续该表的所有操作。由上可知当事务一旦申请到MDL锁后,直到事务执行完才会将锁释放,当长事物或未提交的事务未提交完成时,执行DDL语句会等待MDL排他锁而阻塞,继而阻塞该表的后续其他操作。

    观察MDL锁

    MySQL5.7中的performance_schea库下新增了一张表metadata_locks,可以很方便地查看MDL锁的状态。默认情况下mysql未启动该表,可以通过以下两种方式打开该功能:

    1. 临时启用(mysql实例重启后,恢复默认值)
    UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
    
    1. 永久启用

    修改配置文件,添加以下参数

    [mysqld]
    performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
    

    为了能方便查看到事务以及DDL执行过程中MDL锁的状态,需要延长DDL执行时长,否则会由于DDL执行过快而难以观察MDL锁的状态。可以通过如下语句向测试表中添加200万条测试记录。

    delimiter  //
    CREATE PROCEDURE init_lock_table() 
    BEGIN 
    	DECLARE index_id INT;
    	SET index_id = 0;
    	START TRANSACTION; 
    	WHILE index_id < 2000000 DO
    		insert into lock_table(content) values (CONCAT('content',index_id));
    		SET index_id = index_id + 1;
    	END WHILE;
    	COMMIT; 
    END //
    delimiter ;
    call init_lock_table();
    
    1. 事务获取共享锁

    开启会话1,启用事务执行以下语句,获取MDL共享锁:

    begin;
    select id from lock_table limit 1;
    

    查看MDL锁状态,可见348线程(会话1)获取到测试表的共享锁。

    mysql> select * from performance_schema.metadata_locks;
    +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+
    | OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
    +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+
    | TABLE       | test               | lock_table     |       140633203352496 | SHARED_READ | TRANSACTION   | GRANTED     |        |             348 |              6 |
    | TABLE       | performance_schema | metadata_locks |       140633346571936 | SHARED_READ | TRANSACTION   | GRANTED     |        |             340 |             24 |
    +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+
    
    1. DDL语句获取排它锁

    开启会话2,执行DDL语句,尝试获取MDL排他锁:

    ALTER TABLE `lock_table` ADD INDEX content_index(content);
    

    查看MDL锁状态,可见349线程(会话2)获取到测试表的共享锁,并且在等待测试表的排它锁。由于DML锁已经被会话1的事务占有,会话2只能等待会话1事务结束后释放DML锁,因而该锁状态为PENDING

    mysql> select * from performance_schema.metadata_locks;
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    | OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    | TABLE       | test               | lock_table     |       140633203352496 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             348 |              6 |
    | GLOBAL      | NULL               | NULL           |       140633201296448 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |             349 |              5 |
    | SCHEMA      | test               | NULL           |       140633201296544 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |             349 |              5 |
    | TABLE       | test               | lock_table     |       140633201296352 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |             349 |              5 |
    | TABLE       | test               | lock_table     |       140633202663984 | EXCLUSIVE           | TRANSACTION   | PENDING     |        |             349 |              5 |
    | TABLE       | performance_schema | metadata_locks |       140633346571936 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             340 |             25 |
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    
    1. DDL持有锁降级成共享锁

    会话1执行语句commit;提交事务,令会话1释放MDL共享锁。此时可见会话2获取MDL排它锁成功,然后DDL的排他锁降级为共享锁:

    mysql> select * from performance_schema.metadata_locks;
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    | OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    | GLOBAL      | NULL               | NULL           |       140633201296448 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |             349 |              5 |
    | SCHEMA      | test               | NULL           |       140633201296544 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |             349 |              5 |
    | TABLE       | test               | lock_table     |       140633201296352 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |             349 |              5 |
    | TABLE       | performance_schema | metadata_locks |       140633346571936 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             340 |             26 |
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    

    当DDL持有的MDL锁降级为共享锁后,不再阻塞该表的其他操作,此时会话1再开启事务获取MDL共享锁不会阻塞,锁状态如下,从而实现Online DDL

    mysql> select * from performance_schema.metadata_locks;
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    | OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    | GLOBAL      | NULL               | NULL           |       140633201296448 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |             349 |              5 |
    | SCHEMA      | test               | NULL           |       140633201296544 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |             349 |              5 |
    | TABLE       | test               | lock_table     |       140633201296352 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |             349 |              5 |
    | TABLE       | test               | lock_table     |       140633203352496 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             348 |              9 |
    | TABLE       | performance_schema | metadata_locks |       140633346571936 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             340 |             27 |
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    
    1. DDL持有锁升级为排他锁

    当DDL执行完成时,会再次尝试获取MDL排他锁,如果此时348线程(会话1)的事务未完成而持有MDL共享锁时,349线程(会话2)的DDL操作等待MDL锁继而再一次0阻塞该表的其他操作。

    mysql> select * from performance_schema.metadata_locks;
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    | OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    | GLOBAL      | NULL               | NULL           |       140633201296448 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |        |             349 |              5 |
    | SCHEMA      | test               | NULL           |       140633201296544 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |        |             349 |              5 |
    | TABLE       | test               | lock_table     |       140633201296352 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |        |             349 |              5 |
    | TABLE       | test               | lock_table     |       140633203352496 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             348 |              9 |
    | TABLE       | test               | lock_table     |       140633202744688 | EXCLUSIVE           | TRANSACTION   | PENDING     |        |             349 |              5 |
    | TABLE       | performance_schema | metadata_locks |       140633346571936 | SHARED_READ         | TRANSACTION   | GRANTED     |        |             340 |             28 |
    +-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
    

    解决方案

    解除正在锁表的状态有两种方法:

    方法一

    终止DDL语句,选择业务低峰期或其他时间段执行。

    1. 查询是否锁表
    show OPEN TABLES where in_use > 0;
    
    1. 查询进程
      (如果账号有SUPER权限,则可以看到所有线程,否则,只能看到当前用户的线程)
    show processlist;
    

    或者

    select * from information_schema.processlist where COMMAND != 'Sleep';
    
    1. 杀死进程
    kill 进程ID;
    

    示例:

    查询到DDL会话的进程ID为286,然后通过kill命令杀死该进程终止会话,解决阻塞问题。

    在这里插入图片描述

    kill 286;
    

    方法二

    杀死阻塞DDL语句的会话,但并不建议这么操作,因为无法判断该会话执行了什么操作,武断地终止该会话会造成无法预估的业务异常。

    1. 查看当前的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
    
    1. 查看当前锁定的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
    
    1. 查看当前等锁的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    
    1. 杀死进程
    kill 进程ID;
    

    示例:

    查询到阻塞DDL操作的长事务会话的进程ID为285,然后通过kill命令杀死该进程终止会话,解决阻塞问题。

    在这里插入图片描述

    kill 285;
    

    方法三

    执行DDL语句前,先通过lock_wait_timeout设置好锁超时时间,避免长时间的DML锁等待。当DDL语句等待MDL锁超时时,自动终止当前会话,避免长时间等待锁继而阻塞其他线程。

    示例:

    -- 设置当前会话等待锁超过5秒后,自动终止DDL
    SET lock_wait_timeout=5;
    ALTER TABLE `lock_table` ADD INDEX content_index2(content);
    

    当等待DML锁超时后,DDL终止并且抛出错误:1205 - Lock wait timeout exceeded; try restarting transaction

    总结

    1. 谨慎使用长事务,业务代码中的耗时操作尽量不要在事务中执行;
    2. 执行DDL语句前先查看当前活跃事务,防止有未提交事务或者长事务存在;
    3. DDL语句导致锁表后,先杀掉DDL语句进程,终止锁库减小影响范围,再然后排查问题;
    展开全文
  • Mysql online DDL特性(一)

    万次阅读 多人点赞 2019-03-10 22:53:53
    online DDL是在mysql5.6版本后加入的特性,用于支持DDL执行期间DML语句的并行操作,提高数据库的吞吐量。 online DDL结构简图如下: 由上图可知online DDL大体可以分为3部分: 1、copy(ALGORITHM=COPY)这...

    基础材料:

    centos7.5  mysql 5.7.24


    online DDL是在mysql5.6版本后加入的特性,用于支持DDL执行期间DML语句的并行操作,提高数据库的吞吐量。

    online DDL结构简图如下:

     

    由上图可知online DDL大体可以分为3部分:

    1、copy(ALGORITHM=COPY)这部分是offline的,在DDL执行期间其他DML不能并行,也是5.6版本前的DDL执行方法。其间生成临时表(server层的操作支持所有引擎),用于写入原表修改过的数据,同时在原表路径下会生成临时表的.frm和.ibd文件。在innodb中不支持使用inplace的操作都会自动使用copy方式执行,而MyISAM表只能使用copy方式。

    2、inplace(ALGORITHM=INPLACE)所有操作在innodb引擎层完成,不需要经过临时表的中转。除上图两种特殊索引创建外,其他以inplace方式执行的操作都是online的,执行期间其他DML操作可以并行,其中又以是否重建表又分为两个部分rebuild和no-rebuild。

          rebuild部分涉及表的重建,在原表路径下创建新的.frm和.ibd文件,消耗的IO会较多。期间(原表可以修改)会申请row log空间记录DDL执行期间的DML操作,这部分操作会在DDL提交阶段应用新的表空间中。

          no-rebuild部分由于不涉及表的重建,除创建添加索引,会产生部分二级索引的写入操作外,其余操作均只修改元数据项,即只在原表路径下产生.frm文件,不会申请row log,不会消耗过多的IO,速度通常很快。 

    3、inplace but offline的几种特殊DDL操作,本身是按inplace方式执行,但是执行期间DML语句却不能并行。

    注:如何区分DDL语句是使用了copy方式还是inplace方式,只需要查看语句执行完成输出结果中的 X rows affected,如果X为0则是inplace(online)方式,如果不为0则是copy(offline)方式。


    online DDL可选参数示意图:

    online DDL的两个子选项包括ALGORITHM和LOCK:

    对于ALGORITHM参数使用default默认值即可,不需要强制指定该值,系统会自行判断,优先使用inplace,对于不支持的表或DDL操作使用copy。

    LOCK参数绝大多数情况下也不需要显式指定值,默认值default已经是尽可能允许DML的并行操作了。

    例句如下,参数间使用逗号隔开:

    alter table innodb_test add test int,ALGORITHM=INPLACE,LOCK=DEFAULT;


    inplace(rebuild)的整体执行过程如下:

    准备阶段:

    1、对表加元数据共享升级锁,并升级为排他锁。(此时DML不能并行)

    2、在原表所在的路径下创建.frm和.ibd临时中转文件(no-rebuild除创建二级索引外只创建.frm文件,其中添加二级索引操作最为特殊,该操作属于no-rebuild不会生成.ibd,但实际上对.ibd文件却做了修改,该操作会在参数tmpdir指定路径下生成临时文件,用于存储索引排序结果,然后再合并到.ibd文件中)

    3、申请row log空间,用于存放DDL执行阶段产生的DML操作。(no-rebuild不需要)

    执行阶段:

    1、释放排他锁,保留元数据共享升级锁(此时DML可以并行)。

    2、扫描原表主键以及二级索引的所有数据页,生成 B+ 树,存储到临时文件中;

    3、将所有对原表的DML操作记录在日志文件row log中

    注:如果只修改元数据部分(no-rebuild)该阶段只是修改.frm文件,不需要其他操作,也不需要申请row log

    提交阶段:

    1、升级元数据共享升级锁,产生排他锁锁表(此时DML不能并行)。

    2、重做row log中的内容。(no-rebuild不需要)

    3、重命名原表文件,将临时文件改名为原表文件名,删除原表文件

    4、提交事务,变更完成。

    说明:在DDL期间产生的数据,会按照正常操作一样,写入原表,记redolog、undolog、binlog,并同步到从库去执行,只是额外会记录在row log中,并且写入row log的操作本身也会记录redolog,而在提交阶段才进行row log重做,此阶段会锁表,此时主库(新表空间+row log)和从库(表空间)数据是一致的,在主库DDL操作执行完成并提交,这个DDL才会写入binlog传到从库执行,在从库执行该DDL时,这个DDL对于从库本地来讲仍然是online的,也就是在从库本地直接写入数据是不会阻塞的,也会像主库一样产生row log。但是对于主库同步过来DML,此时会被阻塞,是offline的,DDL是排他锁的在复制线程中也是一样,所以不只会阻塞该表,而是后续所有从主库同步过来的操作(主要是在复制线程并行时会排他,同一时间只有他自己在执行)。所以大表的DDL操作,会造成同步延迟。


     copy的整体执行过程如下:

    1、锁表,期间DML不可并行执行

    2、生成临时表以及临时表文件(.frm .ibd)

    3、拷贝原表数据到临时表

    4、重命名临时表及文件

    5、删除原表及文件

    6、提交事务,释放锁


    online DDL的空间要求:

    由于online DDL执行期间需要创建临时表空间文件用于存储数据,以及申请row log记录DML操作,所以在执行DDL前应该先确认空间上是否满足要求,否则由于空间不够很可能导致操作失败,而进行回滚。

    1、row log空间:row log空间每次申请的大小由 innodb_sort_buffer_size决定,最大值由innodb_online_alter_log_max_size,该值默认为128M,支持动态修改。对于更新频繁的表来讲,如果预计在DDL期间对表的更新操作存储可能超过128M时,需要为本次操作增大该值。当然如果不涉及rebuild操作时,不需要考虑该值。如果提示DB_ONLINE_LOG_TOO_BIG错误,则是由innodb_online_alter_log_max_size空间不足造成的。

    2、索引排序空间:如果DDL操作涉及二级索引的创建,会在MySQL临时目录产生临时排序文件,将中间的排序结果写入文件,最终将内容合并到最终表或索引中,然后自动删除临时排序文件。这个路径默认为mysql全局参数tmpdir指定(默认值为/tmp,如果手动指定了innodb_tmpdir参数的路径,则tmpdir会被覆盖),且不会在原始表的目录中创建临时排序文件。tmpdir需要保证能够容纳要创建的二级索引,临时排序文件最大可能需要的空间等于表中的数据量加上索引否则执行将报错。(官方文档的说明,实际测试200万的表加索引,并未生成临时排序文件,这有点奇怪)

    3、中间表空间:如果DDL操作涉及rebuild表,则会在原表所在目录创建临时表空间文件(以#sql开头),临时表空间大小需要等于原表大小,重建完成后会自动重命名临时表空间,删除原表空间。所以执行rebuild操作时需要保证原表所在路径下有足够空间


    执行DDL语句需要额外注意的是:

    • 如果操作失败,执行回滚操作时可能会影响服务器性能。

    • 长时间运行的联机DDL操作可能导致复制滞后。在从服务器上运行之前,联机DDL操作必须在主服务器上完成运行。此外,在主服务器上同时处理的DML仅在从服务器上的DDL操作完成后才在从服务器上处理。

    展开全文
  • 根据业务需要,需要在存储过程自动创建表 CREATE PROCEDURE parameter_split(in table_name VARCHAR(30),in start_time BIGINT(16),in end_time BIGINT(16)) BEGIN -- 得到时间 DECLARE table_year VARCHAR...
  • 30 6 * * * /u01/dba_scripts/mysql_add_partition/mysql_add_partition_tables.sh 2)脚本 cat /u01/dba_scripts/mysql_add_partition/mysql_add_partition_tables.sh #!/bin/bash ##########################...
  • MySQL DDL记录

    千次阅读 2019-03-14 17:45:31
    DDL(data definition language)数据库定义语言:其实就是我们在创建表的时候用到的一些sql,比如说:CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构,数据类型,表之间的链接和约束等初始化工作上 二...
  • MySQL8.0 Online DDL进一步

    2021-07-25 23:53:31
    binlog同步发生了延迟,导致ddl无法完成 执行过程中,内存占满,CPU 100% ,IO等待 最终导致阻塞读写请求(DML操作阻塞),影响系统的可用性。 所以ddl操作,要考虑这些因素,避开高峰期,操作评估,备手
  • 关于mysql中的DDL,DML,DQL和DCL SQL语言一共分为4大类:数据定义语言DDL,数据操纵语言DML,数据查询语言DQL,数据控制语言。 文章目录关于mysql中的DDL,DML,DQL和DCL1.DDL2.DML3.DQL 1.DDL 1.数据定义语言DDL...
  • Mysql在线DDL工具

    2020-04-14 16:43:23
    在实际生产应用中,经常会有mysql表结构修改的场景,涉及到的表数据量大,如果直接修改,锁表时间过长,影响正常业务。因此,寻求通过在线DDL工具进行表结构修改。 这里选择了常用的2个在线DDL工具gh-ost 和pt-on...
  • 浅谈MySQL Online DDL

    2021-03-02 23:37:55
    Online DDL执行例子 Online DDL性能 例子 总结 Online DDL是什么 在MySQL5.5以及之前的版本,通常更改数据表结构操作(DDL)会阻塞对表数据的增删改操作(DML) MySQL5.6提供Online DDL之后可支持DDL与DML操作同时执行,...
  • mysql online ddl原理

    2017-05-09 17:24:24
    背景在公司一次为配合数据组做数据抽取,给业务数据表添加索引,索引字段主要为:updateTime 因为业务数据比较多,大概有3千多万的数据,担心在做alter table add...dba的日常工作肯定有一项是ddl变更,ddl变更会锁表
  • MySql online DDL 原理

    千次阅读 2016-01-05 22:48:57
    背景 dba的日常工作肯定有一项是ddl变更,...因此mysql 5.6的online ddl特性是dba们最期待的新特性,这个特性解决了执行ddl锁表的问题,保证了在进行表变更时,不会堵塞线上业务读写,保障在变更时,库依然能正常对外
  • mysql DDL语言执行过程

    2016-03-01 17:02:17
    由于mysql在线ddl(加字段、加索引等修改表结构之类的操作)过程如下:  A.对表加锁(表此时只读) B.复制原表物理结构 C.修改表的物理结构 D.把原表数据导入中间表中,数据同步完后,锁定中间表,并删除原表 E.rename...
  • mysqlDDL操作

    万次阅读 2016-04-17 14:59:35
    mysql常用的DDL语句探究
  • MySQL Online DDL工具

    2018-12-19 14:46:00
    MySQL在线表结构变更工具 MySQL的大表表结构变更常用的解决方案无外乎三种: ...三则是升级MySQL到5.6/5.7通过官方Online DDL实现部分变更。 然而,引入触发器带来的锁竞争问题,主备切换带来的附加成本以及Online DD...
  • MySQL 8.0 新特性之原子 DDL

    千次阅读 2018-12-26 16:17:58
    MySQL 8.0 开始支持原子性的数据定义语言(DDL),也称为原子 DDL。一个原子 DDL 语句将相关的数据字典更新、存储引擎操作以及写入二进制日志组合成单一的原子事务。当事务正在处理时出现服务器故障,该事务可能被...
  • MySQL使用DDL语句创建表

    千次阅读 2019-06-30 21:35:57
    使用DDL语句创建表 知识要点 CREATE TABLE语句 列的数据类型 添加数据类型选项 根据已有的表来创建新表 列选项 表选项 约束 对表名和列名使用反引号 CREATE TABLE语句 对于一个表有列和行,对于列有标题和数据...
  • mysql执行DDL语句的时候提示错误: [Err] 4644 – [1065507aa5d0c000][10.0.85.135:3306][test]ERR-CODE: [TDDL-4644][ERR_PENDING_DDL_JOB_EXISTS] Another DDL job ‘1181437929186181120’ with operation ...
  • MySQL基础篇之DDL语句

    千次阅读 2018-01-10 11:42:38
    “Query OK”表示:所有的DDL和DML(不包括SELECT)操作执行成功后都显示“Query OK”,这里理解为执行成功就可以了,这个是MySQL的一个特点。“1 row affected”表示操作只影响了数据库中一行的记录,“0.00sec”则...
  • MySQL DDL即时INSTANT DDL即时Instant 功能自MySQL 8.0.12版本引入。在准备和执行期间,不会在表上采用独占元数据锁,并且表数据不受影响,从而使操作立即生效。允许并发DML。 那先看看官网描述作用: 添加一个列...
  • MySQL Online DDL,还是要谨慎

    千次阅读 2019-07-28 07:30:00
    导读MySQL的Online DDL长期饱受诟病,8.0之后有没有好一些呢...本文重点讨论常见的几种Online DDL需求:增加新列(ADD COLUMN)修改列定...
  • MySQL亿级数据表DDL解决方案及实战 背景   随着业务的发展,用户对系统需求变得越来越多,这就要求系统能够快速更新迭代以满足业务需求,通常系统版本发布时,都要先执行数据库的DDL变更,包括创建表、添加...
  • 执行 SQL 代码“delete from customer where cName=’ 刘一鸣 ‘”,会出现删除异常。 打开从表 orders 的设计视图,单击“外键”,出现 orders 表外键编辑窗口,下拉选中“删除时”的状态值为“SET NULL”。 如果...
  • MySQL online DDL 概述

    千次阅读 2018-08-03 17:26:45
    业界开发了一系列Online DDL的工具,包括MySQL官方也在不断的努力增强online DDL的便捷性。 1.Percona公司开发的pt-online-schema-change工具: 站点:https://www.percona.com/software/data...
  • 那么对于MySQL数据库,哪些表结构操作会“锁表”,会导致读写表中数据操作不能并发执行呢? 背景知识 DML( data manipulation language),数据操作语句,是中INSERT、UPDATE、DELETE、SELECT等表数据操作的语句。 ...
  • MySQL事务中DDL语句的隐式提交

    千次阅读 2019-04-02 22:28:08
    其实是这样的,当我们执行DDL语句之后,系统会自动的执行一次commit,连带着前面的添加数据一起完成了提交。所以当我们再次rollback的时候,其实执行的是一个空的事务。表面上看到的是回滚失败了,但是其实并不是...
  • MySQL三种DDL方式的binlog区别

    千次阅读 2020-07-06 02:36:08
    MySQL三种DDL方式的binlog记录形式原生ddlpt-online-schema-change执行ddl阿里云dms无锁表结构变更 测试环境: MySQL [ddltest]> show create table sbtest1\G *************************** 1. row *************...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 68,162
精华内容 27,264
关键字:

mysql无法执行ddl

mysql 订阅