表明不能在事务中创建临时表，可以在事务外创建，但要求 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;这样的语句，脚本执行失败，所以mysql.db 、mysql.user 和 mysql.proxies_priv表里的初始数据就没有添加进去。
error log 中会看到这样的信息
2014-12-08 20:48:15 92 [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 92 [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 处理客户端连接的方式会触发mysql 新建一个线程来处理新的连接，新建的线程会处理该连接所发送的所有 SQL 请求，即 one-thread-per-connection 的方式，其创建连接的堆栈为：
mysqld_main handle_connections_sockets create_new_thread create_thread_to_handle_connection handle_one_connection
线程建立后，处理请求的堆栈如下：
优点及存在的问题
在连接数较小的情况下可以很快的响应客户端的请求，但当连接数非常大时会创建很多线程，这样会引起以下问题：
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 处理请求的堆栈如下：
其中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 主备同步是通过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;以下两个语句在备库的执行顺序不同，结果会不同
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用来更新其他数据存储的应用来说，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的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事件。
背景
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接口，维护索引。
从上面的步骤和几个维度的说明上，传统的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结构的过程。
从上面的步骤和几个维度的说明上，Bulk Load方式能显著的利用机器的吞吐量，加快创建index的过程。
问题及与Oracle的比较
1. 临时空间使用
MySQL使用临时目录来保存临时文件，对于文件的大小受限于目录空间大小，需要注意。RDS通过增加一个参数来控制临时空间的使用。 Oracle使用临时表空间，如果排序空间不足，则会遇到常见的错误：ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2. 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通过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 #117747)
RDS功能增强
设置read_only阻塞用户写入，但只能阻塞普通用户的更新，RDS为了最大可能的保护数据一致性，增强了read_only功能，通过设置super read only，阻塞除了slave线程以为的所有用户的写入，包括super用户。
背景
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，大写表示有。
其它情况可以自己推算下
总的来说这个优化是比较鸡肋的，有的情况下还会算错，官方的优化 patch 带了个开关控制，默认是关的，这个只是对个别场景比较适合，比如上面的场景1。
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里也不存在 --提交失败，无需处理
这样，无论处于任何一个状态，事务的完整性都不会被破坏，但是每次提交会产生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。
下载本文