安全研究 >> 安全研究详情

运维日记| MySQL/Oracle深度解析之一:逻辑读

作者: 美创运维中心数据库服务团队发布日期: 11月19日
前言

THE FIRST

比较数据库优劣、异同的文章有很多了,使用压测工具,进行不同压力下的测试,就能大致上比较出来哪种数据库是“最快”的数据库。但从有经验的数据架构、DBA等专业人士角度,仅仅“最快”是不够的,数据库是十分复杂的体系,要考虑方方面面的问题。

本文从“竞争”的角度,使用调试技术,在MySQL和Oracle的代码世界中畅游,像使用显微镜一样,以“最近”的距离,分析两种数据库由于代码设计的不同,而造成的竞争与锁机制的异同。

数据库的竞争与锁机制,是数据库最为复杂的部分,不可能在一篇短短的文章中描述完,本文以“逻辑读”为样本,从细枝末节中对比MySQL、Oracle的优劣、异同。逻辑读是数据库最基础的操作,也是最繁忙的操作。

我们先从MySQL入手,这里的研究对象当然是MySQL的InnoDB引擎。其他常用引擎比如Myisam根本没有缓存,没有数据库层面的逻辑读。

再说一下调试技术,它不同于直接一上来就开始的源码阅读,调试技术重点在于要读“活”着的源码。我们不去看磁盘文件中静止的.c、.cpp、.h等文件,而是要把程序运行起来,再结合源码,观察它运行起来的行为,理解源码其中的细枝末节。

有了对细节的探索,可以像他一样:

运维日记|MySQL/Oracle深度解析之一:逻辑读

否则,只能像他一样:

运维日记|MySQL/Oracle深度解析之一:逻辑读

第一节

基础知识介绍:MySQL中的HASH表与CBC Latch

MySQL的逻辑读,网上有相关函数,从buf_page_get_gen函数开始,一直到MySQL8都是这样。不过相比5.7,这个函数在MySQL8中有了很大的改动。我们后面的所有分析,都是针对MySQL 8。

数据页或块的缓存,在MySQL中称为Buffer Pool,在Oracle中称为Buffer Cache,下面统一简称数据缓存。无论MySQL还是Oracle,数据缓存的管理方式类似,如下图:

运维日记|MySQL/Oracle深度解析之一:逻辑读

其实不只MySQL、Oracle,此图适用于所有主流关系型数据库的数据缓存管理。

在MySQL中,HASH表由hash_cell_t型的结构组成,每一个Bucket,都是一个hash_cell_t型的结构变量。hash_cell_t的定义如下:

运维日记|MySQL/Oracle深度解析之一:逻辑读

plugin/innodb_memcached/innodb_memcache/include/innodb_utility.h的126行中你可以找到它的定义。

我建议你打开它看一看,这样,你也是读过MySQL源码的人了,酷不酷。

运维日记|MySQL/Oracle深度解析之一:逻辑读

每一个HASH Bucket都是一个void型的指针,也就是内存地址。在64位系统中,占8字节:

运维日记|MySQL/Oracle深度解析之一:逻辑读

每一个HASH Bucket后,都是一个链表,就是如下的部分:

运维日记|MySQL/Oracle深度解析之一:逻辑读

Oracle中称其为Cache Buffer Chains,简称CBC链。MySQL中没有专门的称呼,也称为CBC链吧,反正和Oracle都是一样的东西。

MySQL中,链表中的每个Node,都是buf_page_t型的类,它的定义在storage/innobase/include/buf0buf.h的1156行,这个就比较复杂了,如果不准备入调试技术的坑,只是看个热闹,我就不建议打开看了。它主要包含文件号、页号、下一个Node的地址、Buffer地址等成员变量。

数据库在HASH Table中搜索Buffer Pool中某个Buffer的过程,我们就不详细说了,有过很多资料讲述这一过程。MySQL中的相关代码在torage/innobase/include/buf0buf.ic的948行buf_page_hash_get_low函数中,代码量并不算复杂,读一下可以使你更拉轰。Oracle中参考《Oracle内核技术揭密》吧,第三章专门讲这个。

无论MySQL、Oracle,在HASH Table中搜索Buffer的过程都需要锁的保护。Oracle中称这个锁为Cache Buffer Chains Latch,简称CBC Latch,MySQL中也没有专门的称呼,我们也称它为MySQL的CBC Latch吧。

好了,基础知识介绍完毕。下面进入本次分享的最主要内容了,近距离分析MySQL、Oracle的异同。下面先从CBC Latch的数量开始。

第二节

MySQL CBC Latch数量造成的竞争

Oracle中CBC Latch数量是很多的(由隐藏参数“_db_block_hash_buckets”计算得到),缺省情况下根据你所设定的Buffer cache大小设定,至少几千个。Buffer Cache越大,CBC Latch数量也越多。

MySQL中呢,CBC Latch的数量就少的可怜了,16个(准确来说,是一个Buffer Pool Instance 16个。一个Buffer Pool Instance就是一个Buffer Pool的子池)。而且,这个数字并不会随着Buffer Pool加大而增大。再大的Buffer Pool,它也是16个。

运维日记|MySQL/Oracle深度解析之一:逻辑读

我猜测,可能是MySQL逻辑读时都是以共享方式持有CBC Latch。因此,MySQL的开发者认为CBC Latch的竞争不会太激烈,所以CBC Latch数量不必太多。

虽然逻辑读都是以共享的模式持有CBC Latch,但物理读可就要以独占方式持有CBC Latch了。如果只有16个CBC Latch的话,可以想像,在物理I/O比较多时,竞争一定是激烈的。

做个测试,验证一下逻辑读、物理读时CBC Latch的阻塞情况。测试非常简单,因为CBC Latch锁只有16个,只要找17个页,这17个页中,必然有两个页共用一个CBC Latch。

我使用下面的存储过程,向一个表中插入了50行。每一行都占6000字以上,50行数据,使用的页数量绝对在17个以上,一定有两个页用同一个CBC Latch保护。

运维日记|MySQL/Oracle深度解析之一:逻辑读

可以得到T1表所有页的HASH值,HASH值相同的,就是使用同一个CBC Latch的页。在我的测试环境,显示的结果id1为1行所在页,和id1为34的行所在页,共用一个CBC Latch,测试过程如下图:

运维日记|MySQL/Oracle深度解析之一:逻辑读

简单说一下这个测试,先开始一个逻辑读(查询多次id1=1的页),使用gdb,当线程获得id1=1页上的CBC Latch后停住(也就是在执行完pfs_rw_lock_s_lock_func()函数后停住)。

接下来再开启一个连接,查询id1=34的行。Id1=34的行所在页之前没被读过,它是一次物理读,它会被前面的逻辑读阻塞。然后我们可以慢慢观察MySQL出现CBC Latch阻塞后的情况。首先查看show processlist中的状态:

运维日记|MySQL/Oracle深度解析之一:逻辑读

红框中的Session,正在执行id1=34的查询,它已经被阻塞了。但你无法得知它被阻塞,它的状态还是Query和Statistics。Oracle中可不是这样,你可以在v$session中看到Session的准确状态,是WAITING、WAITED SHORT TIME和ACTIVE、INACTIVE。

MySQL中除了show processlist,还可以在show engine innodb status中看到更多信息:

运维日记|MySQL/Oracle深度解析之一:逻辑读

在“show engine innodb status”中可以看到CBC Latch出现阻塞。上图红框中的信息说明有一个线程已经被阻塞了,它在等待X-LOCK锁。


但是show engine innodb status的结果不是标准的二维表,而且这里只显示有阻塞,阻塞的次数、时间都不知道。而且CBC Latch的持有时间都是很快的,多了一小会儿竞争时间,你也感觉不到。可能为让一次毫秒级耗时的逻辑读,增加几十、或几百微秒。

Oracle中可以SQL统计计算CBC Latch被调用的总次数、阻塞次数、阻塞时间、……等等信息。通过这些信息,美创运维中心中有经验的DBA一眼就可以看出来,CBC Latch是否竞争过于激烈。

我们还回到CBC Latch的数量问题上来。我们可以得到一点结论了,在MySQL中,因为只有16个CBC Latch锁,平均17个页会有两个页共用一个CBC Latch锁,也就是说平均17次物理读,会有一次CBC Latch竞争。

我们展开说一下,MySQL其实不是SSD友好型数据库。SSD大大提高了物理读的速度,但由于CBC Latch数量过少,在出现物理读时,较容易引发阻塞。

而且你很难察觉这些阻塞。它只是多占了点CPU、让你本来可以更快的SQL,慢了一点。至于慢了多少就很难说了,我粗略的说一下,物理读稍多的情况下有大概5%到10%的性能损耗。有没有可能解决这种问题呢?

运维日记|MySQL/Oracle深度解析之一:逻辑读

当然有,就是增加CBC Latch的数量。当你使用了SSD、特别是高性能的pci-e槽的SSD时,一定要记得增加CBC Latch的数量。

MySQL使用参数“innodb_page_hash_locks”控制CBC Latch的数量,该参数的缺省值是16。修改它也是很简单的事。但是,等等,为什么从来没听人说过要修改这个参数呢?网上搜索了一下,也鲜有针对这个参数的介绍:

运维日记|MySQL/Oracle深度解析之一:逻辑读

你可以换种搜索引擎,反正我用某度搜索“innodb_page_hash_locks”,啥都没搜到。并不是它不重要,刚才我们说了,是它造成的竞争很难察觉,大家不知道而已。


那么这个参数具体应该设为什么值呢?这要结合你的系统、主机CPU/内存等来确定。也可以参照Oracle中CBCLatch的数量,这可是经过了无数大厂验证过的值。或者,我建议你找美创科技的专业运维团队,为你的系统进行健康检查后,再决定是否需要修改、如何修改此参数的值。

如果你的Oracle数据库,遇到了CBC Latch或其他情况的竞争,当然也可以找我们,记住一句口号:

“有情况,找美创”

接下来我们再来看看访问Buffer页时的情况。

第三节

MySQL与Oracle的Buffer Pin Lock比较

搜索CBC链、找到目标Buffer之后,就要对页进行访问了。访问页也需要在锁的保护下进行,这个锁在Oracle中,称为Buffer Pin Lock,有兴趣了解Oracle的Buffer Pin Lock的话,可以看我的那本《Oracle内核技术揭密》第三章3.1.2小节。MySQL中的“Buffer Pin Lock”,限于篇幅,原理咱们就不展开介绍了。我们只说Buffer Pin Lock的相容、阻塞规则。

MySQL的Buffer Pin Lock规则很简单:

① 读与读不阻塞。

② 读与写互相阻塞。

③ 写与写互相阻塞。


读就是共享锁,写就是独占锁。Select就是读,DML就是写。上面的规则,其实就是,当两个线程同时操作同一页时:Select与DML互相阻塞、DML互相阻塞、Select互不阻塞。这种规则很容易理解。所有的数据库也都是这样,但是,Oracle不一样。

Oracle的规则是:

① 读与读不阻塞。

② 读不阻塞写。

③ 写阻塞读。

④ 写与写互相阻塞。

看出来没,Oracle与MySQL的区别:“读不阻塞写”

Oracle比MySQL向前走了一步,实现了读不阻塞写的Buffer Pin Lock。在读多写少的环境,读不阻塞写,可以有效的减少竞争。不像CBC Latch数量的问题,有一个“innodb_page_hash_locks”参数可以简单的弥补MySQL在此点上的不足。Buffer Pin Lock的相容、阻塞规则,是无法被改变的。只能期待后续MySQL代码的进步。

第四节

MySQL的棋高一着:共享的CBC Latch

当然,也不是所有地方Oracle都比MySQL棋高一着。有一个地方,MySQL还是有优势的。MySQL的CBC Latch只保护搜索HASH表:

运维日记|MySQL/Oracle深度解析之一:逻辑读

如上图红色虚线框中的画,假设Bucker 2后的Node2是目标,在找到Node2后,MySQL将马上释放CBC Latch。Oracle不一样,如下图:

运维日记|MySQL/Oracle深度解析之一:逻辑读

Oracle中在找到目标的Node 2后,并不马上释放CBC Latch,还要再修改Buffer Pin Lock的锁变量、设置Buffer Pin Lock锁,然后才能释放CBC Latch。(注:Oracle的Buffer Pin Lock锁就在图中的Node 2的内存中)

有看官说,“不就是多了个加个锁的操作吗,速度是很快的,Oracle的CBC Latch持有时间也不会比MySQL的长多少啊!”

话不能这么说,加锁虽然是耗时很短的操作,特别是Buffer Pin Lock这种“低级内存级锁”,也就是修改个内存标志位的事。但是,正是因为“修改”二字,导致Oracle的CBC Latch变成独占的了。所以,在Oracle 10G之前,CBC Latch没有模式,只有持有、不持有。因此只要持有CBC Latch,就是独占的,没有共享CBC Latch这一说。

但Oracle当然也非浪得虚名,在11G后,对CBC Latch、Buffer Pin Lock机制做了很大的调整。最大的改变发生在11.2.0.4后,在这个版本中,共享模式的CBC Latch得到了更为普及的使用。但总体上来说,独占模式的CBC Latch,还是比MySQL中要多。在这一点上,MySQL算是扳回一局。并不是所有地方,Oracle都比MySQL先进。Oracle的最低层内核,其实还有提升空间。

好了,为了照顾看热闹的群众,我并没有对MySQL、Oracle的部分原理做太过细致的剖析,省略了调试过程的细节。对调试技术、数据库源码有兴趣的朋友,请持续观注美创新运维新数据公众号,精彩持续中。


服务热线:400-711-8011
Copyright ©2005-2018 杭州美创科技有限公司. All Rights Reserved. 浙ICP备12021012号-1