• SQL优化案例(二)高并发下的简单SQL占尽CPU资源

    背景

    线上数据库突然从某一刻开始CPU资源告警,如下:

    a

    和业务核对上线发布情况和系统调整后,发现可能不是由上线代码改动引起的。

    登录线上服务器后,执行  show processlist 发现比平时的连接数要高很多,而且很多都在执行这一条SQL:

    a

    开始其实我并没有怀疑这条SQL,因为它确实很简单,没有 order by ,也没有 group by。 所以第一时间是怀疑有大事务或者死锁发生,但是经过查看发现并不是:

    紧接着我怀疑是不是有其他的大SQL 与 这个高并发 SQL 有冲突,于是翻找了一下,发现是有两个SQL比较慢,并且耗费资源,但是量不大,应该难以这么快降数据库拖垮。

    于是还是将目标瞄准了这个简单SQL,下面是它的一些基本信息:

    表结构与索引如下:

    表行数如下:

    SQL 的执行计划如下:

    在主库上执行这条SQL只需要0.22s(我们的主库是读写分离,所以当时没有问题,可以用来测试):

    profile 分析结果如下:

    解决

    其实也是因为运气好,出问题时RDS实例没有各种业务模块的SQL在并发进行,所以我在管理控制台,将所有的读请求量转到一台空闲的备份RDS上。 发现这台新的RDS又立马被这条SQL夯满了,并且因为没有CPU资源所以很多查询都被阻塞的很慢 。

    值得怀疑的有两点:

    1、就是profile 中 Sending data 环节的CPU资源占用还是有点的多的,虽然是0.17 左右,但是成百上千个同时过来的话也不算小。

    2、就是Explain结果中的 rows 有18w行,而 filtered 字段值只有1.11,也就意味着这条SQL每执行一次要扫描 18w行,但是还要进行过滤,过滤的比率高达 99% 左右,才返回。

    从上面的分析结果来看,所以做出如下修改:

    然后再看执行计划和profile:

    如上,加的索引立马被用到了,扫描行数变成了 2406 ,filtered 变成了 5%,再看下Profile:

    如上,变化果然很明显,Sending Data 环节的CPU消耗立马减少了好多。

    然后加了此索引后,线上也立马恢复正常了。

    总结

    有时候一个看似简单的SQL,在一定的量级下也会对数据库造成毁灭性的打击。 这件事又一次侧面强调了索引的重要性。

    另外再出现类似的情况时,可以结合 Explain 和 Profile 来综合分析。

     

     

     

  • MySQL-FAQ 线上数据库大量锁等待超时回滚处理

    背景

    接到开发同学反馈,线上大量事务出现锁等待超时回滚报错,如下:

    数据库相关信息:

    处理思路与解决

    根据报错,我们知道这张表上发生了大量的锁争用,导致部分锁等待事务被回滚。

    思路1: 根据 show processlist 结果分析具体有哪些对该表的操作和锁等待,并且从Time 值判断是否有大连接。

    show processlist 看到的结果太杂太多,并且是时刻变化的,我发现了很多对这个表的操作都持续十几秒,甚至几十秒,但是并没有收获太有用的信息,我还是不知道哪些类型的事务会彼此阻塞。并且没有 Time 值特别大的连接存在:

    a

    我只能知道这个SQL是阻塞的成员之一,但不知道它属于哪个事务,也不知道它和其他的哪个事务阻塞。

    思路2:利用 show engine innodb status 查看锁等待和死锁记录。

    show engine innodb status 查看的结果也不太乐观,发现死锁信息里面记录的最近一次死锁记录还是几天前的,和这次应该关系不大,而锁等待信息太多太杂,有大量的物理信息,看的人眼花缭乱,不太好定位:

    a

    思路3:利用 information_schema 中的三张事务表来查看信息。

    不得不说在查看事务和锁信息的时候,用这个方式确实会方便并且快上很多,我可以立刻知道哪些事务之间有阻塞,并且他们属于哪个Thread 以及 他们正在执行的语句是啥 ?

    关于这三张表的使用方式见之前的博文:MySQL数据库锁(三)

    用此SQL在 information_schema 下执行,可以快的帮助我们定位到事务与锁的相关信息。但是在使用时有两个关键点需要注意:

    注意1:此SQL中查询出的阻塞事务正在执行的SQL并不一定是阻塞的SQL 

    上述方式读到的SQL只是阻塞的事务对应的MySQL Thread 正在执行的SQL,完全有可能是它的上一条 SQL 与另外一个 事务的某一条SQL锁阻塞了,而现在这条SQL并没有参与锁阻塞 。

    注意2:此SQL 查询出的 MySQL Thread 对应的 Time 值不一定就很大 ! 

    这里很重要,即使一个长事务对应的MySQL Thread 也不一定会有很大的 Time 值,因为我们在一个MySQL Thread 连接中,这个 Time 值会随着SQL的执行不断被清0开始重新计数! 比如一个MySQL Thread 连接开启了一个长事务,这个事务可能包含N多条SQL语句,每执行一条SQL,这个Time值就会变成0,直到它执行下一条SQL。

    所以也就意味着我们再定位问题时不能通过MySQL Thread的Time值来确定它下面的事务执行的时间,判断它是不是长事务,而应该根据 innodb_trx 表中的 trx_started 或 trx_wait_started 来判断是否是长事务。

    问题解决:

    最后,我发现有几个事务存活了好久:

    a

    从 information_schema.processlist 表中查看了这几个事务对应的MySQL Thread ,发现它不断的有新的SQL执行,所以Time值并不大,所以我 show processlist 的时候没有发现异常。

    最后我将这几个长事务对应的 MySQL Thread 给 kill 掉,数据库立刻恢复正常了。

    开发同学进一步查验代码,发现是在代码异常处理中没有添加事务的回滚或者提交策略导致的 ~

    总结

    其实上述的案例是开发同学在代码的异常处理中,没有显示的加上事务回滚或者其他处理手段。 导致了在一个事务中,执行某条SQL失败时,此事务没有commit 或者 rollback,形成了“僵尸事务” 或者也可以称为长事务。 僵尸事务它占据着锁,导致与新的事务不断的产生锁争用。 我们在 MySQL数据库锁(三)中讲过,MySQL 默认的Innodb_lock_wait_timeout  是 50 s,当两个事务发生锁争用并且超过这个时间后,它会选择小的事务进行SQL级别的回滚。 注意,这里是SQL级别的,而不是事务级别的。这就导致了上面的现象,事务中的某个SQL异常后,事务并没有自动回滚,而是依然存在着,成了僵尸事务。

    如果上面的例子中两个事务不是锁争用,而是死锁,那反而不会有这个问题,小的事务直接被执行事务级别的回滚了。

     

  • MySQL 元数据锁浅析(一)

    背景

    作为DBA或者数据库运维,我们经常会经历在线上数据库操作DDL而导致SHOW PROCESSLIST结果中出现大量 “Waiting for table metadata lock” 的连接并致使整个数据库的连接数迅速增长。所以我们必须对元数据锁有清晰的认识和了解才能放心的去管理和操作。

    先把我们的疑问和需要搞明白的问题抛出来:

    1. 元数据锁的存在意义是什么 ? 如果数据库没有它会怎么样 ?
    2. 什么情况下会产生元数据锁 ?
    3. 元数据锁的原理是怎样的 ?  既然是一种锁,那么它的锁定对象、锁的持有时间、锁的类型都是怎样的 ?
    4. 元数据锁会导致什么问题 ? 导致了以后我们该如何处理 ?

    带着这四个问题,我们去一探究竟吧。

    元数据锁的存在意义

    metadata lock即元数据锁是在 MySQL 5.5 版本引入的。所谓元数据,即 db,table,function,procedure,trigger,event等。顾名思义,metadata lock主要为了保证这些元数据的一致性,用于处理不同线程操作同一数据对象的同步与互斥问题。

    MySQL引入MDL主要是为了解决两个问题:

    1. DDL类型的事务隔离问题:

    比如在可重复隔离级别下,会话A在2次查询期间,会话B对表结构做了修改,两次查询结果就会不一致,无法满足可重复读的要求;

    注:这里的概念不要和MVCC以及事务的隔离级别混淆了,维度不一样。我们平常在事务隔离级别中提高的一致性主要指的是MySQL在整个实例的宏观角度去看待,不同的隔离界别对应不同的现象和一致性问题。而MVCC则是InnoDB为了提高并发性能做的多版本并发控制。具体可以参考之前的这篇博文: http://www.foreverlakers.com/2015/11/mysql-隔离级别/

    我们这里说的一致性,是DDL类型的事务(涉及到metadata变更的事务) 与其他事务之间的一致性问题。

    2. Binlog主从同步bug:

    这是MySQL官网上的一个很著名的bug: bug989 大概的意思如下:

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2018-01-19-%e4%b8%8b%e5%8d%886-54-13
    如上,当session1 在正常执行的时候,有一个session2 将对应的表drop了,那么在binlog中会形成如下的顺序:

    很显然备库执行binlog时会先删除表 t1,然后执行insert 会报1032 error,导致复制中断。

    多说无益,我们来试试吧:

    首先,我们找一个MySQL5.1 的测试环境:

    然后做如下测试:

    session 1 session 2
    mysql> begin ;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from t where id = 12 ;
    +——+——+
    | id   | name |
    +——+——+
    |   12 | wang |
    +——+——+
    1 row in set (0.01 sec)
    mysql> begin ;
    Query OK, 0 rows affected (0.00 sec)
    mysql> alter table t add column age int ;
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    mysql> select * from t where id = 12 ;
    +——+——+——+
    | id   | name | age  |
    +——+——+——+
    |   12 | wang | NULL |
    +——+——+——+
    1 row in set (0.00 sec)
    mysql> drop table t;
    Query OK, 0 rows affected (0.00 sec)
    mysql> commit ;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from t where id = 45 ;
    ERROR 1146 (42S02): Table ‘test.t’ doesn’t exist
    mysql> commit ;
    Query OK, 0 rows affected (0.00 sec)

    如上,我们发现session 1中的三次查询竟然得到了不一样的结果。 这要是在生产环境中岂不都乱套了吗 ?

    我们再找个 5.5 版本的数据库试试:

    然后执行如下操作:

    session 1 session 2 session 3
    mysql> select * from t order by id ;
    +——+——–+
    | id   | name   |
    +——+——–+
    |    1 | james  |
    |    6 | kobe   |
    |   78 | bryant |
    +——+——–+
    3 rows in set (0.00 sec)
     # 注意,这里并没有加 begin
    mysql> alter table t add column age int ;

    阻塞住了

    mysql> show processlist;
    +—-+——+———–+——+———+——+———————————+———————————-+
    | Id | User | Host      | db   | Command | Time | State                           | Info                             |
    +—-+——+———–+——+———+——+———————————+———————————-+
    |  1 | root | localhost | test | Query   |    0 | NULL                            | show processlist                 |
    |  2 | root | localhost | test | Query   |   16 | Waiting for table metadata lock | alter table t add column age int |
    +—-+——+———–+——+———+——+———————————+———————————-+
    2 rows in set (0.00 sec)明显能看到这个alter 被阻塞了
    # 注意:这里并没有begin

    mysql> select * from t;

    后来的查询也被阻塞住了

    mysql> select * from t ;
    +——+——–+
    | id   | name   |
    +——+——–+
    |    6 | kobe   |
    |   78 | bryant |
    |    1 | james  |
    +——+——–+
    3 rows in set (0.00 sec)# 它本身还是可以正常读取的
    mysql> show processlist;
    +—-+——+———–+——+———+——+———————————+———————————-+
    | Id | User | Host      | db   | Command | Time | State                           | Info                             |
    +—-+——+———–+——+———+——+———————————+———————————-+
    |  1 | root | localhost | test | Query   |    0 | NULL                            | show processlist                 |
    |  2 | root | localhost | test | Query   |   66 | Waiting for table metadata lock | alter table t add column age int |
    |  3 | root | localhost | test | Query   |    4 | Waiting for table metadata lock | select * from t                  |
    +—-+——+———–+——+———+——+———————————+———————————-+
    3 rows in set (0.00 sec)
    mysql> commit ;
    Query OK, 0 rows affected (0.00 sec)
    同一时间,卡在这里的alter执行通过了,花了这么长时间是因为我去吃了个饭: mysql> alter table t add column age int ;

    Query OK, 3 rows affected (41 min 53.41 sec)
    Records: 3  Duplicates: 0  Warnings: 0

     

    同一时间,这个select也执行通过了:mysql> select * from t;

    +——+——–+——+
    | id   | name   | age  |
    +——+——–+——+
    |    6 | kobe   | NULL |
    |   78 | bryant | NULL |
    |    1 | james  | NULL |
    +——+——–+——+
    3 rows in set (40 min 51.34 sec)

    注意:上面的session2 和 session 3 并没有添加 begin 事务关键字,所以session 1提交之后,session 3 可以执行完后就直接隐性的提交了,相当于session3 这个整个事务就完了,所以 session 2 才可以跟着执行完,否则依旧会阻塞 。这个会在 MySQL 元数据锁浅析(二)中进一步解释。

    如上,我们看到在5.5 MDL拉长了生命周期,只要事务还在,MDL就在,由于事务持有MDL锁,其他DDL在事务期间都被阻塞了,show processlist 会发现大量的 Waiting for table metadata lock 信息。

    这时候就可以解释我们或许曾经都遇到过得情况,在线上并发比较高的热表上执行DDL时,有时候会引起连接数突然暴增,从而使业务受到影响。

    写到这里我心里不禁有几个疑问:

    1. 这session 2 究竟是被阻塞在哪个阶段呢,因为我知道 alter 操作其实也是分为好几个阶段的 ?
    2. 如果这时候连接数暴增了,而刚好session 1 的事务还需要一段时间才能执行完,我们该怎么处理呢 ?

    关于第一个问题,其实简单,试一下不就知道了吗 ? 我们打开profile重新执行一遍上面的操作得出结果:

    如上,很显然是在rename 结果表时卡住了。

    至此,问题一已经得到解答,问题2 我们留在后面与MDL的原理与解决方案一并表述。

    咦 ? 不对啊,如果是卡在了rename阶段,那么试想这么一种情况:MySQL5.6之后引入了 Online DDL (具体请务必参考之前的这篇博文),所以有些DDL操作不需要创建临时表了,那么是不是意味着就不会出现上述的锁表情况了 ?

    我们再找个MySQL 5.6 的数据库:

    然后做如下操作:

    session 1 session 2
    mysql> begin ;
    Query OK, 0 rows affected (0.00 sec)
    mysql> select * from t order by id ;
    +——+——–+
    | id   | name   |
    +——+——–+
    |    1 | james  |
    |    6 | kobe   |
    |   78 | bryant |
    +——+——–+
    3 rows in set (0.00 sec)
    mysql> begin ;
    Query OK, 0 rows affected (0.00 sec)
    mysql> alter table t add index id(id) ;

    阻塞住了

    mysql> commit ;
    Query OK, 0 rows affected (0.00 sec)
    同时,这边也执行完毕了。

    发现还是被阻塞了,我们看下是阻塞在哪里了:

    奇怪,被阻塞在 after create 操作后的元数据锁等待了。这是啥意思? 如果想知道每个细小步骤的具体过程可以执行 show profile all for query 语句来查看每个步骤在源码中调用的函数,并且还能看到具体的源码位置:

    额,尴尬的是虽然知道源码的位置,但是我看了一会发现并没有看懂。 不过其实从原理方面也是可以解释的,Online DDL 的主要作用其实是针对DML的,它是希望在执行DDL的过程中,不影响后来的DML执行,而上面那个例子是先一个事务执行DML,再去执行Online DDL,为了保证事务一致性它肯定为阻塞。而且 Online DDL 并不是在整个过程中完全不加任何锁,只不过很多时候它的加锁和措施比之前更对保守,对并发性能更好。

    上面的这个问题解释的还是有些牵强,但是水平有限,暂时难以再深入了,后续等研究多了,再进一步补充吧。

    到此为止,我想我们大概知道了元数据锁的存在意义,以及如果没有它会造成的后果。但同样的我们也留下了一些问题,所以我们进一步去了解它。

    元数据锁的类型和形成的条件

    在将这一小节之前,建议先看一下之前的MySQL锁系列文章,对MySQL的锁本身有一些大概的了解,然后再继续一起探究元数据锁。

    metadata lock也是一种锁,每个metadata lock都会定义锁住的对象,每个锁都有定义的持有时间和锁的类型。

    在 MySQL5.6 中,MySQL有9中MDL锁类型,我们可以在MySQL的源代码中看到如下两个枚举类型的结构体,第一个是 enum_mdl_type 定义了9种 MDL 类型,第二个是 enum_mdl_duration 定义了MDL锁持有的时间,我们一起看下:

    如上,9 种MDL 兼有注释,我们可以根据这些注释大概就知道每种MDL锁的作用。从上面的注释我们可以看到MDL锁可以作用于不同的对象和范围

    MDL锁属性 锁含义 锁的作用域:范围/对象,以及我们可以在show processlist中看到的状态
    GLOBAL 全局锁 范围              Waiting for global read lock
    COMMIT 提交保护锁 范围              Waiting for commit lock
    SCHEMA 库锁 对象              Waiting for schema metadata lock
    TABLE 表锁 对象              Waiting for table metadata lock
    FUNCTION 函数锁 对象              Waiting for stored function metadata lock
    PROCEDURE 存储过程锁 对象              Waiting for stored procedure metadata lock
    TRIGGER 触发器锁 对象              Waiting for trigger metadata lock
    EVENT 事件锁 对象              Waiting for event metadata lock

    以及MDL锁的持续时间:

    属性 含义
    MDL_STATEMENT 从语句开始执行时获取,到语句执行结束时释放。
    MDL_TRANSACTION 在一个事务中涉及所有表获取MDL,一直到事务commit或者rollback(线程中终清理)才释放。
    MDL_EXPLICIT 需要MDL_context::release_lock()显式释放。
    语句或者事务结束,也仍然持有,如
    Lock table, flush .. with lock语句等。

    上面的源码注释我们可能看的不是很清晰,所以结合上面两表进行整理一下:

    下表摘自:【MySQL】MetaData Lock 之二

    属性
    含义
    示例:  (作用对象,持锁时长、锁类型)
    MDL_INTENTION_EXCLUSIVE(IX)
    意向排他锁,大部分的操作都会都会获取MDL意向锁,
    然后再获取更强级别的MDL锁。
    insert into t1 values(3,’abcde’);
    (SCHEMA,MDL_TRANSACTION,MDL_INTENTION_EXCLUSIVE)
    MDL_SHARED(S)
    只访问元数据 比如表结构,不访问数据。
    set golbal_read_only =on;
    (GLOBAL,MDL_EXPLICIT,MDL_SHARED)
    MDL_SHARED_HIGH_PRIO(SH)
    用于访问information_scheam表,不涉及数据。
    select * from information_schema.tables;
    show create table xx; desc xxx;
    (TABLE,MDL_TRANSACTION,MDL_SHARED_HIGH_PRIO)
    MDL_SHARED_READ(SR)
    访问表结构并且读表数据
    select * from t1; lock table t1 read;
    (TABLE,MDL_TRANSACTION,MDL_SHARE_READ)
    MDL_SHARED_WRITE(SW)
    访问表结构并且写表数据
    insert/update/delete/select .. for update
    (TABLE,MDL_TRANSACTION,MDL_SHARE_WRITE)
    MDL_SHARED_UPGRADABLE(SU)
    是mysql5.6引入的新的metadata lock,
    在alter table/create index/drop index会加该锁;可以说是为了online ddl才引入的。特点是允许DML,防止DDL;
    (TABLE,MDL_TRANSACTION,MDL_SHARED_UPGRADABLE)
    MDL_SHARED_NO_WRITE(SNW)
    可升级锁,访问表结构并且读写表数据,并且禁止其它事务写。
    alter table t1 modify c bigint;(非onlineddl)
    (TABLE,MDL_TRANSACTION,MDL_SHARED_NO_WRITE)
    MDL_SHARED_NO_READ_WRITE(SNRW)
    可升级锁,访问表结构并且读写表数据,并且禁止其它事务读写。
    lock table t1 write;
    (TABLE,MDL_TRANSACTION,MDL_SHARED_NO_READ_WRITE
    MDL_EXCLUSIVE(X)
    防止其他线程读写元数据
    CREATE/DROP/RENAME TABLE,其他online DDL在rename阶段也持有X锁
    (TABLE,MDL_TRANSACTION,MDL_EXCLUSIVE)

    MDL 锁的兼容性矩阵(下图摘自 http://www.cnblogs.com/cchust/p/4303929.html):

    yy

    这样的表格摆在这里大家可能一下子觉得有点懵,我们与实际SQL类型场景结合起来就好理解了,我们描述几种典型语句的加(释放)锁流程:

    Select语句操作MDL锁流程:

    1. Opening tables阶段,加共享锁
      a)   加MDL_INTENTION_EXCLUSIVE锁
      b)   加MDL_SHARED_READ锁
    2. 事务提交阶段,释放MDL锁
      a)   释放MDL_INTENTION_EXCLUSIVE锁
      b)   释放MDL_SHARED_READ锁

    DML语句操作MDL锁流程:

    1. Opening tables 阶段,加共享锁
      a)   加MDL_INTENTION_EXCLUSIVE锁
      b)   加MDL_SHARED_WRITE锁
    2. 事务提交阶段,释放MDL锁
      a)   释放MDL_INTENTION_EXCLUSIVE锁
      b)   释放MDL_SHARED_WRITE锁

    Alter操作MDL锁流程:

    1. Opening tables阶段,加共享锁
      a)   加MDL_INTENTION_EXCLUSIVE锁
      b)   加MDL_SHARED_UPGRADABLE锁,升级到MDL_SHARED_NO_WRITE锁
    2. 操作数据,copy data,流程如下:
      a)   创建临时表tmp,重定义tmp为修改后的表结构
      b)   从原表读取数据插入到tmp表
    3. 将MDL_SHARED_NO_WRITE读锁升级到MDL_EXCLUSIVE锁
      a)   删除原表,将tmp重命名为原表名
    4. 事务提交阶段,释放MDL锁
      a)   释放MDL_INTENTION_EXCLUSIVE锁
      b)   释放MDL_EXCLUSIVE锁

    上述描述的流程中,涉及到的Opening tables阶段、事务提交阶段等,如果你不是很了解的话,可以打开 profile 来查看每个操作都是由那些细小操作组成的。

    接下来,我们顺着上面所述,举几个典型例子,便于理解:

    典例1: DDL 与 Select 是否会相互阻塞

    首先,我们简单看下在 MySQL5.6 下的select和DDL语句的具体操作:

    当先执行select语句时,只要 select 语句在获取MDL_SHARED_READ锁之前,alter 没有执行到 rename阶段,那么 select 获取 MDL_SHARED_READ 锁成功,后续alter执行到rename阶段,请求MDL_EXCLUSIVE锁时,就会被阻塞(看看我们上面的矩阵,MDL_SHARED_READ和MDL_EXCLUSIVE是相互阻塞的)。

    而当先执行DDL语句时, rename 阶段时会持有MDL_EXCLUSIVE锁,但由于这个过程时间非常短(大头都在copy数据阶段),并且是alter的最后一个阶段,所以即使这时有select 语句过来,基本也感觉不到 alter 会阻塞select语句。

    而最常见的情况是:最开始的时候一个大的查询或者大的事务对表进行 select 操作时,来了一个alter操作,这时alter操作被阻塞了。那么在此之后,若再有select查询或DML进来,都会被堵住,就会出现 thread running 连接状态暴增的情况。

    最糟糕的情况是,在高并发场景下,同一时刻有N个Thread在对表进行select,这时候突然来个 alter 进行元数据锁,然后后面的所有select 也会阻塞。 这时候在处理时,可能需要酌情将之前的N个Thread都干掉才行。

    注:测试后发现可能需要将后面来的几个select也kill掉才行 ~

    典例2: DDL 与 DML 是否会相互阻塞

    同样的,我们简单看下在 MySQL5.6 下的DML和DDL语句的具体操作:

    alter 在 opening 阶段会将锁升级到 MDL_SHARED_NO_WRITE,rename阶段再将升级为MDL_EXCLUSIVE,由于MDL_SHARED_NO_WRITE与MDL_SHARED_WRITE互斥,所以先执行alter或先执行DML语句,都会导致语句阻塞在opening tables阶段。

    同理的,我们也可以很容易推断出由于 MDL_SHARED_WRITE 与 MDL_SHARED_READ 兼容,所以SELECT 语句与 DML 语句不会因为MDL而导致等待的情况。

    参考

    http://www.cnblogs.com/cchust/p/4303929.html

    https://yq.aliyun.com/articles/282311?spm=a2c4e.11163080.searchblog.75.6e265a7cuGR76Z

    https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html

  • MySQL 元数据锁浅析(二)

    MDL 的处理与响应

    上面说了那么多,我们对MDL的原理有了大概的了解,所以要扩展到实践中,如何处理MDL导致的问题呢 ?

    从运维的角度来讲,或许我们第一时间考虑的是对MDL的监控,有了监控才能第一时间上手处理问题。

    从MySQL5.7.3开始,便可以通过 performance_schema.metadata_locks 表来了解元数据锁的相关信息:

    • 哪些会话拥有哪些元数据锁
    • 哪些会话正在等待元数据锁
    • 哪些请求由于死锁被杀掉,或者锁等待超时而被丢弃

    此表是只读的,默认不开启此统计功能,如果想要控制可用如下方法:

    在配置文件中配置生效:


    在正在运行的MySQL中配置:

    重要字段含义(具体可参考对应的官方文档):

     此表可以方便我们定位和查询MDL相关信息。  而在MDL的应用管理中,我想我们最常见的就是因为在线执行DDL导致数据库连接数暴增的情况吧(连接数的监控告警是基本的原则,不要告诉我你没有啊)。 我们来测试验证一下,这种情况的处理:

    数据库的基础环境如下:

    测试如下:

    session1 session2 session3
    begin
    mysql> select * from test.t3 ;
    +—-+———–+
    | id | name      |
    +—-+———–+
    |  1 | 000000    |
    |  2 | 888888    |
    |  3 | gggggg    |
    |  4 | 444444    |
    |  5 | ccccccc   |
    |  6 | 666666    |
    |  7 | hehehehe  |
    |  8 | sssssss   |
    |  9 | bbbbbbbbb |
    | 11 | nnnnnn    |
    | 12 | lkjhgfd   |
    | 13 | hhhh      |
    | 15 | pppppp    |
    +—-+———–+
    13 rows in set (0.00 sec)
    mysql> begin ;
    Query OK, 0 rows affected (0.00 sec)
    # 阻塞了

    mysql> alter table test.t3 add column age int ;

    mysql> begin ;
    Query OK, 0 rows affected (0.00 sec)
    # 阻塞了

    mysql> select * from test.t3 where id > 3;

    如上,session1 事务在表上进行了一个读操作,随后session2 上事务起了一个DDL操作,加字段。 然后session3 上事务去读表,也被阻塞了。 这时候,如果再来100个连接对这个表查询,都会阻塞!

    当前 processlist 信息与 Metadata lock 锁信息如下:

    如上,我们可以从表中得到对应的元数据锁的信息。 当发生这种情况的时候,我们可能需要作出选择,往往是 终止掉这个 151 号操作,让其他操作不再阻塞,我们试试,下面的表格紧接着上面的表格:

    # 将session1提交

    mysql> commit ;
    Query OK, 0 rows affected (0.00 sec)

    # 但是session 2 这里依旧阻塞着 # 在session 1 提交之后,session 3 这边瞬间通了

    +—-+———–+
    | Id | name      |
    +—-+———–+
    |  4 | 444444    |
    |  5 | ccccccc   |
    |  6 | 666666    |
    |  7 | hehehehe  |
    |  8 | sssssss   |
    |  9 | bbbbbbbbb |
    | 11 | nnnnnn    |
    | 12 | lkjhgfd   |
    | 13 | hhhh      |
    | 15 | pppppp    |
    +—-+———–+
    10 rows in set (1 min 13.01 sec)

    嗯? 情况貌似和我们想象的又有出入啊,本来以为 session 3 是在session 2 之后开始的,session 1 提交之后还以为senssion 2 会紧接着执行完,然后 session 3 也是一样,但貌似不是啊,session 2 的DDL操作竟然被 session 3 又阻塞了。

    这样的话岂不是说如果后续再来一百个事务查询这个表阻塞后,我得把这一百多个事务对应的连接都杀掉(或commit提交后)才能让这个session 2 这个 DDL顺利执行完吗 ?

    其实这个问题要辩证的去看待,首先,我们要清楚 MDL 锁的持有对象是 事务。 也就是说,事务不提交,它持有的MDL锁就不会释放,则他们的阻塞关系就还在。 大多数时候,在一般的生产环境中,很多在这种情况下被阻塞的连接不是复杂事务,往往是只包含一个查询的简单事务(就好比在MySQL 元数据锁浅析(一)里面的那个例子,后来的session新连接,都没有begin关键字,轮到它的时候,执行完就自动隐式提交了)。 在这种情况下,我们只要干掉session1,后续的那些自动提交之后,Alter 自然也就可以执行了。而如果出问题的场景确实是很多复杂事务的阻塞的话,那就比较麻烦了,得综合评估是否可以干掉那些事务的连接,如果是主库,可以考虑干掉alter这个连接,如果是从库,那还不能轻易动它,否则可能导致主从不一致。

    而且更坑的是,在高并发场景下,虽然阻塞alter 的select 在逐渐完成,但是也有新的 select 再加入进来,这样就变成了我们之前遇到的那种一个 alter 长时间阻塞整个数据库。紧急情况下只好先无差别 kill 掉所有select 查询来尝试释放元数据锁。

    我们来看看上面表格中这个情况下的 metadata_lock 表的信息:

    如上,session 2 和 session 3 还是互相阻塞,看看我们之前的那个表,两个处于 PENDING 状态的锁 SHARED_READ 和 EXCLUSIVE 锁是互相阻塞的。

    我们接着测试session3提交之后的情况:

    session1 session2 session3
    # 同一时间这边也通了,执行完毕
    Query OK, 0 rows affected (2 min 16.38 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    # session 3 提交

    mysql> commit ;
    Query OK, 0 rows affected (0.00 sec)

    这时候再查询metadata_lock表就空了:

    也就是说这个表只记录实时的元数据锁,没有历史的记录。

    在实际的生产环境高并发下,我们可能会发现这个 innodb_trx 表中有大量的事务,执行 show processlist 时会发现几百上千个连接刷屏,你可能没办法判断哪些事务才是持有MDL并阻塞了alter操作的那个操作。

    那么这时候,建议你选择processlist表中TIME比较大的时候开始排查,毕竟大事务才会导致阻塞时间很长,小事务他自己很快就完了。

     

  • MySQL order by limit 导致的分页数据重复问题

    背景

    源于一次业务人员的SQL优化:在使用order by来排序,然后用limit分页展示的时候发现在某些页有重复的数据出现,体现在SQL层如下:

    如上,我们是每两行数据算作一页,利用limit来展示给用户的,但是发现尴尬的是第一页和第四页都出现了那个id为8的数据。 这不是搞事情么? 想要搞清楚这个原因我们需要弄明白下面这个问题:

    当我们运行如下类型SQL时:

    SELECT ….. WHERE [conditions] ORDER BY [sort] LIMIT N,M

    数据库是如何运行的。

    官方文档中的Limit优化这一章节有说说明:

    If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

    One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders.

    如果order by待排序的字段的多个行都有相同的值,mysql是会随机的顺序返回查询结果的,具体依赖对应的执行计划。也就是说如果排序的列是无序的,那么排序的结果行的顺序也是不确定的。我们上面的测试表中,time字段本身就是很多重复的值,所以就刚好印证了这个现象。

    但是这里有个问题,上面说到这个现象依赖具体的执行计划,那么什么样的执行计划才会导致这个结果呢 ?

    MySQL5.6的版本开始,优化器在遇到order by limit语句的时候,如果使用了Filesort文件排序,那么做了一个优化,使用了priority queue 。 而这个优化就是导致我们这一现象的根本,我们来看看源代码里面的东西:

    这样做的好处是在不能使用索引有序性的时候,如果要排序,并且使用了limit n,那么只需要在排序的过程中,保留n条记录即可,这样虽然不能解决所有记录都需要排序的开销,但是只需要 sort buffer 少量的内存就可以完成排序。因为 priority queue 使用了堆排序的排序方法,而堆排序是一个不稳定的排序方法,也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。

    MySQL 5.5 就没有这个优化,所以也就不会出现这个问题。

    接下来我们验证一下上述所言:

    验证一:此现象是否只出现在执行计划中的排序方式是Filesort时 ?

    首先,我们确认在使用Filesort的时候会出现数据重复现象

    然后,我们调整加索引,使得执行计划变成using index,再试试:

    果然,不会再出现那种情况了。

    解决办法

    如果出现上面那种问题的时候,我们该怎么避免呢 ?

    其实我们上面已经给出一种解决办法了,调整SQL或者索引,避免Filesort排序,既然说到这了,我们也讲一下,什么情况下会产生Filesort呢 ?

    Order by排序方式

    在MySQL中的ORDER BY有两种排序实现方式:

    1. 利用有序索引获取有序数据
    2. 文件排序

    在使用explain分析查询的时候利用有序索引获取有序数据显示Using index。而文件排序显示Using filesort。

    我们可以参考官方文档Order by优化章节查看什么情况下会使用哪种索引方式。

    Index索引排序

    查询是否使用Index索引排序分很多种情况,很多时候一个查询是否使用索引排序不光取决于 order by 子句中的变量是否使用了合理的索引,而是与三部分字段变量都有关系: 查询检出的字段、where 子句中的字段、order by 子句中的字段 。

    所以有时候,即使 order by 的字段与加索引的字段不完全匹配,也可能会用到 Index 索引排序。 例如 索引中 未使用的部分以及 ORDER BY列中剩余的部分 都是WHERE子句中的常量,如下:

    我们有这样一张表:

    执行如下SQL:

    如上,order by 子句中排序的字段只包含了联合索引的一部分,但是由于 where 子句和select 对象中都包含了联合索引的另外一部分,所以此查询依然可以使用 Index 索引排序。

    再举个例子,比如执行这样一条SQL:

    如上,虽然此查询中 order by 子句字段和联合索引完全相符,但是仍然使用了 filesort 排序。 因为此查询时 select * ,在这种情况下,优化器认为 “通过扫描索引得出对应的索引值再根据行此值将剩余对应的列值取出来” 的成本比直接扫描全表并对结果进行排序的代价更高。  所以优化器选择了直接扫描全表,如果将 * 换成要排序的列,那么优化器自然就会选择用 Index 索引排序:

    接下来,我们再举些查询典例

    典例0: 最简单的场景,当一个单独的索引被用作order by 时,是否能走索引扫描排序取决于 select 等参数:

    典例1: 在 InnoDB 中,主键索引也可以隐含的算作 待排序联合索引的一部分,所以下面的情况也是可以使用 Index 索引排序。

    典例2: 当 联合索引中的前一部分是一个常亮并放在where子句中时,如果此常量的“索引选择性”够高,则有可能用到 Index 索引范围扫描:

    典例3: 下面的两个SQL类型比上面的多个desc,但结果与上面的保持一致:

    典例4: 在下面的SQL中,key_part1 在where子句中与一个常量做比较并且做order by 子句中的排序,这时候是否能使用 索引排序取决于 key_part1 的索引选择性是否够高。

    典例5: 在下面的SQL语句中,key_part1 在where子句中与一个常量做比较, key_part2 位于 order by 子句中用作排序:

    上面测试的是联合索引,加入把 key_part1 和 key_part2 放成两个独立索引,也还是不行。

    典例六:key_part1 和 key_part2 都作为where子句的部分,key_part2 作为order by子句的部分。

    这种情况下,也是可以使用 Index 索引排序的:

    但此方式只适用于联合索引,如果是两个独立索引就不行了:

    上面的例子中,除了典例5 ,其他的都是可以使用Index 索引的例子,下面再举一些无法用到索引排序的例子

    典例1: 查询两个独立索引做排序

    典例2: 查询中的联合索引不连续:

    典例3: 查询中混合了DESC 和 ASC :

    典例4: 查询where 子句和order by 子句中的索引不一致

    典例5: order by 子句中包含运算函数

    典例6:查询中有不同的 order by 和 group by 表达式:

    如果上述查询中的 表达式一致,则可以使用索引:

    由于篇幅限制,剩下的几个就不做验证了,后续感兴趣的可以自行验证。

    总体来说,下面的情况大部分都走filesort排序了。

    • where语句与order by语句,使用了不同的索引
    • 检查的行数过多,且没有使用覆盖索引
    • ORDER BY中的列不包含在相同的索引,也就是使用了不同的索引
    • 对索引列同时使用了ASC和DESC
    • where语句或者ORDER BY语句中索引列使用了表达式,包括函数表达式
    • where 语句与ORDER BY语句组合满足最左前缀,但where语句中使用了条件查询。查见第10句,虽然where与order by构成了索引最左有缀的条件,但是where子句中使用的是条件查询
    • 当使用left join,使用右边的表字段排序
    Filesort 文件排序

    文件排序是通过相应的排序算法,将取得的数据在内存中进行排序:MySQL需要将数据在内存中进行排序,所使用的内存区域也就是我们通过 sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的,所以说可能在同一时刻在MySQL 中可能存在多个 sort buffer 内存区域。

    MySQL的Filesort排序算法大致分为三种:

    The Original filesort Algorithm

    第一种为比较原始的FileSort算法,主要包括如下步骤:

    1. 获取所有需要排序的数据行,将每行数据的排序字段和可以直接定位行数据的行指针信息两者组成的键值对。
    2. 将此键值对放入Sort Buffer 中。
    3. 如果Sort Buffer可以放入所有数据,则在Sort Buffer中进行排序。
    4. 如果Sort Buffer中无法放入所有数据,则每次用快速排序排序sort_buffer_size能容纳的行数,然后将排序结果写入一个新的临时文件,将本次排序结果的位置信息写入另一个临时文件中。
    5. 重复步骤3、或者4 直到所有满足条件的行都被处理了。
    6. 如果步骤4产生了临时文件,则对这些临时文件做合并排序。
    7. 合并排序的结果是将所有的行指针信息写入到一个整合的临时文件中。
    8. 根据行指针信息回表取数据,但是这里也有一层优化。 如果我单纯的从Sort Buffer中取到行指针,然后根据行指针去取数据的话,很大概率会产生大量的随机读操作。 所以MySQL是先将这些行指针排序后存入一个row buffer中(这个row buffer的大小取决于参数 read_rnd_buffer_size 大小)。然后再根据这个结果去取数据的话,很多就是顺序IO了。

    从上面的步骤来看,这种排序算法有一个很严重的问题就是两次回表。  第一次发生在where条件匹配取排序数据和行指针时,第二次发生在sort key之后取出对应的查询数据。

    The Modified filesort Algorithm

    这种排序算法针对第一种排序算法的两次回表做出了优化。他在第一步骤时就取出满足条件行的排序字段和select中的字段,组成排序字段和其他字段的键值对放在Sort Buffer中。 其他步骤基本相同,这样的话在执行完合并排序后就可以将字段全部返回。

    当然,这种也会消耗更多的sort buffer空间,所以也存在一种可能就是会创建大量的临时文件从而造成额外的IO消耗,当此消耗积累到一定程度的时候也许会造成更大的IO损耗。  所以mysql允许我们设定系统参数 max_length_for_sort_data的大小来控制使用哪种算法。

    Modified filesort Algorithm还有一个额外的优化,当查询的列类型中有CHAR或者VARCHAR或者nullable类型的字段时,他会进行标识优化以节省Sort Buffer空间。举个例子,比如一个VARCHAR(255)类型的列存储了3个字节的数据,那么他不会真的占用Sort Buffer 255字节的空间,而只会占用3字节的字符和2字节的长度指标。 NuLL值则只需要一个比特位。

    默认情况下,MySQL采用Modified filesort Algorithm ,除非满足下两个条件之一时选择original算法:

    • 查询的字段中有text或者blob类型。
    • length+sortlength > max_length_for_sort_data ,即查询的列的长度总和 + 排序列的长度 大于max_length_for_sort_data 值的时候。
    The In-Memory filesort Algorithm

    这第三种算法刚好印证了我们这篇博文的主题,在使用下列格式的SQL语句中,MySQL会使用内存排序算法,避免文件合并排序。

    SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

    当指定排序的N行数据可以完全存放在 Sort Buffer中时(如果M也给定的话,就是M+N),MySQL将Sort Buffer用作一个优先队列(priority queue)来在内存中排序处理数据。

    1. 扫描查询表,将匹配行对应的select查询的列插入priority queue 中。
    2. 返回队列中的前N行数据,如果M也指定了,则跳过前面的M行数据,返回接下来的N行。

    否则,数据库将依旧使用合并排序来处理此类操作:

    1. 扫描查询表,排序sort_buffer_size能容纳的行数,然后将排序结果写入一个新的临时文件。
    2. 重复上面的操作,直到将所有满足条件的行读完后,进行合并排序。

    在这里你可能会疑问,那这个priority queue是个啥东西,它是怎么实现的 ? 为什么会出现我们再最开始的时候发现的数据重复问题呢 ?

    这个priority queue,顾名思义就是优先队列,说到底就是一个排序策略。它会根据情况来选择排序算法,我们可以看看源码中的注释:

    我们知道,从一个很大的集合里面,找出最大的k个元素或最小的k个元素,最适合的算法就是使用大根堆或小根堆。

    如果 order by 后面有limit语句,则MySQL会通过check_if_pq_applicable选择是否使用堆排序。函数check_if_pq_applicable可读性很差,简单来说,需要满足一下几个条件:

    1. limit中的记录(N+M)小于匹配记录数的1/3,因为测试发现,堆排序比快排慢三倍,如果要获取的就大于所有记录的1/3,还不如直接使用快排。
    2. 当排序缓冲区不能容纳m+n行数据时,使用快排进行排序。

    而,堆排序是一种不稳定的排序方法。因为在堆的调整过程中,关键字进行比较和交换所走的是该结点到叶子结点的一条路径,因此对于相同的关键字就可能出现排在后面的关键字被交换到前面来的情况。

    如果是使用堆排序的话消耗主要集中在CPU,而合并排序的消耗主要集中在IO。

     

    参考

    https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

    https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

    https://www.cnblogs.com/thinkingthigh/p/7118343.html

    http://mysql.taobao.org/monthly/2015/06/04/

    http://blog.sae.sina.com.cn/archives/1940

    http://mingxinglai.com/cn/2016/04/mysql-filesort/

    http://blog.csdn.net/hguisu/article/details/7161981

    http://blog.51cto.com/ustb80/1073352

     

  • MySQL的Server-id简单一述

    背景

    今天被一位同行问到MySQL的server-id参数,才发现自己对它的认知还停留在:“它是标识一个MySQL实例的唯一标识”。 对于它在主从复制中的作用以及影响还是有所欠缺,在此研究总结一下。

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-11-29-%e4%b8%8a%e5%8d%8811-48-49

    直入主题吧,server-id 的存在意义是什么 ?

    1. 在主从复制的过程中唯一标识每个实例的。
    2. binlog中标识该语句最初是从哪个实例写入的。
    3. 在类似双主架构中,可以避免循环写入。

    有时候,我们可能对这些概念一扫而过,但其实往往并没有真正的理解这些东西,尤其是遇到问题的时候难以联想到这里,我想这就是知识框架和整理的重要性吧。

    比如下面几个问题:

    1. 两个MySQL实例做双主时,server-id一样能搭建成功吗?如果能成功会发生什么现象 ?不能成功的话原因是什么?
    2. 一主一从的MySQL架构中,server-id一样能搭建成功吗?如果能成功会发生什么现象 ?不能成功的话原因是什么?
    3. 在主-从-从的级联架构中,任意两实例的server-id一样能搭建成功吗?如果能成功会发生什么现象 ?不能成功的话原因是什么?

    针对上面的问题,我们一一的做出实际的测试,看看结果:

    • 服务器版本:Centos 6.5
    • MySQL版本:5.7.19
    • Binlog模式:Row

    测试一: 双主实例

    因为我这边测试时两个新的MySQL实例,所以其实双主搭建并不困难,主要就是通过配置文件解决自增ID的问题:

    Master1(10.30.249.59):

    • auto_increment_offset表示自增长字段从那个数开始,他的取值范围是1 .. 65535
    • auto_increment_increment表示自增长字段每次递增的量,其默认值是1,取值范围是1 .. 65535

    Master2(10.31.152.178):

    这样我们可以使两台实例的自增ID错开,不会导致冲突。 配置好这些以后,我让两台实例的server-id相同。

    然后在两个实例上为对方分配主从同步账号,并启动主从复制。呵呵,遗憾的是,当我从任意一个实例执行start slave的时候就直接报错了:

    如上,IO线程无法启动,报1593错误,错误说明里面也描述的很清楚,直接告诉你server-id冲突了。

    嗯,既然都测试到这里了,那我们刚好也看看正常的双主模式下,这个server-id是怎么起到避免循环执行的。

    我们将Master2 的server-id改成别的值:

    然后在Master1和Master2上分别启动主从复制,这次很顺利。

    首先,我在Master1上创建一个数据库kobe,也成功同步到了Master2,我们来看看两个实例的binlog:

    Master1: 一个Query类型的 event,并且标识了server-id是123.