MySQL数据库InnoDB存储引擎中Adaptive hash index存在问题、Percona改进及一个bug
三月 19, 2013 by 丁奇 · Leave a Comment
背景
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]. Read more
[MySQL 版本差异] 丢失frm文件之后drop database的不同结果分析
背景
今天在讨论frm丢失后drop database失败的问题。简单复现如下。假设test库中有两个表t1, t2，执行如下脚本：
bin/mysql -uroot –socket=run/mysql.sock -e ”
drop database if exists test2;
create database test2;
use test2;
create table t1 like test.t1;
create table t2 like test.t2;
”
rm -f data/test2/t1.frm; Read more
MySQL数据库量化InnoDB group commit的效果
二月 9, 2013 by 丁奇 · 2 Comments
前几天有位开发的同学问了个问题，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) 数据
简单表结构 Read more
MySQL数据库5.6 explain update一个疑似bug
二月 7, 2013 by 丁奇 · 2 Comments
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; |
这里我们看到，在explain update的结果中，type=range，并且Extra是Using where。 Read more
MySQL数据库的字符集和copy_and_convert 字符集不同导致CPU资源额外消耗
关于copy_and_convert
在对MySQL做业务压力测试的时候，我们在perf结果中发现 copy_and_convert 是一个耗费cpu的操作。这个函数的意思，就是在字符集之间做内容转换。
如果源和目标的字符集相同，就可以直接用memcpy，这显然比做字符集转换（按字节或字长拷贝更快，和节省cpu）
当整个系统是CPU瓶颈时，我们希望能够减少这种cpu消耗。
一次查询涉及的拷贝
如果我们执行一个简单的select语句，会涉及到两部分的内容：metadata和data。MySQL的返回包是能够“自解析”的，也就是说不仅有内容，还有描述信息。这些描述信息我们称为metadata。
在将这两部分信息返回给客户端的时候，就涉及到字符串拷贝，如果源字符集和目标字符集不同，就需要作转换。 Read more
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
十二月 13, 2012 by 丁奇 · 2 Comments
背景
这个问题由一个同事问到的一次导入数据引发。一个很常见的操作，将数据从一个表中dump出来，在用mysql < a.sql的方式导入到另一个库的一个表中。
在执行导入的时候，提示 MySQL server has gone away。在追查的时候突然想到会不会是因为max_allowed_packet太小导致的。将max_allowed_packet改大，确实解决了问题。
本文基于在此之后想到的两个问题：
1、 MySQL server has gone away这个提示很不友好，是不是所有的包超过大小都是报这个？
2、 对于出现这种不友好的错误提示，有什么方法定位原因（而不是靠“突然想到”）？
追查1 Read more
MySQL数据库从load data引发的死锁说起
十月 16, 2012 by 丁奇 · 7 Comments
一个线上项目报的死锁，简要说明一下产生原因、处理方案和相关的一些点.
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; Read more
master_pos_wait函数与MySQL数据库主从复制切换
十月 9, 2012 by 丁奇 · 2 Comments
背景
主从切换是高可用MySQL架构的必要步骤（即使用不发生，也要有备无患）。一般设置为双M（M1、M2），假设当前状态为写M1，而M2只读，切换的大致流程如下：
1、 停止应用写M1，将M1设置为只读
2、 检查M2的slave status直到赶上M1
3、 将M1设置为可写
其中在第2步细化为
a) 在M1上show master status;得到binlog位置P，因为已经设为只读，不会变化 Read more
MySQL数据库中like语句及相关优化器tips
九月 27, 2012 by 丁奇 · 2 Comments
背景
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) |
