MySQL内核月报

WBOYOriginal: 2016-06-07 15:46:231552browse

fromhttp://mysql.taobao.org/index.php/MySQL%E5%86%85%E6%A0%B8%E6%9C%88%E6%8A%A5_2014.12 MySQL· 性能优化·5.7 Innodb事务系统背景知识为了便于理解下文，我们先简单梳理下Innodb中的事务、视图、多版本的相关背景知识。在Innodb中，每次开启一个事

from http://mysql.taobao.org/index.php/MySQL%E5%86%85%E6%A0%B8%E6%9C%88%E6%8A%A5_2014.12

MySQL·　性能优化·5.7 Innodb事务系统

背景知识

为了便于理解下文，我们先简单梳理下Innodb中的事务、视图、多版本的相关背景知识。

在Innodb中，每次开启一个事务时，都会为该session分配一个事务对象。而为了对全局所有的事务进行控制和协调，有一个全局对象trx_sys，对trx_sys相关成员的操作需要trx_sys->mutex锁。

Innodb使用一种称做ReadView(视图)的对象来判断事务的可见性（也就是ACID中的隔离性）。根据可见性原则，某个新开启的事务不应该看到其他未提交的事务。 Innodb在执行一个SELECT或者显式开启START TRANSACTION WITH CONSISTENT SNAPSHOT (后者只应用于REPEATABLE-READ隔离级别) 会创建一个视图对象。对于RR隔离级别，视图的生命周期到事务提交结束，对于RC隔离级别，则每条查询开始时重分配事务。

通常一个视图中包含创建视图的事务ID，以及在创建视图时活跃的事务ID数组。例如，当开启一个视图时，当前事务的事务ID为5，事务链表上活跃事务ID为{2,5,6,9,12}，那么就会把{2,6,9,12}存储到当前的视图中（5是当前事务的ID，不记录到视图中），{2,6,9,12}对应的事务所做的修改对当前事务而言都是不可见的，小于2的事务ID对当前事务都是可见的，大于12的事务ID对当前事务是不可见的。

那么如何判断可见性呢？对于聚集索引，每次修改记录时，都会在记录中保存当前的事务ID，同时旧版本记录存储在UNDO中；对于二级索引，则在二级索引页中存储了更新当前页的最大事务ID，如果该事务ID大于readview->up_limit_id（对于上例，up_limit_id值为2），那么就需要回聚集索引判断记录可见性；如果小于2，那么总是可见的，可以直接读取。

Innodb的多版本数据使用UNDO来维护的，例如聚集索引记录(1) =>(2)=>(3)，从1更新成2，再更新成3，就会产生两条undo记录。当然这不是本文讨论的重点。后续在单独针对临时表的优化时会谈及undo相关的知识。

Innodb事务系统优化

在MySQL 5.7版本里，针对性的对事务系统做了比较深入的优化，主要解决了下面几个问题。

问题一：视图对象的创建需要trx_sys->mutex锁保护

trx_sys->mutex是事务系统最核心的全局锁对象，持有该锁进行的操作都不应该耗时过长。对于read view对象，完全可以将其缓存下来重复使用。这样就避免了持有锁分配视图内存。

因此在MySQL 5.7版本中，实例启动时就分配1024个视图对象；同时维护两个链表，一个是已使用的视图链表，一个是空闲的视图链表；当需要分配新的视图时，总是从空闲视图链表中分配，如果没有，再新分配一个。

在Percona Server中也做了类似的优化，但与5.7不同的是，其不集中管理所有的视图，而是每个事务对象(trx_t)上都挂载一个预分配的视图对象，在事务对象销毁时释放(事务对象本身对session而言也是重用的)。

问题二：视图对象中保存全局事务ID时，需要扫描事务链表

正如上面描述的，为了判断事务视图的可见性，在打开一个视图时需要拷贝当时活跃的事务ID。在5.5及之前版本需要遍历所有的活跃事务，而在5.6中，将事务链表拆分成了只读事务链表，和读写事务链表，这样我们只需要遍历读写事务链表，拷贝事务ID即可。

在5.7中，事务系统维持了一个全局事务ID数组，每个活跃读写事务的ID都被加入到其中，在事务提交时从其中删除，这样打开视图时只需要使用memcpy 拷贝该数组即可，无需遍历链表。在读写链表较长(高并发下)的场景，该优化可以显著的提升性能。不过就该优化点而言，Percona Serve同样走在了前面，相同的思路实现在Percona Server 5.6中。

问题三: 用户需要显式开启只读事务，才会放入只读事务链表

尽量在5.6中已经将事务链表拆分成了只读事务链表和读写事务链表（AUTOCOMMIT的SELECT不加入任何链表），但用户需要显式的指定事务以只读模式打开（START TRANSACTION READ ONLY）或者设置SESSION变量tx_read_only。

显然这种方式对用户而言是极不友好的，因此在5.7中做了比较彻底的改变，将只读事务链表从其中彻底移除了，取而代之的是，所有事务都以只读模式打开。

例如如下事务序列：

BEGIN;

SELECT; //事务开始，不分配事务ID，不分配回滚段；

UPDATE; //分配事务ID并插入全局事务数组和事务对象集合中，分配回滚段；

COMMIT;

而对于BEGIN;SELECT;SELECT;COMMIT这样的序列，整个事务周期既不分配事务ID，也不分配回滚段。

那么问题来了，既然只读的事务不分配事务ID，那么如何标示事务呢，在5.7中，使用事务对象的地址来进行计算得到一个唯一的事务ID。执行’SHOW ENGINE INNODB STATUS’不再显示活跃的只读事务，只能通过INNODB_TRX表来查询。这是一个需要注意的点，因为很多人都是通过前者来找到长时间未提交的事务。

另外一个比较有意思的小优化是，对于AUTOCOMMIT的只读查询，关闭视图时，并不是立刻从视图链表中移除，而是设置一个简单的close标记；该session下次需要打开该read view时，如果这期间没有任何读写事务，就可以直接重用上次的read view，清楚close标记，这样打开、关闭视图都无需获取trx_sys->mutex。

问题四：隐式锁转换为显式锁的开销

Innodb对于类似INSERT操作，采用的是隐式锁的方式，隐式锁不是锁，只是一种称呼而已，只有在需要的时候，才会转换为显式锁。例如如下：

Session 1: BEING; INSERT INTO t1(pk, val) VALUES (1,2); //不创建锁对象

Session 2: UPDATE t1 SET val=val+1 WHERE pk=1; //创建两个锁对象，一个是为session1创建一个记录锁对象，另外一个是给自己创建一个等待类型的记录锁对象，然后session2加入锁等待队列。

在Session 2中为Session1创建锁对象的过程即是所谓的隐式锁向显式锁转换。当session2扫描到session 1插入的记录时，发现session 1的事务依然活跃，就会进入转换逻辑。

在5.6版本中，其转换过程如下：

1.持有lock_sys->mutex

2. 持有trx_sys->mutex；

根据事务ID，扫描读写事务链表，找到对应的事务对象；

释放trx_sys->mutex；

3.创建显式锁对象

4.释放lock_sys->mutex

可以看到，在该操作的过程中，全程持有lock_sys->mutex，持有锁的原因是防止事务提交掉。当读写事务链表非常长时（例如高并发写入时），这种开销将是不可接受的。

在5.7版本中，上述逻辑则优化成:

1. 持有trx_sys->mutex

根据事务ID找到对应的事务对象（直接查找trx_sys->rw_trx_set，其保存了trx_id和事务对象的映射关系，因此无需扫描读写事务链表）

增加事务对象引用计数（++trx->n_ref）

释放trx_sys->mutex

2. 持有lock_sys->mutex；

创建显式锁对象；

释放lock_sys->mutex；

3.递减事务对象引用计数

在事务commit，释放记录锁前，会先判断引用记录数是否为0，如果不为0，表示正有其他事务为其转换显式锁，这时候需要等待，直到计数为0，才能进入释放事务记录锁阶段。

总的来说，该优化减少了隐式锁转换时持有LOCK_sys->mutex的时间，从而提升性能。

除了上述提到的几点事务优化外，在5.7版本中还对事务系统部分的代码做了重构，完全用C++重写；引入了一个POOL结构，事务对象和锁对象都可以缓存复用。大家可以阅读几个相关的worklog，以更好的理解上述优化：

http://dev.mysql.com/worklog/task/?id=6047

http://dev.mysql.com/worklog/task/?id=6578

http://dev.mysql.com/worklog/task/?id=6899

http://dev.mysql.com/worklog/task/?id=6906

MySQL·　踩过的坑·5.6 GTID 和存储引擎那会事

混用引擎的问题

在MySQL中，存储引擎是通过插件方式使用的，事务是由存储引擎自己实现，MySQL服务层是不管理事务的，所以在同一个事务中混用不同的存储引擎是不可靠的。如果混用事务引擎和非事务引擎的话，事务如果正常提交的话，5.5不会有问题，但是5.6版本如果开了 GTID 的话就会报错，因为GTID模式下不允许事务中同时更新事务引擎和非事务引擎（Restrictions on Replication with GTIDs）；如果事务回滚的话，不管是 5.5 还是 5.6 都会有问题的，因为对非事务引擎表的操作是无法回滚的，这样就会造成数据不一致，因为只有部分操作成功，并且结果不可预知，事务的原子性和一致性被破坏。

我们下面举例子来说明，t1_i 是事务引擎表（InnoDB），ti_m 是非事务引擎（MyISAM）。

在事务执行前2张表里的数据如下：

mysql> SELECT * FROM t1_i;
+------+------+
| id   | name |
+------+------+
|    1 | test |
|    2 | test |
+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1_m;
+------+------+
| id   | name |
+------+------+
|    1 | test |
|    2 | test |
+------+------+
2 rows in set (0.00 sec)

事务如下，对2张表分别插一条数据，然后 rollback 模拟出错回滚，

BEGIN;
INSERT INTO t1_m VALUES (3, "test");
INSERT INTO t1_i VALUES (3, "test");
ROLLBACK;

执行回滚后，我们会看到 MySQL 返回信息中有 warnings;
Query OK, 0 rows affected, 1 warning

查看 warning，可以清楚地看到提示，非事务引擎无法回滚：

SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+

我们来看下现在2张表中的数据，可以看到 t1_m 的插入确实没有回滚，这与事务逻辑的预期是不一样的。

mysql> SELECT * FROM t1_m;
+------+------+
| id   | name |
+------+------+
|    1 | test |
|    2 | test |
|    3 | test |
+------+------+

mysql> SELECT * FROM t1_I;
+------+------+
| id   | name |
+------+------+
|    1 | test |
|    2 | test |
+------+------+

当我们在非事务引擎上执行事务相关的操作的时候，MySQL 一般是不会报错的，比如在非事务引擎的操作前后用 BEGIN 和 COMMIT, 这是完全可以的，并且让我们误以为操作是有事务性的，我们在使用的时候要注意这一点。

总的来说，要遵循这样的一条原则，不要在事务中混用不同的存储引擎。

5.5 到 5.6 升级问题

5.5 升级到 5.6 的时候，我们会先建立一个5.6版本的实例，然后把数据从5.5迁移到5.6，最后把连接切到 5.6 完成升级。有时候升级会失败，这是因为5.6 GTID 的一些限制导致的。

其中有一条是关于临时表的限制，官方描述如下：

Temporary tables. CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not supported inside transactions when using GTIDs (that is, when the server was started with the --enforce-gtid-consistency option). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.

限制表明不能在事务中创建临时表，可以在事务外创建，但要求 autocommit = 1。

我们新建5.6的实例的时候，会重用 5.5 的配置，autocommit 就是其中之一，并且这个是允许用户配置的，如果用户在5.5实例上把这个值改为0，然后升级到5.6，就会出错。我们知道，创建新实例的时候，会先通过 mysql_install_db 脚本初始化数据库，这包括系统表的创建、基本数据的添加等，其中会用到 mysql_system_tables_data.sql 这个sql脚本，里面有：

CREATE TEMPORARY TABLE tmp_db LIKE db;
CREATE TEMPORARY TABLE tmp_user LIKE user;
CREATE TEMPORARY TABLE tmp_proxies_priv LIKE proxies_priv;

这样的语句，脚本执行失败，所以mysql.db 、mysql.user 和 mysql.proxies_priv表里的初始数据就没有添加进去。

error log 中会看到这样的信息

2014-12-08 20:48:15 9264 [Warning] Bootstrap mode disables GTIDs. Bootstrap mode should only be used by mysql_install_db which initializes the MySQL data directory and creates system tables. ERROR: 1787 When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. 2014-12-08 20:48:16 9264 [ERROR] Aborting

我们这时如果正常启动mysqld的话，会发现默认root用户是登录不进去的，因为user表是空的，根本没有root用户，而匿名用户 @localhost 又什么也做不了。

我们用 --skip-grant-tables 启动数据库后，匿名用户登录进去，查看user表，会发现是空的。

mysql> SELECT * FROM mysql.user;
Empty set (0.00 sec)

因为新建的数据库不可用，所以最终导致迁移失败。

这个问题的根本原因是5.5的配置文件中 autocommit = 0 导致的，所以好的解决方法是我们在升级5.6前，把这个值设置为1，然后再升级。

MySQL·　性能优化·thread pool 原理分析

大连接问题

现有mysql 处理客户端连接的方式会触发mysql 新建一个线程来处理新的连接，新建的线程会处理该连接所发送的所有 SQL 请求，即 one-thread-per-connection 的方式，其创建连接的堆栈为：

 mysqld_main
   handle_connections_sockets
     create_new_thread
       create_thread_to_handle_connection
         handle_one_connection

线程建立后，处理请求的堆栈如下：

0 mysql_execute_command
1 0x0000000000936f40 in mysql_parse
2 0x0000000000920664 in dispatch_command
3 0x000000000091e951 in do_command
4 0x00000000008c2cd4 in do_handle_one_connection
5 0x00000000008c2442 in handle_one_connection
6 0x0000003562e07851 in start_thread () from /lib64/libpthread.so.0
7 0x0000003562ae767d in clone () from /lib64/libc.so.6

优点及存在的问题

在连接数较小的情况下可以很快的响应客户端的请求，但当连接数非常大时会创建很多线程，这样会引起以下问题：

1. 过多线程之间的切换会加重系统的负载，造成系统资源紧张且响应不及时；

2. 频繁的进行线程的创建及销毁以及线程间同时无序的竟争系统资源加重了系统的负载。

thread_pool正是为了解决以上问题而产生的；

什么是thread_pool

thread_pool(线程池)，是指mysql 创建若干工作线程来共同处理所有连接的用户请求，用户的请求的方式不再是 ‘one thread per connection’，而是多个线程共同接收并处理多个连接的请求，在数据库的底层处理方面(mysql_execute_command)，单线程的处理方式和线程池的处理方式是一致的。

thread_pool 的工作原理

启动 thread_pool 的mysql 会创建thread_pool_size 个thread group , 一个timer thread, 每个thread group 最多拥有thread_pool_oversubscribe个活动线程，一个listener线程，listener线程负责监听分配到thread group中的连接，并将监听到的事件放入到一个queue中，worker线程从queue中取出连接的事件并执行具体的操作，执行的过程和one thread per connection 相同。timer threaad 则是为了监听各个threadgroup的运行情况，并根据是否阴塞来创建新的worker线程。

thread_pool 建立连接的堆栈如下：

 mysqld_main
   handle_connections_sockets
     create_new_thread
       tp_add_connection
         queue_put

thread group中的 worker 处理请求的堆栈如下：

0 mysql_execute_command
1 0x0000000000936f40 in mysql_parse
2 0x0000000000920664 in dispatch_command
3 0x000000000091e951 in do_command
4 0x0000000000a78533 in threadpool_process_request
5 0x000000000066a10b in handle_event
6 0x000000000066a436 in worker_main
7 0x0000003562e07851 in start_thread ()
8 0x0000003562ae767d in clone ()

其中worker_main函数是woker线程的主函数，调用mysql本身的do_command 进行消息解析及处理，和one_thread_per_connection 是一样的逻辑； thread_pool 自行控制工作的线程个数，进而实现线程的管理。

thread_pool中线程的创建：

1. listener线程将监听事件放入mysql放入queue中时，如果发现当前thread group中的活跃线程数active_thread_count为零，则创建新的worker 线程；

2. 正在执行的线程阻塞时，如果发现当前thread group中的活跃线程数active_thread_count为零，则创建新的worker 线程；

3. timer线程在检测时发现没有listener线程且自上次检测以来没有新的请求时会创建新的worker线程，其中检测的时间受参数threadpool_stall_limit控制；

4. timer线程在检测时发现没有执行过新的请求且执行队列queue 不为空时会创建新的worker线程；

worker线程的伪码如下：

 worker_main
 {
   while(connection)
   {
        connection= get_event();
        /* get_event函数用于从该线程所属thread_Group中取得事件，然后交给Handle_event函数处理，在同一Group内部，只有thread_pool_oversubscribe个线程能同时工作，多余的线程会进入sleep状态  */
        if(connection)
          handle_event(connection);
        /* 如果是没有登录过的请求，则进行授权检查，并将其Socket绑定到thread_group中的pollfd中，并设置Connection到event中的指针上；对于登录过的，直接处理请求 */
   }
   // 线程销毁
 }

thread_pool中线程的销毁：

当从队列queue中取出的connection为空时，则此线程销毁，取connection所等待的时间受参数thread_pool_idle_timeout的控制；综上，thread_pool通过线程的创建及销毁来自动处理worker的线程个数，在负载较高时，创建的线程数目较高，负载较低时，会销毁多余的worker线程，从而降低连接个数带来的影响的同时，提升稳定性及性能。同时，threadpool中引入了Timer 线程，主要做两个事情。

1. 定期检查每个thread_group是否阻塞，如果阻塞，则进行唤醒或创建线程的工作；

2. 检查每个thread_group中的连接是否超时，如果超时则关掉连接并释放相应的资源；

threadpool在使用中存在的问题：

1. 由于threadpool严格控制活跃线程数的限制，如果同时有多个大查询同时分配到了同一个thread group，则会造成此group中的请求过慢，rt 升高，最典型的就是多个binlog dump 线程同时分配到了同一个group内；

2. 开启了事务模式时，非事务模式的请求会放入低优先级队列，因此可能在较长时间内得不到有效处理，极端情况下，会导致实例hang 住，例如某个连接执行了 flush tables with read lock ,并且此连接的后续请求都会放入低优先级，那么有可能会造成实列hang住；

3. 较小并发下，threadpool 性能退化的问题；

MySQL·　性能优化·并行复制外建约束问题

背景：

mysql 主备同步是通过binlog来进行的，备库的 IO 线程从主库拉取binlog，SQL线程将拉取的binlog应用到备库，在5.6之前，备库只有一个线程应用binlog，主库的更新量大，且备库的执行效率低时，就会造成了大量从主库拉取的binlog来不及执行，因此造成了主备延迟问题。为了解决主备延迟，需要提高备库的执行效率，阿里MySQL 设计并开发了并行复制功能，所谓并行复制，指的是应用binlog的线程数量是多个的，而不是原生的单个线程，经过测试可以极大的提高复制性能（有3X的性能提升），在并行复制中，一个 IO 线程，一个分发线程，多个sql_thread，分发线程读取relay log，并将读取的relay log 分发给多个sql_thread, 从而实现并行化的效果。

原理：

分发线程的分发原理是依据当前事务所操作的表的名称来进行分发的，如果事务是跨表的（一个事务更新多张表），则需要等待已分配的该表相关的事务全部执行完毕，才会继续分发，其分配行为的伪码可以简单的描述如下：

get_slave_worker
 if (contains_partition_info(log_event))
    table_name= get_db_name(log_event);
    entry {table_name, worker_thread, usage} = map_table_to_worker(table_name);
    while (entry->usage > 0)
       wait();
   return worker;
 else if (last_assigned_worker)
   return last_assigned_worker;
 else
   push into buffer_array and deliver them until come across a event that have partition info

问题描述（testcase）：

drop table t2 if exists t2;
drop table t1 if exists t1;
create table t1(c1 int primary key, c2 int);
create table t2(c1 int primary key, c2 int , foreign key (c2) references t1(c1));
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t2 values(1,1);
insert into t2 values(2,2);

以下两个语句在备库的执行顺序不同，结果会不同

delete from t2 where c2=1; （语句1）

update t1 set c1=3 where c1=1;（语句2）

如果语句2先于语句1在备库执行，则会报外建约束错误，因为在上述的分发原理中没有考虑到外建约束问题，这种情况下，只有串行化处理了，当然，你可以执行：set global foreign_key_checks=off;然后start slave；在类似语句执行完后，再恢复foreign check，但是这样做真正安全吗？答案是不一定的……

情况1:

create table t1(c1 int primary key, c2 int);

create table t2(c1 int primary key, c2 int , foreign key (c2) references t1(c1));

在这种定义下，如果不检测foreign key，则不会有问题，因为对t1, t2的操作都会记录binlog；

情况2:

create table t1(c1 int primary key, c2 int);

create table t2(c1 int primary key, c2 int , foreign key (c2) references t1(c1) on delete cascade);

在这种定义下，如果不检测foreign key，则会有问题，因为对表t1的操作会影响t2表，在检测foreign key的时候，会进行相应的cascade操作，如果不检测foreign key,则不进行级联操作，这种问题一旦发生，则会引起主备不一致问题。

解决方法

5.6 并行复制没有此问题，5.6中在检测到foreign key的事件时，会等待已经分发的所有binlog都已执行完再执行，因此解决了此问题。

改进方案 这个方案虽然能解决问题，但是若系统中只要出现一个外键关系，并且持续有更新，会导致持续性的回退到单线程方案，那么多线程复制的效果就会大打折扣。实际上这个做法比较极端，改进的方案是，遇到有foreign key 的表，应该将其分发到依赖他的表的同一个sql thread 中。这样执行这些事务时，其他表的并行复制仍能继续。

MySQL·　答疑释惑·binlog event有序性

背景

　　对于解析MySQL的binlog用来更新其他数据存储的应用来说，binlog的顺序标识是很重要的。比如根据时间戳得到binlog位点作为解析起点。

　　但是binlog里面的事件，是否有稳定的有序性？

　　binlog中有三个看上去可能有序的信息：xid、timestamp、gno。本文分析这三个信息在binlog中的有序性。

Xid

　　当binlog格式为row，且事务中更新的是事务引擎时，每个事务的结束位置都有Xid，Xid的类型为整型。

　　MySQL中每个语句都会被分配一个全局递增的query_id(重启会被重置)，每个事务的Xid来源于事务第一个语句的query_id。

　　考虑一个简单的操作顺序：

　　session 1: begin; select; update;

　　session 2: begin; select; update; insert; commit;

　　session 1: insert; commit;

　　显然Xid2 > Xid1，但因为事务2会先于事务1记录写binlog，因此在这个binlog中，会出现Xid不是有序的情况。

TIMESTAMP

　　时间戳的有序性可能是被误用最多的。在mysqlbinlog这个工具的输出结果中，每个事务起始有会输出一个SET TIMESTAMP=n。这个值取自第一个更新事件的时间。上一节的例子中,timestamp2>timestamp1,但因为事务2会先于事务1记录写binlog，因此在这个binlog中，会出现TIMESTAMP不是有序的情况。

GNO

　　对于打开了gtid_mode的实例，每个事务起始位置都会有一个gtid event，其内容输出格式为UUID:gn，gno是一个整型数。

　　由于NEXT_GTID是可以直接指定的，因此若故意构造，可以很容易得到不是递增的情况，这里只讨论automatic模式下的有序性。

　　与上述两种情况不同，gno生成于事务提交时写binlog的时候。注意这里不是生成binlog，而是将binlog写入磁盘的时候。因此实现上确保了同一个UUID下gno的有序性。

小结

　　一个binlog文件中的Xid和TIMESTAMP无法保证有序性。在无特殊操作的情况下，相同的UUID可以保证gno的有序性。

MySQL·　答疑释惑·server_id为0的Rotate

背景

　　在MySQL的M-S结构里面，event是binlog日志的基本单位。每个event来源于主库，每个Event都包含了serverid，用于表示该event是哪个实例生成的。

　　在5.6里面，细心的同学会发现，备库的relaylog中出现了server_id为0的event，其类型为Rotate Event。

　　这里说说server_id=0的Rotate Event。

心跳event

　　MySQL Cluster中从NDB 6.3开始就出现的HEADBEAT event(hb event), 在社区版直到5.6.2才提供。

　　hb event的目的是为了保持M-S之间的心跳。用法上是slave在change master的时候可以指定MASTER_HEARTBEAT_PERIOD。当此值为0时，主库发送完所有事件后这个主备通道就一直idle直到发送新的event；当此值为非0的n时，主库通道在idle超过n秒之后，发一个hb event。

　　心跳event的另外一个作用是主库将当前的最新位点通知给备库。hb event中包含主库当前binlog最新位置的文件名和位点。备库收到hb event后判断主库位点是否大于本地保存的位点，若是，则在relay log中记录一个server_id为0的Rotate事件，这意味着主库上新增了不需要发送给自己的event。

出现条件

　　在传统的主备环境中，正常情况下心跳事件是不会被触发写入到备库的relaylog的。这是因为所有的主库binlog中的事件都会发给备库，所以备库收到的hb event中的位点总是不大于备库已经接收到的binlog event最大值（注意到hb event只在通道idle时才发）。

　　但是在5.6启用了GTID以后，就出现了这样的case。最常见的是每个binlog文件开头用于表示之前所有binlog执行过的事件合集的Previous-GTIDs，这个事件需要记录在binlog中，但是不需要发给slave。这就会让备库在接收到hb之后记录一个server_id=0的Rotate event。

主库relaylog

　　与此相关的，一个可能出现的现象是双M单写场景下，备库没有更新，但是主库会一直写relay log。

　　步骤如下：

　　1、主备之间完成MM关系(GTID_MODE=on)

　　2、主库和备库各自stop slave

　　3、主库执行大量更新

　　4、主库start slave

　　5、备库start slave

　　在备库同步日志过程中生成了本地的binlog，这些binlog需要再发回给主库。5.6的一个机制是，如果发现通道对面的接收方的executed_set已经包含了这个事件，则不发送。

　　由于这些事件本身就是主库发送过来的，因此备库都不需要发回。但是备库必须通知主库本地的binlog的最新位点，因此构造了一个hb event。

　　主库收到hb event后记录在relaylog中，形式就是server_id=0的Rotate事件。

MySQL·　性能优化·Bulk Load for CREATE INDEX

背景

MySQL5.6以后的版本提供了多种优化手段用于create index，比如online方式，Bulk Load方式。

Online提供了非阻塞写入的方式创建索引，为运维提供了很大的便利。

Bulk Load提升了索引创建的效率,减少了阻塞的时间。

这篇介绍下MySQL 5.7.5 Bulk Load的细节，并从查找，排序，redo，undo，page split等维度比较一下传统方式和Bulk Load方式。

传统方式

MySQL 5.7.5版本之前，create index使用的是和insert一条记录相同的api接口，即自上而下的插入方式。

步骤1: 扫描clustered index，得到行记录。

步骤2: 根据record，按照B-Tree的结构，从root->branch->leaf page查找到属于record的位置。:

步骤3: 调用write index record接口，维护索引。

1. 查找: 对每一条记录在插入前从B-Tree上查找到自己的位置。

2. 排序: 因为是按照B-Tree的结构，所以每一条记录插入都是有序的。

3. redo: 每条记录的插入都会记录innodb的redo做保护。

4. undo: 记录每个插入记录位置的undo

5. page split: 插入采用optimistic的方式，如果失败而发现page full，那么就split page，并向上更新branch page。

从上面的步骤和几个维度的说明上，传统的create index比较简单，但一方面会阻塞写入，另一方面效率会比较低，延长了不可用时间。

Bulk Load方式

MySQL 5.7.5 版本，提供了Bulk Load方式创建索引，使用多路归并排序和批量写入的方法，是一种自下而上的方式。

步骤1: 扫描clustered index，写入sort buffer，等sort buffer写满了后，写入到临时文件中。

步骤2: 对临时文件中的有序记录进行归并排序。

步骤3: 批量写入到索引结构中。

批量写入: 因为记录都是有序的，所以写入的过程就是，不断的分配leaf page，然后批量写入记录，并保留innodb_fill_factor设置的空闲空间大小，所以，就是不断在最右边leaf page写入，并不断进行平衡B-Tree结构的过程。

1. 查找: Bulk Load方式并没有单条record查找的过程。

2. 排序: 使用多路归并排序，对待写入的records进行排序。

3. redo: Innodb并没有记录redo log，而是做checkpoint直接持久化数据。

4. undo: 记录了新分配的page。

5. page split: 因为每次都是初始化一个最右端的page，create index的时候不存在split。

从上面的步骤和几个维度的说明上，Bulk Load方式能显著的利用机器的吞吐量，加快创建index的过程。

问题及与Oracle的比较

1. 临时空间使用

MySQL使用临时目录来保存临时文件，对于文件的大小受限于目录空间大小，需要注意。RDS通过增加一个参数来控制临时空间的使用。

Oracle使用临时表空间，如果排序空间不足，则会遇到常见的错误：ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

2. redo保护

MySQL 的Bulk Load方式，没有使用redo保护，数据库从write-ahead logging方式退化成direct persist data，并且未来如果MySQL希望使用Innodb redo的方式进行复制，也变的困难。

Oracle如果不指定no logging参数，索引创建过程中记录完整的redo信息。

3. direct write

MySQL Bulk Load方式，对于新的leaf page，在创建的过程中，唤醒page cleaner线程对这些page做checkpoint进行持久化。

Oracle提供一种用户的服务器进程直接direct write物理文件的方式，写入数据，而不依赖DBWR进程。

MySQL·　捉虫动态·Opened tables block read only

背景

MySQL通过read_only参数来设置DB只读，这样MySQL实例就可以作为slave角色，只应用binlog，不接受用户修改数据。这样就可以保护master-slave结构中的数据一致性，防止双写风险。

global read_only的实现方式

MySQL5.5版本通过三个步骤来设置read_only：

步骤1：获取global read lock，阻塞所有的写入请求

步骤2：flush opened table cache，阻塞所有的显示读写锁

步骤3：获取commit lock，阻塞commit写入binlog

步骤4：设置read_only=1

步骤5：释放global read lock和commit lock。

MySQL 5.5的版本，通过这5步完成设置read only的功能。

Bug描述

比如如下场景：

 session1：
 lock table t read;
 
 session2：
 set global read_only=1;

先执行session1，然后session2会一直被session1阻塞。

原因是：session1的显示锁，虽然与步骤1中的global read lock相容，但session2因为session1一直持有读锁并保持t表opened而被阻塞。

但是，实际上，显示的读写锁产生的opened table并不影响read_only的功能，这里的flush tables也并非是必须的。

这也是我们的实际应用环境中，因主备切换而要在master实例上设置read_only的时候，经常被大查询所阻塞的原因。

修复方法

修复方法非常简单，只需要把步骤2删除即可，不影响read only的语义。

官方在MySQL 5.6.5中进行了修复：

If tables were locked by LOCK TABLES ... READ in another session, SET GLOBAL read_only = 1 failed to complete. (Bug #57612, Bug #11764747)

RDS功能增强

设置read_only阻塞用户写入，但只能阻塞普通用户的更新，RDS为了最大可能的保护数据一致性，增强了read_only功能，通过设置super read only，阻塞除了slave线程以为的所有用户的写入，包括super用户。

MySQL·　优化改进· GTID启动优化

背景

GTID 可以说是 MySQL 5.6 版本的一个杀手级特性，它给主备复制带来了极大的便利，RDS只读实例就是强依赖于这个特性。然而GTID在给我们带来便利的同时，也埋下了许多坑，最近几期内核月报中GTID的频繁出现也说明了这一点，对其我们可以说是既爱又恨。

GTID 并不是免费午餐，要使用它是要有代价的，为了保证GTID这个体系能够运转起来，需要做许多相关的工作，比如binlog里每个事务要多记 gtid_event 这种事件、写binlog的时候要生成 gtid、要维护几个GTID集合(logged, purged, owned)、THD类要多加GTID的成员变量等等，这些对性能和资源开销方面都有影响。

官方的最新代码中加入了一个关于GTID的优化，是在mysqld启动的时候，加快 gtid_set 初始化的速度，详见revno: 6110。关于GITD集合，最重要的有2个，一个是 gtid_executed，另一个是gtid_purged，很多数据库运维相关的操作都要和这2个集合打交道，前者对应当前实例已经执行过的事务集合，后者对应已经执行过，但是已经不在binlog中的事务集合。mysqld 正常运行时，这2个集合是在内存中持续更新的，可是重启的时候，需要初始化这2个集合，因为并没有专门的地方记录这2个集合，初始化是通过读取binlog进行的。

优化分析

mysqld 是通过对 binlog.index 中记录的 binlog 文件做2次遍历来实现初始化的，第一次是从后向前，即从最新的binlog开始，到最老的binlog，对每个binlog从头到尾读一遍，初始化 gtid_executed 集合；第二次是从前往后，同样对每个binlog从头到尾读一遍，用来初始化gtid_purged 集合。每一遍的最好情况都是只读一个binlog文件，对gtid_executed 集合来说，只需要最新的binlog就行了，因为每个binlog开始会记录 previous_gitd_set，这个集合加上当前binlog内部记录的 gtid_event，就是所有已经执行的，也即 gtid_executed；对gitd_purged来说，理想情况更简单，只需要读最老binlog文件的头部的previous_gtid event即可，文件里面的 gtid_event 根本不需要。

最坏情况是什么呢，就是一堆binlog文件里，只有其中一个文件里有gtid，其它都没有，这样的话，对于2遍扫描，都需要扫到这个binlog，才能确定这2个集合。

比如 a b c D e f 这几个，每个对应一个binlog文件，其中只有D含有gtid，其它的都没有，这样的话，每一遍的扫描都要读到文件D才能确定。

官方的优化是，不管什么情况下，每一遍的扫描，最多只读一个文件，不会再多读，如果最新和最老的文件都没有gtid，就把gtid_executed和gtid_purged设为空。

优化场景

下面我们来看下，这个优化有没有用。
我们还是用 a b c d e f 这几个表示binlog文件，小写表示文件没有包含gtid，大写表示有。

开始没有开gtid，后来开了：a, b, c, d, e, F 这样的模式，gtid_executed 只读 F，gtid_purged 只读a, 前者是F全集，后者是空的，如果没有这个优化的话，gtid_executed 也是读一个文件，gtid_purged 要从a读到F，最终还是空的，优化是有效果的
开始有，后来没有：A, b, c, d, e, f，这种情况下 gtid_executed 集合被初始化成空集，gtid_purged 也是空集，初始化结果是错的
开始没有，中间有最后也没有：a, b, c, D, e, f 这种情况，2个集合都被初始化成空的，结果也是错的
一直有：A，B，C，D，E，F，这种本来就是最好情况，本来每次遍历就只读一个文件的，加不加这个优化都一样

其它情况可以自己推算下

总的来说这个优化是比较鸡肋的，有的情况下还会算错，官方的优化 patch 带了个开关控制，默认是关的，这个只是对个别场景比较适合，比如上面的场景1。

TokuDB· Binary Log Group Commit with TokuDB

MySQL在开启Binary Log的情况下，使用2PC(图1)来保证事务(XA)完整性的，每次提交事务需要做:

 1) prepare phase:
     记录prepare信息到引擎层的Redo Log，fsync到磁盘
 2) commit phase:
     A) 记录commit信息到MySQL Server层的Binary Log，fsync到磁盘
     B) 记录commit信息到引擎层的Redo Log，fsync到磁盘

每个事务在提交的时候都要做3次fsync以确保日志真正的落盘，这样在log里，一个事务就会有3种状态:

 状态1: Redo Log里存在，Binary Log里也存在      --正常情况，crash恢复时需要commit
 状态2: Redo Log里存在，Binary Log里不存在      --prepare完毕后发生crash，恢复时需要rollback
 状态3: Redo Log里不存在，Binary Log里也不存在   --提交失败，无需处理

MySQL内核月报

这样，无论处于任何一个状态，事务的完整性都不会被破坏，但是每次提交会产生3次fsync，性能非常低。
为了提升性能，MySQL 5.6增加了group commit功能，当多个事务并发提交时，让多个都在等待fsync的事务合并做一次fsync，大大提升了吞吐量。
但是这个优化还需要引擎层的配合，引擎层需要＂一切行动听指挥＂，不要＂任性＂的做fsync，需要对当前THD做HA_IGNORE_DURABILITY判断，代码如下:

 static bool tokudb_fsync_on_commit(THD *thd) {
 #if MYSQL_VERSION_ID >= 50600
     if (thd_get_durability_property(thd) == HA_IGNORE_DURABILITY)
         return false;
     else
 #endif
         return THDVAR(thd, commit_sync) != 0;
 }

TokuDB 7.5.4版本即将包含这个特性，官方透露，600 tokudb commits/sec只产生120个tokudb fsyncs。

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：Advanced Oracle SQL: Subquery Inline ViewNext article：mysql的root用户被删除, MySQL 服务无法启动 1067错误

See more

MySQL内核月报

MySQL· 性能优化·5.7 Innodb事务系统

MySQL· 踩过的坑·5.6 GTID 和存储引擎那会事

MySQL· 性能优化·thread pool 原理分析

MySQL· 性能优化·并行复制外建约束问题

MySQL· 答疑释惑·binlog event有序性

MySQL· 答疑释惑·server_id为0的Rotate

MySQL· 性能优化·Bulk Load for CREATE INDEX

MySQL· 捉虫动态·Opened tables block read only

MySQL· 优化改进· GTID启动优化

TokuDB· Binary Log Group Commit with TokuDB

Related articles

MySQL·　性能优化·5.7 Innodb事务系统

MySQL·　踩过的坑·5.6 GTID 和存储引擎那会事

MySQL·　性能优化·thread pool 原理分析

MySQL·　性能优化·并行复制外建约束问题

MySQL·　答疑释惑·binlog event有序性

MySQL·　答疑释惑·server_id为0的Rotate

MySQL·　性能优化·Bulk Load for CREATE INDEX

MySQL·　捉虫动态·Opened tables block read only

MySQL·　优化改进· GTID启动优化