MySQL数据库InnoDB存储引擎中Adaptive hash index存在问题、Percona改进及一个bug

背景
Adaptive hash index (AHI)是MySQL数据库InnoDB存储引擎中用于加速索引查找的一个结构。InnoDB本身不支持hash索引,所有的索引检索都走B树查询。AHI可以认为是“索引的索引”。当对一个页面的访问次数满足一定条件后,将这个页面的地址存在一个hash表中,下次查询可以直接访问到页面,不需要走B树查询。

问题
天下没有免费的午餐,在加速查询的同时,AHI与其他缓存结构一样,也面临维护的问题。作为一个全局结构,在更新时必然有一个全局锁操作(btr_search_latch),一个查询里面可能会对其作多次加x_lock操作。

Percona的改进
如同Buffer Pool可以用多个instance减少锁冲突,Percona也用类似的策略来处理AHI的锁问题。由于本身就是Hash结构,这个处理既自然又方便。所有的数据节点都必然属于一个索引,AHI就用索引id来作分区的key(block->index->id).计算规则为(index_id % btr_search_index_num), 这个 btr_search_index_num 就是 Percona中引入的只读参数 innodb_adaptive_hash_index_partitions。
全局的btr_search_latch则分为btr_search_index_num 份,AHI中每个block中新增一个成员指向 btr_search_latch_part[i].

MySQL数据库量化InnoDB group commit的效果

前几天有位开发的同学问了个问题,InnoDB的group commit效果如何?之前说好了回头给看下,结果险些拖过年。

Group commit背景
         InnoDB的redo log的group commit历史比较悠久了(有别于binlog的group commit)。如果设置为1,每次事务提交都至少需要写一次redolog。这对IOPS冲击严重,尤其是在HDD上,直接成为性能瓶颈。
Group commit的基本想法是将多个并发线程对redo的fsync操作合并成1个。具体的过程可以参照这篇

Group commit的效果

         其实效果好不好全看具体场景。这里我们先给出一种直观看结果的方法,然后再举例子分析。

1)       数据
简单表结构

MySQL数据库5.6 explain update一个疑似bug

5.6 的新增特性,允许对DML语句做explain。这下大家高兴了,碰到复杂更新语句(且还造成慢查询)要自己手动改成select语句的日子终于到头了。

饶有兴致的试用了一把,总体感觉不错,不过发现一个bug。

复现    

mysql> create table tb(id int primary key , c int);
Query OK, 0 rows affected (0.01 sec)mysql> insert into tb values(1,1);
Query OK, 1 row affected (0.00 sec)mysql> insert into tb values(2,2);
Query OK, 1 row affected (0.00 sec)mysql> explain select * from tb where id=1;
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
|  1 | SIMPLE      | tb    | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
1 row in set (0.00 sec)

mysql> explain update tb   tb set c=2 where id=1;
+—-+————-+——-+——-+—————+———+———+——+——+————-+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+—-+————-+——-+——-+—————+———+———+——+——+————-+
|  1 | SIMPLE      | tb    | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
+—-+————-+——-+

 这里我们看到,在explain update的结果中,type=range,并且Extra是Using where。

MySQL数据库的字符集和copy_and_convert 字符集不同导致CPU资源额外消耗

关于copy_and_convert
在对MySQL做业务压力测试的时候,我们在perf结果中发现 copy_and_convert 是一个耗费cpu的操作。这个函数的意思,就是在字符集之间做内容转换。
如果源和目标的字符集相同,就可以直接用memcpy,这显然比做字符集转换(按字节或字长拷贝更快,和节省cpu)

当整个系统是CPU瓶颈时,我们希望能够减少这种cpu消耗。

一次查询涉及的拷贝
如果我们执行一个简单的select语句,会涉及到两部分的内容:metadata和data。MySQL的返回包是能够“自解析”的,也就是说不仅有内容,还有描述信息。这些描述信息我们称为metadata。

在将这两部分信息返回给客户端的时候,就涉及到字符串拷贝,如果源字符集和目标字符集不同,就需要作转换。

MySQL数据库逻辑备份工具mysqldump的输出方式和进度报告功能增加与改进

mysqldump在导出数据的过程中是“沉默”的,为了显示导出进度,给它加一个小patch显示进度。感谢 @宁青_ 的需求。

一、mysqldump输出方式

这个patch比较简单,先说一下相关的一个背景:mysqldump的输出方式。
1、  默认方式 stdout
2、  指定文件
-r target_file 可以指定导出的内容写入文件target_file中
3、–tab=path
在path目录下,对每个表生成两个文件,如表a生成a.sql和a.txt,分别存储表的定义和表的内容。

注意在指定—tab参数时,使用的是select into outfile …因此对指定的用户权限要求更高。

二、进度报告patch

增加参数 –progress-report 后,执行效果如下:

MySQL数据库关于一次导入数据提示的MySQL server has gone away

背景

这个问题由一个同事问到的一次导入数据引发。一个很常见的操作,将数据从一个表中dump出来,在用mysql < a.sql的方式导入到另一个库的一个表中。

在执行导入的时候,提示 MySQL server has gone away。在追查的时候突然想到会不会是因为max_allowed_packet太小导致的。将max_allowed_packet改大,确实解决了问题。

本文基于在此之后想到的两个问题:

1、              MySQL server has gone away这个提示很不友好,是不是所有的包超过大小都是报这个?

2、              对于出现这种不友好的错误提示,有什么方法定位原因(而不是靠“突然想到”)?

追查1

MySQL数据库从load data引发的死锁说起

一个线上项目报的死锁,简要说明一下产生原因、处理方案和相关的一些点.
1、背景
         这是一个类似数据分析的项目,数据完全通过LOAD DATA语句导入一个InnoDB表中。为方便描述,表结构简化为如下:

Create table tb(id int primary key auto_increment, c int not null) engine=innodb;

导入数据的语句对应为
Load data infile ‘data1.csv’ into table tb;
Load data infile ‘data2.csv’ into table tb;

master_pos_wait函数与MySQL数据库主从复制切换

背景

  主从切换是高可用MySQL架构的必要步骤(即使用不发生,也要有备无患)。一般设置为双M(M1、M2),假设当前状态为写M1,而M2只读,切换的大致流程如下:

1、  停止应用写M1,将M1设置为只读
2、  检查M2的slave status直到赶上M1
3、  将M1设置为可写

其中在第2步细化为
a)       在M1上show master status;得到binlog位置P,因为已经设为只读,不会变化

MySQL数据库中like语句及相关优化器tips

背景

         MySQL中在对某个字段做包含匹配时可以用like

先看这个结构和结果

 

CREATE TABLE `tb` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `user_id` bigint(20) DEFAULT NULL,  `title` varchar(128) NOT NULL,

  `memo` varchar(2000) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `title` (`title`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

mysql> explain select * from tb where title like ‘%abcd%’;

+—-+————-+——-+——+—————+——+———+——+——+————-+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+—-+————-+——-+——+—————+——+———+——+——+————-+

|  1 | SIMPLE      | tb    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |

+—-+————-+——-+——+—————+——+———+——+——+————-+

1 row in set (1.65 sec)