God's in his heaven.
All's right with the world.

0%

发表于 分类于

前言

MySQL5.5版本开始引入了MDL锁用来保护元数据信息，让MySQL能够在并发环境下多DDL、DML同时操作下保持元数据的一致性。本文用MySQL5.7源码分析了常用SQL语句的MDL加锁实现。

MDL锁粒度

MDL_key由namespace、db_name、name组成。

namespace包含：

  • GLOBAL。用于global read lock，例如FLUSH TABLES WITH READ LOCK。

  • TABLESPACE/SCHEMA。用于保护tablespace/schema。

  • FUNCTION/PROCEDURE/TRIGGER/EVENT。用于保护function/procedure/trigger/event。

  • COMMIT。主要用于global read lock后，阻塞事务提交。（在DML的commit阶段也会获取COMMIT锁）

  • USER_LEVEL_LOCK。用于user level lock函数的实现，GET_LOCK(str,timeout)， RELEASE_LOCK(str)。

  • LOCKING_SERVICE。用于locking service的实现。

阅读全文 »

发表于 分类于

MDL(Meta Data LocK)的作用

在MySQL5.1及之前的版本中，如果有未提交的事务trx，当执行DROP/RENAME/ALTER TABLE RENAME操作时，不会被其他事务阻塞住。这会导致如下问题(MySQL bug#989)

master： 未提交的事务，但SQL已经完成(binlog也准备好了)，表schema发生更改，在commit的时候不会被察觉到.

slave： 在binlog里是以事务提交顺序记录的，DDL隐式提交，因此在备库先执行DDL，后执行事务trx，由于trx作用的表已经发生了改变，因此trx会执行失败。 在DDL时的主库DML压力越大，这个问题触发的可能性就越高

在5.5引入了MDL（meta data lock）锁来解决在这个问题

阅读全文 »

发表于 分类于

背景

MySQL 从5.5.3版本，对Metadata lock进行了调整，主要是MDL锁持有的周期从语句变成了事务， 其原因主要是解决两个问题：

问题1: 破坏事务隔离级别 在repeatable read的隔离级别下，多次的select语句执行过程中，会因为其它session的DDL语句，而导致select语句执行的结果不相同，破坏了RR的隔离级别。

问题2: 破坏binlog的顺序 在对表的DML过程中，会因为其它session的DDL语句，导致binlog里的event顺序在备库执行的结果和主库不一致。

从MySQL 5.5.3开始，MDL锁的持有周期变成了事务，解决了上面提到的两个问题，但在autocommit=off的情况下，也大大增加了阻塞的可能性。DBA对于阻塞的case，处理起来又比较麻烦，原因就是MDL锁的阻塞情况没有暴露明确的信息。

从MySQL 5.7.6开始，可以通过performance schema来查询MDL锁的持有情况。

在开始介绍5.7的跟踪Metadata lock之前， 小编还想讨论一下前面提到的这两个问题，在Oracle数据库中是如何处理的。

阅读全文 »

发表于 分类于

前言

InnoDB有两个非常重要的日志，undo log 和 redo log；通过undo log可以看到数据较早版本，实现MVCC，或回滚事务等功能；redo log用来保证事务持久性

本文以一条insert语句为线索介绍 mini transaction

mini transaction 简介

mini transation 主要用于innodb redo log 和 undo log写入，保证两种日志的ACID特性

mini-transaction遵循以下三个协议:

  1. The FIX Rules

  2. Write-Ahead Log

  3. Force-log-at-commit

阅读全文 »

发表于 分类于

背景

在 AliSQL 上面有人提交了一个 bug，在使用主备的时候 service stop mysql 不能关闭主库，一直显示 shutting down mysql …，到底怎么回事呢，先来看一下 service stop mysql 是怎么停止数据库的。配置 MySQL 在系统启动时启动需要把 MYSQL_BASEDIR/support-files 目录下的脚本 mysql.sever 放到 /etc/init.d/ 目录下，脚本来控制 mysqld 的启动和停止。看一下脚本中的代码 ：

1
2
3
4
5
6
7
8
9
10
11
if test -s "$mysqld_pid_file_path"
     then
       mysqld_pid=`cat "$mysqld_pid_file_path"`

       if (kill -0 $mysqld_pid 2>/dev/null)
       then
         echo $echo_n "Shutting down MySQL"
         kill $mysqld_pid
         # mysqld should remove the pid file when it exits, so wait for it.
         wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$?
	...

实际上的关闭动作就是向 mysqld 进程发送一个 kill pid 的信号，也就是 TERM ， wait_for_pid 函数中就是不断检测 $MYSQL_DATADIR 下面的 pid 文件是否存在，并且打印 ‘.’，所以上述问题应该是 mysqld 没有正确处理接收到的信号。

阅读全文 »

发表于 分类于

前言

我们知道InnoDB的索引组织结构为Btree。通常情况下，我们需要根据查询条件，从根节点开始寻路到叶子节点，找到满足条件的记录。为了减少寻路开销，InnoDB本身做了几点优化。

首先，对于连续记录扫描，InnoDB在满足比较严格的条件时采用row cache的方式连续读取8条记录（并将记录格式转换成MySQL Format），存储在线程私有的row_prebuilt_t::fetch_cache中；这样一次寻路就可以获取多条记录，在server层处理完一条记录后，可以直接从cache中取数据而无需再次寻路，直到cache中数据取完，再进行下一轮。

另一种方式是，当一次进入InnoDB层获得数据后，在返回server层前，当前在btree上的cursor会被暂时存储到row_prebuilt_t::pcur中，当再次返回InnoDB层捞数据时，如果对应的Block没有发生任何修改，则可以继续沿用之前存储的cursor，无需重新定位。

上面这两种方式都是为了减少了重新寻路的次数，而对于一次寻路的开销，则使用Adaptive hash index来解决。AHI是一个内存结构，严格来说不是传统意义上的索引，可以把它理解为建立在Btree索引上的“索引”。

阅读全文 »

发表于 分类于

背景

最近有同事问，set names 时会同时设置了3个session变量

1
2
3
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

就从变量名字来看，character_set_client 是设置客户端相关的字符集，character_set_results 是设置返回结果相关的字符集，character_set_connection 这个就有点不太明白了，这个有啥用呢？

概念说明

通过官方文档来看:

  1. character_set_client 是指客户端发送过来的语句的编码;
  2. character_set_connection 是指mysqld收到客户端的语句后，要转换到的编码；
  3. 而 character_set_results 是指server执行语句后，返回给客户端的数据的编码。
阅读全文 »

发表于 分类于

前言

上一篇文章 提过，我们在之后的文章中会从 optimizer 的选项出发，系统的介绍 optimizer 的各个变量，包括变量的原理、作用以及源码实现等，然后再进一步的介绍优化器的工作过程（SQL 语句扁平化处理、索引选择、代价计算、多表连接顺序选择以及物理执行等内容），本期我们先看一下众所周知的 ICP，官方文档请参考这里。

ICP 测试

首先，咱们来看一下打开 ICP 与关闭 ICP 之间的性能区别，以下是测试过程：

准备数据：

1
2
3
4
5
6
7
8
create table icp(id int, age int, name varchar(30), memo varchar(600)) engine=innodb;
alter table icp add index aind(age, name, memo);
--let $i= 100000
while ($i)
{
  --eval insert into icp values($i, 1, 'a$i', repeat('a$i', 100))
  --dec $i
}

PS: MySQL 有一个叫profile的东东，可以用来监视 SQL 语句在各个阶段的执行情况，咱们可以使用这个工具来观察 SQL 语句在各个阶段的运行情况，关于 profile 的详细说明可以参考官方文档。

阅读全文 »

发表于 分类于

前言

本文的目的是对 InnoDB 的事务锁模块做个简单的介绍，使读者对这块有初步的认识。本文先介绍行级锁和表级锁的相关概念，再介绍其内部的一些实现；最后以两个有趣的案例结束本文。

本文所有的代码和示例都是基于当前最新的 MySQL5.7.10 版本。

行级锁

InnoDB 支持到行级别粒度的并发控制，本小节我们分析下几种常见的行级锁类型，以及在哪些情况下会使用到这些类型的锁。

LOCK_REC_NOT_GAP

锁带上这个 FLAG 时，表示这个锁对象只是单纯的锁在记录上，不会锁记录之前的 GAP。在 RC 隔离级别下一般加的都是该类型的记录锁（但唯一二级索引上的 duplicate key 检查除外，总是加 LOCK_ORDINARY 类型的锁）。

阅读全文 »

发表于 分类于

背景

在MySQL 5.1中，如果配置项sync_binlog=1，并且innodb_flush_log_at_trx_commit=1，那么MySQL的TPS将会下降到几十每秒，完全不可接受。这是因为InnoDB提交事务时，不仅需要将REDO刷盘，还需要将Binlog刷盘，每个事务都需要2次sync操作。机械磁盘的IOPS也就为几百的水平，所以InnoDB的性能极差。

这个问题，在MySQL 5.6中得到了比较好的解决。在了解Binlog Group Commit之前，需要先了解MySQL Binlog和InnoDB的两阶段提交。MySQL为了保证主库和从库的数据一致性，就必须保证Binlog和InnoDB的一致性，即如果一个事务写入了Binlog，InnoDB中就必须提交该事务；相反，如果一个事务没有写入Binlog，InnoDB就不能提交该事务。做法是：

阅读全文 »