MySQL内核月报 2014.08-MySQL·　参数故事·timed_mutexes

提要

MySQL 5.5.39 Release版本正式从源码里删除了全局参数timed_mutexes。timed_mutexes原本用来控制是否对Innodb引擎的mutex wait进行计时统计，以方便进行性能诊断。为什么要删除这个参数呢？ 下面介绍下相关背景：

Innodb的同步锁机制

Innodb封装了mutex和rw_lock结构来保护内存的变量和结构,进行多线程同步，考虑可移植性， mutex使用lock_word或者OS mutex来保证原子操作，并使用event条件变量进行阻塞和唤醒操作。

 os_event_t event;

 volatile lock_word_t lock_word;

 os_fast_mutex_t os_fast_mutex;

Innodb同步锁引入的数据结构和开销

1. 全局mutex链表

Innodb引入了一个全局的链表ut_list_base_node_t mutex_list，并使用一个单独的mutex来保护链表。 所有的mutex在create或者free的时候来修改链表，有了全局链表，也使统计汇总有了可能性，参考命令“show engine innodb mutex”. 虽然需要维护一个全局的链表，但这并不会影响太多的性能，因为大部分的mutex的生命周期都是从Innodb启动一直到shutdown。

2. 统计信息

mutex的结构中，有几个统计信息：

 count_os_wait：请求mutex进入等待的次数

 count_using: 请求mutex的次数

 count_spin_loop: 请求mutex时spin的轮数

 count_spin_rounds: 请求mutex的spin次数

 count_os_yield:请求mutex spin失败后os等待次数

 lspent_time: 统计等待mutex的时间

lock mutex的主要步骤：

 1. 首先trylock mutex，如果没有获取到mutex，并不马上进行wait，而是进行spin。

 2. 尝试spin，如果在SYNC_SPIN_ROUNDS次后，仍然没有lock，那么就进入等待队列，等待唤醒。

在MySQL5.5的版本里，非UNIV_DEBUG模式下，Innodb仅仅保留了count_os_wait的次数，这也是为了性能的考虑。所以5.5的版本后， timed_mutexes在Release下，其实已经不再起作用，所以5.5.39，以及5.6以后，源码里都不再保留timed_mutexes。 要么在debug模式下，启用这些统计，但上线版本又不可能使用DEBUG模式，所以对于mutex的统计，MySQL在后面的版本中使用了performance_schema的等待事件来代替，即:

 mysql SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

 - WHERE TABLE_SCHEMA = performance_schema

 - AND TABLE_NAME LIKE %instances;

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

 | TABLE_NAME |

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

 | cond_instances |

 | file_instances |

 | mutex_instances |

 | rwlock_instances |

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

3. 全局等待队列

Innodb为所有的等待状态的线程准备了一个队列，如果获取mutex失败，那么就申请一个cell，进入阻塞状态，等待signal。 sync_primary_wait_array，有了这个全局的队列，Innodb就可以对这些wait的线程进行统计，比如long semaphore waits就是根据这个队列进行的查询。

4. signal丢失

这里再讨论下signal丢失的情况，我们重新再看下lock mutex的步骤：

 线程1：

 1. try lock mutex

 2. fail，然后spin

 3. fail，然后进入队列，然后wait event

 线程2：

 1.own mutex

 2.free mutex

 3.signal event

如果按照这个时序，在线程2 signal event后，线程1才进入队列，那么线程1就永远处在阻塞状态，无法唤醒。为了解决signal丢失的情况， Innodb启动了一个后台线程：sync_arr_wake_threads_if_sema_free，每隔1s就轮询wait数组，如果可以lock，就signal这个event来唤醒线程。

从上面来看，Innodb为了mutex和rwlock的移植性，以及为了监控和诊断，添加了多个全局的数据结构，这样实时的统计才有可能，但也带来了维护数据结构的开销。 而timed_mutexes控制的mutex wait时间统计，因为只在debug模式下进行编译，而且5.6以后使用performance schema的等待事件进行替代，所以参数做了删除处理。

MySQL· 参数故事·innodb_flush_log_at_trx_commit

背景

innodb_flush_log_at_trx_commit 这个参数可以说是InnoDB里面最重要的参数之一，它控制了重做日志（redo log）的写盘和落盘策略。 具体的参数意义见手册

简单说来，可选值的安全性从0- 2- 1递增，分别对应于mysqld 进程crash可能丢失 - OS crash可能丢失 - 事务安全。

以上是路人皆知的故事，并且似乎板上钉钉，无可八卦。

innodb_use_global_flush_log_at_trx_commit

直到2010年的某一天，Percona的CTO Vadim同学觉得这种一刀切的风格不够灵活，最好把这个变量设置成session级别，每个session自己控制。

但同时为了保持Super权限对提交行为的控制，同时增加了innodb_use_global_flush_log_at_trx_commit参数。 这两个参数的配合逻辑为：

1、若innodb_use_global_flush_log_at_trx_commit为OFF，则使用session.innodb_flush_log_at_trx_commit;

2、若innodb_use_global_flush_log_at_trx_commit为ON,则使用global .innodb_flush_log_at_trx_commit（此时session中仍能设置，但无效）

3、每个session新建时，以当前的global.innodb_flush_log_at_trx_commit 为默认值。

业务应用

这个特性可以用在一些对表的重要性做等级定义的场景。比如同一个实例下，某些表数据有外部数据备份，或允许丢失部分事务的情况，对这些表的更新，可以设置 Session.innodb_flush_log_at_trx_commit为非1值。

在阿里云RDS服务中，我们对数据可靠性和可用性要求更高，将 innodb_use_global_flush_log_at_trx_commit设置为ON，因此修改session.innodb_flush_log_at_trx_commit也没有作用，统一使用 global.innodb_flush_log_at_trx_commit = 1。

MySQL· 捉虫动态·Count(Distinct) ERROR

背景

MySQL现行版本中存在一个count(distinct)语句返回结果错误的bug，表现为，实际结果存在值，但是用count(distinct)统计后返回的是0。

 drop table if exists tb;

 set tmp_table_size=1024;

 create table tb(id int auto_increment primary key, v varchar(32)) charset=gbk;

 insert into tb(v) values("aaa");

 insert into tb(v) (select v from tb);

 insert into tb(v) (select v from tb);

 insert into tb(v) (select v from tb);

 insert into tb(v) (select v from tb);

 insert into tb(v) (select v from tb);

 insert into tb(v) (select v from tb);

 insert into tb(v) (select v from tb);

 insert into tb(v) (select v from tb);

 update tb set v=concat(v, id);

 select count(distinct v) from tb;

 上述中update语句的目的是将所有的v值设为各不相同。

原因分析

Count(distinct f)的语义就是计算字段f的去重总数，计算流程大致如下：

流程一:

1、 构造一个unique集合A1（用tree实现） 2、 对每个值都试图插入集合A1中 3、 若和A1中现有item重复则直接跳过，不重复则插入并+1 4、 完成后计算集合中元素个数。

细心的同学会看到上面的语句中有一个set tmp_table_size的过程，集合A1并不能无限扩大，大小上限为tmp_table_size。若超过则上述流程变为

流程二：

1、 构造一个unique 集合A1 2、 插入item过程中若大小超过tmp_table_size，则将A1暂时写到文件中，再构造集合A2 3、 重复步骤2直到所有的item插入完成 因此若item很多则可能重复生成多个集合A1～An。 4、 对A1～An作合并操作。由于只是每个集合A保证unique，因此需要做类似归并排序的操作（实际上不需要排序，只是扫一遍） 5、 因此合并操作需要一个临时内存，长度为n，单元大小为key_length （key大小）。这个临时内存，用的也是tmp_table_size定义的大小。实际上在合并过程中还需要长为key_length的预留空间作临时内存保存。因此需要的空间为 (n+1)*key_length。 6、 在进行合并前会判断tmp_table_size =(n+1)*key_length， 不满足则直接放弃合并。其结果就是返回为0。

案例分析

以上面这个case为例。字段v的单key大小为65 (65 = 32*2+1) 加上tree节点字占空间24字节共89字节。单个集合只能放11个item （1024/89）， 因此n为 24 （24 =256/11）, 在合并时需要 (24+1)*65= 1625字节的临时空间，大于1024，放弃合并。

Sql_big_tables

实际上在最初处理这个问题时，DBA同学发现社区也有人讨论这个bug，并且指出在set sql_big_tables=on的时候，执行count(distinct)就能正确返回结果。原因就是在sql_big_tables=on的情况下，构造集合的方式是直接生成一个临时表，全部插入后直接计算临时表的大小作为结果，整个过程与tmp_table_size无关。

解决方法

运维上，set sql_big_tables是一个方法，不过会影响性能。调高tmp_table_size算是正招。当然本质上这是一个bug。 代码上，对于已经走到合并操作的这个逻辑，如果tmp_table_size不够，应该直接申请新的临时空间用于合并，完成后释放。虽然会造成临时征用内存，不过以现有的逻辑来看，临时征用的内存已经不少了.

另外一种时间换空间的方法，就是作多次合并。

相比之下第一种改造比较简单安全。该bug在RDS MySQL 5.5 中已经修复。

MySQL· 捉虫动态·mysqldump BUFFER OVERFLOW

bug背景

在上个月发布的新版本中，官方修复了一个mysqldump输入库名或表明长度越界的bug。

在MySQL的当前约束中，库名和表名字符串最大长度为NAME_LEN=192字节。在myqldump实现中，需要对输入的表名做处理，比如增加``防止表名中的特殊字符。这些临时处理的内存，声明为类似name_buff[NAME_LEN+3],这样在用户输入的库名或表名长度过长时，会造成数组越界读写，导致不可预期的错误。

这个修复的逻辑也比较简单，就是在开始dump前作参数检查，若发现长度超过NAME_LEN的库/表名，直接抛错返回“argument too long”。

细节说明

需要注意的是，该修复改变了mysqldump的行为。由于名字长度超过NAME_LEN的库/表肯定不存在，因此修复之前的逻辑，是报告该表不存在。“table not exists”这个逻辑是可以通过--force 跳过的。而“argument too long”则无视force参数，直接抛错返回。

MySQL· 捉虫动态·long semaphore waits

现象描述：

Innodb引擎，父表和子表通过foreign constraint进行关联，因为在更新数据时需要check外键constraint，当父表被大量的子表referenced时候，那么在open Innodb数据字典的时候，需要open所有的child table和所有的foreign constraint，导致持有dict_sys- mutex时间过长，产生long semaphore wait, 然后innodb crash了。

case复现

 CREATE TABLE `t1` (

 `f1` int(11) NOT NULL,

 PRIMARY KEY (`f1`)

 ) ENGINE=InnoDB

 CREATE TABLE `fk_1` (

 `f1` int(11) NOT NULL,

 PRIMARY KEY (`f1`),

 CONSTRAINT `pc1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON DELETE CASCADE ON UPDATE CASCADE

 ) ENGINE=InnoDB

 ......

 这里建了fk_[0-10000]张表。

分析过程

1. 数据字典

innodb使用系统表空间保存表相关的数据字典，系统的数据字典包括：

 SYS_TABLES

 SYS_INDEXES

 SYS_COLUMNS

 SYS_FIELDS

 SYS_FOREIGN

 SYS_FOREIGN_COLS

 SYS_STATS

在load某个表的时候，分别从这些表中把表相关的index,column, index_field, foreign, foreign_col数据保存到dictionary cache中。 对应的内存对象分别是：dict_col_struct，dict_field_struct，dict_index_struct，dict_table_struct，dict_foreign_struct。

2. open过程

dict_load_table：

 1. 通过sys_tables系统表，load table相关的定义

 2. 通过sys_indexes系统表，根据table_id load 所有相关index

 3. 通过sys_columns系统表，根据table_id load 所有的columns

 4. 通过sys_fields系统表，根据index_id load 所有index的field

 5. 通过sys_foreign系统表，load所有关联的表和foreign key

3. load foreign的详细过程

3.1 根据表名t1 查找sys_foreign.

而sys_foreign表上一共有三个索引：

 index_1(id): cluster_index

 index_2(for_name): secondary_index

 index_3(ref_name): secondary_index

所以，根据for_name=t1, ref_name=t1检索出来所有相关的foreign_id.

3.2 加入cache

因为没有专门的cache，foreign分别加入到for_name- foreign_list, ref_name- referenced_list。 问题的关键：因为foreign是全局唯一的，但foreign又与两个表关联，所以，有可能在open 其它表的时候已经打开过，所以，create foreign对象后，需要判断以下四个list，是否已经存在，如果存在就直接使用。

dict_foreign_find：分别查询这四个list，如果已经存在，则free新建的foreign对象，引用已经存在的。

 for_name- foreign_list

 for_name- referenced_list

 ref_name- foreign_list

 ref_name- referenced_list

如果不存在，把新建的foreign加入到for_name- foreign_list，ref_name- referenced_list链表中。

4. 问题的原因：

因为第一次load，所以find都没有找到，但这四个都是list，随着open的越来越多，检索的代价越来越大。 而整个过程中，都一直持有trx_sys- mutex，最终导致了long semaphore wait。

5. 问题改进方法：

在MySQL 5.5.39版本中，进行了修复，修复的方法就是，除了foreign_list，referenced_list。 另外又增加了两个red_black tree，如下源码所示：

 struct dict_table_struct{

 table_id_t id; /*! id of the table */

 mem_heap_t* heap; /*! memory heap */

 char* name; /*! table name */

 UT_LIST_BASE_NODE_T(dict_foreign_t)

 foreign_list; /*! list of foreign key constraints in the table; these refer to columns in other tables */

 UT_LIST_BASE_NODE_T(dict_foreign_t)

 referenced_list;/*! list of foreign key constraints which refer to this table */

 ib_rbt_t* foreign_rbt; /*! a rb-tree of all foreign keys listed in foreign_list, sorted by foreign- id */

 ib_rbt_t* referenced_rbt; /*! a rb-tree of all foreign keys listed in referenced_list, sorted by foreign- id */

这样dict_foreign_find的过程中，通过red_black tree进行检索，时间复杂度降到O(log n).

TDSQL中修复的mysql内核bug 在TDSQL这两年多的开发工作中，我感觉很自豪的一件事是我修复了不少mysql-5.7.17和mariadb-10.1.9的内核bug，这些bug大多已经报告给了MySQL/MariaDB官方开发团队，在每个bug描述中我会贴出来bug报告的连接。本文将大略介绍这些bug的概况，我在将来会写更多文章详细介绍每个bug的具体问题分析以及解决思路。本文列出的所有bug都已经修复，经过验证可以正确工作并解决相关问题。 这里先说一下为什么我要提交代码给mysql/mariadb官方开发团队，主要有一下几个好处： 1. 官方开发者可以review我提交的patch，帮助完善patch，发现和解决之前
MySQL · 内核特性 · 统计信息的现状和发展 简介我们知道查询优化问题其实是一个搜索问题。基于代价的优化器 ( CBO ) 由三个模块构成：计划空间、搜索算法和代价估计 [1] ，分别负责“看到”最优执行计划和“看准”最优执行计划。如果不能“看准”最优执行计划，那么优化器基本上就是瞎忙活，甚至会产生严重的影响，出现运算量特别大的 SQL ，造成在线业务的抖动甚至崩溃。在上图中，代价估计用一个多项式表示，其系数 c 反应了硬件环境和算子特性，而
关于MySQL内核，一定要知道的！ 近一个多月，写了一些MySQL内核的文字，稍作总结，希望对大家有帮助。1.《InnoDB，为何并发如此之高？》 文章介绍了： （1）什么是并发控制； （2）并发控制的常见方法：锁，数据多版本； （3）redo，undo，回滚段的实践； （4）InnoDB如何利用回滚段实现MVCC，实现快照读。
MySQL · 引擎特性 · MySQL内核对读写分离的支持 读写分离的场景应用 随着业务增长，数据越来越大，用户对数据的读取需求也随之越来越多，比如各种AP操作，都需要把数据从数据库中读取出来，用户可以通过开通多个只读实例，将读请求业务直接连接到只读实例上。使用RDS云数据库的读写分离功能，用户只需要一个请求地址，业务不需要做任何修改，由RDS自带的读写分离中间件服务来完成读写请求的路由及根据不同的只读实例规格进行不同的负载均衡，同时当只读实例出现故障时能够主动摘除，减少对用户的影响。
MySQL · 引擎特性 · Group Replication内核解析之二 前文已经介绍了MySQL的Group Replication的实现机制和原理，本文就Group Replication的具体实现进行详细的阐述，以更深入的理解Group Replication的机制，在实践中更好的应用Group Replication，提升应用系统的可用性，优化其性能。
db匠 rds内核团队秘密研发的全自动卖萌机. 追加特效: 发数据库内核月报. 月报传送: http://mysql.taobao.org/monthly/