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

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

提要

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;
   返回0

  上述中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).

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
471 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
27天前
|
SQL 关系型数据库 MySQL
数据库:MYSQL参数max_allowed_packet 介绍
数据库:MYSQL参数max_allowed_packet 介绍
20 2
|
6月前
|
存储 监控 关系型数据库
MySQL 参数innodb_read_io_threads
`innodb_read_io_threads` 是 MySQL 数据库中 InnoDB 存储引擎的一个配置参数,它用于指定后台线程池中用于处理读取 I/O 请求的线程数量。InnoDB 存储引擎负责管理数据库的物理存储和检索,是 MySQL 最常用的存储引擎之一。 ### 参数说明 - **名称**: `innodb_read_io_threads` - **默认值**: 4 - **范围**: 1 到 64 - **动态修改**: 不能动态修改(需要重启服务器) - **适用版本**: MySQL 5.6 及以上版本 ### 作用 `innodb_read_io_threads`
549 1
|
6月前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
254 0
|
4月前
|
分布式计算 关系型数据库 MySQL
MySQL超时参数优化与DataX高效数据同步实践
通过合理设置MySQL的超时参数,可以有效地提升数据库的稳定性和性能。而DataX作为一种高效的数据同步工具,可以帮助企业轻松实现不同数据源之间的数据迁移。无论是优化MySQL参数还是使用DataX进行数据同步,都需要根据具体的应用场景来进行细致的配置和测试,以达到最佳效果。
|
4月前
|
存储 缓存 关系型数据库
Mysql/etc/my.cnf参数详解
以上只是 `/etc/my.cnf`中的部分参数,实际上,`/etc/my.cnf`中的参数非常多,可以根据具体的应用需求进行调整。
79 0
|
6月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之使用 MySQL CDC 进行数据同步时,设置 server_id 参数如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
5月前
|
存储 Oracle 关系型数据库
【MySQL技术内幕】3.1-参数文件
【MySQL技术内幕】3.1-参数文件
35 0
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库——存储过程-if条件判断、参数、case(介绍、用法、案例)
MySQL数据库——存储过程-if条件判断、参数、case(介绍、用法、案例)
317 0
|
6月前
|
存储 SQL 关系型数据库
MySQL的参数optimizer_switch
`optimizer_switch`是MySQL系统变量,用于控制查询优化器行为。它由键值对组成,如`index_merge=on/off`,用于开启或关闭特定优化功能。要查看当前设置,运行`SHOW VARIABLES LIKE &#39;optimizer_switch&#39;;`,修改则用`SET`命令,如`SET optimizer_switch=&#39;index_merge=off&#39;;`。
177 1

相关产品

  • 云数据库 RDS MySQL 版