微信号:tingyun718

介绍:帮助您更好的了解听云提供的SaaS平台和更多服务

Oracle和MySQL性能优化感悟

2017-04-25 18:11 听云

来自搜狐畅游的杨建荣在QCon“听云新一代APM技术专场”发表了题为《Oracle和MySQL性能优化感悟》的演讲,现场分享了Oracle和MySQL的性能优化思路,将会着重通过对比来解析两者性能优化的一些感悟总结,并对一些相关技术细节进行现场解析。


以下为演讲实录:


大家好,我是杨建荣,非常荣幸能够参加这次分享。我今天演讲的题目是《Oracle和MySQL的性能优化的一些基本的总结和感悟》。我主要做Oracle方面的一些工作,也做MySQL相关的一些工作。个人是从去年写了一本书,就是《Oracle DBA工作笔记》。现在每天会写一点博客,连续坚持了1100多天,下面直接进入正题。


今天分享的思路大体分为下图五个部分:

  • Oracle和MySQL发展情况

  • Oracle和MySQL技术对比

  • 监控、压测工具的方法和数据

  • SQL性能优化案例

  • 职业发展展望


一、Oracle和MySQL发展情况

首先说一下Oracle和MySQL的发展情况。Oracle和MySQL的发展会从以下五个方面来进行简单的分享:

  • Oracle和MySQL的技术现状

  • 近几年提的比较普遍的去O的概念

  • 性能优化的反向金字塔

  • 对于DBA的角色及分工

  • 运维二进制的概念


1、Oracle和MySQL的现状

如上图右边的排行,截止到目前抓取到这个数据是2017年4月份更新的数据。这是根据搜索的热度来进行的一个DB数据库的排行榜,仅供参考,排前五的是这几个数据库。今天分享的主题就是Oracle和MySQL。对于Oracle来说,最新的版本是12.2,这个版本等了很多年才出来,因为这个版本是稳定版本。而对于MySQL的研发计划来说,MySQL从去年年底推出最新的版本是5.7.17,接下来发布的版本会直接跨到8.0。


对于Oracle来说,它现在的版本是12C,它的核心概念会在以下几个方面进行体现:

  • 在12C的R1里提到两个比较大的概念是PDB和IMO

  • 在12CR2,提到一个新的概念sharding。

总结下,PDB就是可插拔数据库,IMO是列式存储,是SGA中的一个新的静态池,它和传统有一些差别,这两个方面是Oracle当前版本最主要的两个亮点。


对于MySQL来说,去年年底推出的一个5.7版本,最亮点的特性是MGR,它是replication的一个扩展概念。通过这个技术,高可用性方面做了一个更大的改进。,后面会详细解释。


2、关于去O

从08年开始,阿里提出了一个关于去O的技术革命,当然这个过程从08年提出来到现在也快10年了,效果大家也看到了,现在用O的还是在用,去O的也还是去。这里引用浙江移动王晓征老师的分享,针对于去O是有四大误区的。

Oracle本身是一个成熟的数据库,而MySQL现在也隶属于Oracle公司。就是一个是商业数据库和开源数据库的不同,他们间的对比就相当于左右手互博,很难分高下。


从开发人员或者是像运维人员的角度来说,Oracle一直以来是一个非常专业的数据库,它的运维工作需要非常专业的DBA来做。MySQL在过去则经常是由一些运维同学附带去做的;而现在MySQL有一个非常大的空间,现在MySQL整体的需求很高,为什么是这样的,因为就MySQL本身来说,它的技术功能比Oracle相对要简单一些,但是它对人的要求会多一些。其实大家去做一个技术矩阵简单的看一下,可以发现MySQL有非常多的工具都是第三方的。


3、性能优化的反向金字塔

这个就是引用的这么一个性能调优。性能调优分了几个层面,简单的概括为反向金字塔,一般金字塔最顶端是最高级的,但是在这里是最低级的。


早期的性能优化,说的通俗一点就是拼硬件,就是不断的对硬件进行优化以保证性能不会出问题的状态。当然由于现在云端技术和其他基础设施发展的比较成熟,这方面已经不再是大问题了。下面这一部分问题就是一些基本的数据库内的参数这样一些实例级别的优化,比如举一个早期的例子,大概在2000年初的时候,当时如果会搭建、安装一个Oracle数据库,就会显示工程师的技术比较好;再或者有一个工程师去做Oracle的优化,因为当时技术相对来说技术相对比较低一些,在优化的过程中发现优化的内存有8G,相当于占用了最大缓存区的几百兆,之后只要把八个G的服务器缓存提大,原来的性能问题都没有了。就是对于参数类等一些非常初始类的优化的空间会越来越小。


金字塔下方就是对一些表、索引和cache做的一些优化。DBA之前分为1.0和2.0,早期DBA和索引做斗争,后面一批和统计信息做斗争,起点会越来越高,对表和索引的优化层次也会越来越高。


再下方就是对应用来说的优化,只要有业务的需求,就肯定产生相应的基本的业务的东西,肯定会和数据库打交道。这一块相对来说普遍性的就是SQL优化。


4、DBA的角色分工

上图是根据当前DBA的职位所总结出来DBA的角色和分工。对于很多的公司包括国内的公司来说,把DBA简单的抽象为一个运维角色,当然对一些大的公司来说是有非常详细的角色和分工。对于DBA来说大体分为两部分,一部分是核心层,另外一层是定制层。核心层就相当于一款产品,以JAVA为例,JAVA的核心层是做基本的管理,然后定制层就是去基于Java做一些相应的应用等等,并且针对于该应用层面定一个层,从而去做这样一些基本的规划。


对于原来的Physical DBA,不只是安装部署、存储网络等这些都要非常的精通,对数据库打的补丁、操作系统这些也要非常熟悉;Performance DBA是主要做性能优化的DBA,在大公司特指一个高级的、可以从其他的DBA角色可以升级为的性能DBA。Performance DBA需要在技术上、业务上都要非常熟悉公司产品;开发DBA,主要负责一些数据库开发等一些研发类工作;驻场DBA,驻扎在客户现场,什么事都做;Delivery DBA,针对大公司去维护大的数据的变更补丁等业务进行持续的交付;Application DBA,主要负责公司业务,需要对业务非常熟悉;


所以就像上图所表示的一样,定制层的DBA偏右一点。图里面颜色加深的部分,对现在DBA角色和定位来说,社会对于这些加深颜色的DBA的需求更大一些。


5、运维二进制的概念

运维二进制是一个很有深意的概念,就是无论什么东西做的再好,只要丢一分,那就是零分。比如说做了一个数据库的变更,前期的测试都做的非常充分,可能有一点遗漏,结果导致这个数据库起不来,这个就是一个零分。这里对于数据库的故障进行一个简单的说明,通过总结早些年的数据库故障得到如下这张图。

在早期的数据库故障中,硬件故障占了80%。其他的一些诸如网络、系统等故障占的比例相对较少,总体上就构成了比较典型的二八比例。在80%的比例中主板故障又占了一个很高的比例,接下来依次是内存故障、主板故障,内存故障等,这些硬件问题很难检测到,一旦出现问题后,又会造成比较大的困扰。


二、Oracle和MySQL的技术对比

第二部分是Oracle和MySQL的技术对比,技术对比会从以下五个部分进行相关讲解。

  • Oracle和MySQL的技术矩阵

  • 数据访问的模式对比

  • 性能优化的基石

  • 性能优化和系统演进策略

  • 数据库参数的版本变化


1、Oracle和MySQL的技术矩阵

下图是经总结过得的Oracle和MySQL的技术矩阵。

这里简单提一下,首先Oracle和MySQL是同一家公司,都属于同一个公司的下的产品,同属于关系型数据库;


Oracle基本的架构思想是集成共享,而MySQL的架构类型是分布式和虚拟化。大家都知道现在有一些企业会利用docker来做MySQL的虚拟化,Oracle却不可以。因为相对来说MySQL更轻量一些。


Oracle属于商业+闭源类型,而MySQL属于开源数据库的版本免费+商业版。

分支方面,Oracle属于集成式的,没有分支;MySQL则有非常多的分支,有Percona, MariaDB,还有Drizzle。它们之间是不兼容的,从MySQL分支开始,它们间的差异越来越大。


2、数据访问模式对比

上图是Oracle和MySQL数据访问模式的对比图,通过这张图能看出Oracle和MySQL的访问模式差别。左边是MySQL访问数据库,MySQL的用户是一个用户名+主机名,可以理解客户端名,通过共同组成这个用户这个方式来定义用户;比如user1这个用户,可以访问2个数据库,但是该用户下是没有任何数据的。User2只能访问test2,那么就把权限给出去。

而Oracle的差异比较大,如上图右边,这里的owner下是有数据的。可以理解为Oracle的用户和MySQL的database是差不多的。当然Oracle里也可以做成多个用户访问同一份的数据,通过指定几个链接用户,然后通过权限的方式,通过角色分发的方式做一个基本的访问库。


3、性能优化的基石

性能优化的基石是灾难备份和高可用性。首先保证的灾备不丢数据,保证业务的持续运行。简单对比一下,MySQL中的灾备高可用方案是Master slave,Oracle就是Data Guard主备库和RAC就是集群的方式。总体来说,Oracle的技术相对成熟一点。但是像大型金融行业,RAC是标配,是必须要有的。但是如MHA PXC MGR这样一些技术方案,简单来说都是第三方的。


当然谈论性能优化,不能只从简单的数据库的层面或者系统层面去谈论。而是要从整体的应用层面的去看待性能优化。


4、性能优化和系统演进策略

上图就是性能优化和系统的一个大体上的演进策略,这种属于系统架构层面的东西对于性能优化是一个非常重要的参考。以上图为例,横轴是业务价值,产品首先要有业务价值。纵轴是技术价值,企业要有技术水平。对于左下角哪些既没有什么业务价值,技术水平又比较差一些的,基本上都是淘汰。其次对于高水平高价值这样一些系统我们就不断的去做一些集成。一些如技术水平低但是业务价值高的这样一些系统,我们就进行改造。

简单的给大家举一个例子,用数据恢复来做一个拓展。通常情况下会对数据恢复来做这样一些基本的查询。当然这个查询有的时候在节假日去做,然后我们去查询一些基本的数据。当数据比较多时,DBA会加一个row number函数来看这个输出的数量,将基本的格式调一下,最后会输出来九行数据。但是如果实际操作中,运行SQL语句误把row number删了,导致原来查出的几万条数据,最后只能查出几条。如果时间已经过去三四天,怎么才能把这个工作很严谨的做出来,这时就涉及到Oracle的闪回方案。


Oracle如果做数据的变更,或者想把数据库某一个表的数据恢复到较短的时间内的情况,比如说查5分钟前,就可以用闪回查询。再比如说表的数据,也可以用闪回查询等等。另外如果是个库,并且这个库优先级别比较高的时候是不能这么闪回的。但是这个时间隔的太长了,往往隔了三四天后,查不出来了。这时就需要额外开一个闪回数据库,设置备份为一个星期,这样就可以闪回到三四天前某一分钟的情况。


MySQL无法做闪回数据库、闪回表格数据、闪回DML或DML的操作,但是MySQL可以做短期内的闪回,因为MySQL将数据变更写到binblog里,通过手工做一些转换,把binblog里的数据抓出来。


当然闪回所涉及的面比较多。比如做了一个insert update delete这样一些操作你去闪回可能性要大,如果先做了一个drop命令,那么闪回DDL对于MySQL来说难度就很大,因为日志很少。做的闪回数据库不光是闪回DDL,任何数据库的操作只要不是数据文件的操作就可以了,其他的数据的变更操作完全不会有问题。


5、数据库参数的版本变化

这样总结一下Oracle和MySQL数据参数的版本变化,方便大家对数据库的基本的情况有一个整体的了解。这里取一个样本,这个样本分别取的Oracle10,11和12这三个版本,因为12C的两个版本1和2隔的时间太长了,所以各取一份,大概参数比例是这样的一个情况。


这里简单解释一下什么是所有参数,所有参数就有很多隐含的、不公开的参数就是所有参数。像上图左侧就是Oracle的所有参数和公开参数。通过这个图可以看到开放的参数占所有参数的比例非常低。


再看一下MySQL版本的变化,样本分别是5.5,5.6,5.7这三个版本。结合上图将MySQL和Oracle进行比较,会发现MySQL在开放的参数上和Oracle是处于一个量级。但是上文说了Oracle有一些隐含参数,这样总体的参数量就很大,因此Oracle上开放参数占总参数10%左右,也就是开放出来10%的参数。

通过上面的饼图可以看的更清楚一些。这个图是索引参数,即隐含参数加开放参数。据图中可以看出Oracle12版本的参数量比10和11这两个版本加起来还要多,约占52%,大概有4800多个。在开放参数比例图中,基本还上算平衡。那么为什么说Oracle12C的变化非常大?因为这主要体现在PDB、IMO上,R1和R2这两个版本相隔的时间又比较长。并且通过隐含参数的比例可以看出,开放参数400多个,隐含参数4800多个,因此Oracle里面隐含很大的空间来扩展功能;


在MySQL中,你会看到四个版本的参数比例变化,5.6和5.7这两个版本占的比例相对要高一些。当然MySQL它本身就是开源的,没有隐含参数的概念,因此MySQL的参数变化相对稳定一些。当然在下一个版本8.0中参数会进行大幅度变化。


三、监控、压测工具

这一部分主要是指监控、压测以及它们的基本方法和数据,简单把这部分分为以下三个方面:

  • 数据库的监控和优化定制

  • 性能测试工具

  • 压测场景总结


1、数据库的监控和优化定制

上图就是对数据库是所做的一个基本的监控架构图。简单来说,我们是有一个监控的服务端就是Zabbix Server,它去监控每一台服务器就是一个Agent,每个服务器部署一个Zabbix Agent,这样一个方式。当然Zabbix支持系统,支持MySQL好一些,所以它监控的系统,OS这个MySQL上面部署Agent就可以了。当然对Oracle来说,Zabbix目前支持不了怎么办呢?所以第三方把Zabbix改成Orabbix,它也是开源的。其实你可以GDBC的方式,我们把Orabbix的方式,你可以当成一个客户端。当然这个客户端它可以对应多个数据库,这个是Zabbix监控MySQL和监控Oracle间比较大的一个差别。有人说Oracle利用EM12C这样一些工具监控,那是商业,这个我们说的是开源的方案。相对来说更轻量一些。

Zabbix+Orabbix监控系统

上图就是使用Zabbix和Orabbix结合之后所做的一个监控系统,这类监控系统的监控力度非常细致,可以监控到分钟级别、秒级别甚至是某一个数据库的负载在几分钟之内或者多少秒之内的一个微小的抖动。其实很多时候我们数据库的性能,如果只是单纯的看数据库的时间和快照,会发现一般的微小抖动都会被平均掉,所以通过平均值是看不出来数据库的变化的,但是通过上面这种方式就能够得到一个比较精确的情况。


2、性能测试工具

我们简单的说一下性能测试工具。性能测试工具这一块,我简单的提一下,我举了两个样板,一个是Sysbench,一个是swingbench。


Sysbench支持Oracle、MySQL和PostgreSQL,但是在新版本测试中,对于Oracle的支持并不理想。swingbench只支持Oracle,不支持MySQL。具体区别见上图。

MySQL选择合适的redo大小


MySQL中redo的大小怎么测?当然在不同的场景有不同的方案,这里以一个在特定的场景中大概一千多个线程为例,最后测试结果显示TPS的大小是在3500比较合适。这里redo的大小是一个G,据图中可以看出一个G的重量相对比较平稳,而其他的时候则会有比较大的抖动。因此在此场景中,在一千个线程的环境中,一个G的redo对于MySQL比较平稳。

MySQL主从延迟测试

另外一个很重要的概念是主从延迟。主从延迟这个概念其实在MySQL中已经是老生常谈了。上图中做了一个5.6、5.7两个版本的对比测试。在5.7多线程中,延迟比较低,而5.6版本则相对高一些。

延迟回落

这里有一个延迟回落的概念,比如在某个瞬间,这个线程已经停了,这个延迟什么时候能补齐。在上图可以看到在5.7版本里这是一个并行复制,即多线程复制,延迟回落的时间会早一些,造成的延迟率也会低一些。而5.6版本单线程和多线程没有太大的变化,因为这是对于数据库级别的复制,并不能精确到表齐,因此对单一数据库做压侧的时候变化不会太大。

Oracle压缩归档

上图是针对Oracle所做的压测,通过传输维度去做数据库存储维度。Oracle本身有改进压测,通过把redo压缩之后可以很大的降低网络带宽的负载。通过上图对比,可以看到CPU使用率在未开起压缩和开启压缩之后的基本上没有太大的变化,因此开启压缩这个功能对系统层面来说影响很小;上图右侧是压缩前后的带宽流量,可以看到压缩后的带宽流量是压缩前的一倍。所以当一些数据库的redo带宽占用比较高时,可以尝试启用压缩维度。


四、SQL性能优化案例

1、MySQL优化案例

这是个MySQL优化案例,大概执行的是六万多秒,20多个小时。为什么有这么一个问题呢?它有这么一个业务查询需要查某一个表的数据,这个DBA执行之后,生成到某一个文件里头,执行之后,隔一天想起来,这个SQL还没有执行完。大概这么一个情况,SQL大概是六万多秒,这个表格数据是两百万,下面的表格数据是两千多万。为什么执行二十多个小时还执行不完?这个表上个account地方是有索引的,这个表里头有一个ID,ID有索引,这个Login time和login account是没有索引的。大概是这样一个情况。

我们简单的测试一下,发现情况和我们预期差距比较大。单查这个表,按这个条件查这个表,这个数据一秒内返回,子查询执行时间一分钟,最后怎么执行20个小时还没有执行完呢?我们生成一个执行计划,explain extended这个能够生成比较详细的执行计划。我们来看一下SQL语句在MySQL优化器里头相当于做了一个转换,转换之后是下面这样一个图。

针对这个问题我们做了一些小的改进,比如说针对上面语句的查询花了一秒钟和下面的语句查询花了一分钟,通过创建一个临时表来看一下性能优化,但是实际上我们创建临时表之后,性能还是没有什么改进,那这个问题最后怎么解决呢?

MySQL本质上在执行的时候会先执行上面的语句,比如两百万数据中筛选出十万数据,这十万个ID都要和下面的表做查询关联,因为没有索引,所以每次都做全表扫描,这表是两千多万的数据,因为MySQL支持反连接所以性能会差一些。最后怎么改进呢?方法就是给这个表创建一个索引login_account,这样最后运行时间就可以节省到2秒多。


2、Oracle资源取舍

Oracle的优化其实有非常多的技巧,以下图为例,会看到数据图基本的负载很高,因为有这样一段时间有update操作,造成负载高一些。

就是因为一个update操作造成这个数据库的负载比较高,并且执行时所涉及的这个表数据量都是亿级,所以执行时间大概是四个小时。在执行这样一个优化的时候,来看一下所执行的路径。

这里会引用另外一个数据库的表格数据,那个表里面的数据抓过来以后,会对现有数据库的数据做一个处理并在现有数据库上做操作。这个过程中涉及到一个概念,就是资源的取舍,原来的操作是把所有的数据全部抓过来,每次抓过来的数据上千万,最后做一个关联和查询,然后update。


那么怎么优化呢,原库表的每一个数据都有时间的标识,可以做增量,即可以通过这些增量来做一个变更,意思就是最终加上临时表,可以在临时表上取一个增量。而且这个过程中不用创建任何的索引,就是在了解这个业务情况之后做一个资源的平衡。做完之后这个执行过程执行一分钟就完了,所以就可以看到下图右边基本上就没有了。

五、职业发展展望


最后做一个职业发展的感悟,就和下图说的一样:不管是做数据库性能优化维护开发等等,这句话给大家做一个参考,做有价值的事情,复杂的事情简单做,简单的事情重复做,重复的事情用心做。

 

Q&A:


提问:问一下刚才说分区的问题,咱们这边的分区变化的时候,分区数据的迁移,分区路由使用什么样的方案?第二个DBA分不同的角色,那个优化案例有的时候可能是开发人员对DBA不太了解,您认为DBA在哪个阶段进入,还是不应该介入,咱们搜狐这边怎么做的?

杨建荣:因为我之前在跨国公司做,所以提的DBA角色的分工的概念。当然国内角色分工不是太明确,一些基本的用人管理优化工作都是DBA做。当然你说的整个的优化的流程怎么去介入呢?其实从需求阶段、从表设计介入效果是最好的;分区方面,Oracle在12C中也做了一个尝试,这个尝试的基本就是根据路由,最后做了GDS的服务。其实原本Oracle这种方法是可以做水平切割的。比如说一个表做切分,可以把表设为很多的分区表,或者把同样的表分布在不同的数据库上。当然12C里面的分区其实跟我们所理解的简单的分布式分区更像,通过GDS路由来实现,当然对应用来说是一个透明的。


提问:老师你好,我想问一下您刚才说的闪回数据库是怎样实现的?

杨建荣:闪回数据库就是一个flashback,是Oracle内制的,内置闪回支持七八个特性,我只是简单的举了几个。


提问:Oracle隐含参数,用户可以很安全的感知到这些参数吗?

杨建荣:隐含参数这块量比较大,绝大多数情况下是不建议去修改的,因为有一些隐含参数是有关联的。可能改了一个参数之后没有效果,需要几个联合起来才有效果。甚至有的隐含参数还没有开放出来,有些隐含参数是测试过程可能有一些问题,Oracle开放出来参数是有一个默认的最优值的。


提问:您好,首先我不是DBA,我想问一个问题,您刚才提到了数据库版本更迭的问题,我们应该很快的调研新的版本,立刻让它更快的进入到业务线,还是根据业务为主,现版本的MySQL已经满足我们的需求,我们有必要冒进升级版本吗?

杨建荣:升级方面还是要以稳定为主。不建议做一些相对来说激进一些的动作,包括MySQL有一些新的增量补丁基本上都是以稳定为主,要更新的版本必须是测试环境运行过很长一段时间,而且压测的时候没有大的问题才可以。毕竟新的版本可能大体用的时候没有问题,但是有的时候压测会出现比较大的隐患,比如宕机等等。


提问:搜狐畅游有多款产品,在公司内部什么样的场景下用Oracle,什么时候用MySQL。出于性能考虑,手游或者大型端游有没有偏向?

杨建荣:很多的游戏公司其实都用大家所了解的GS(游戏服务器)来做的,不同的游戏服务在做的时候,如果数据的存储量比较大,基本上都会用MySQL。当然你说的端游手游甚至页游,这些数据库都是属于平台化,平台化涉及到交易,只要涉及到和交易这样的一些事务性要求比较高的业务流程,一般都会用Oracle,包括BAT也是这样做的。



了解更多性能优化内容及听云产品请点击“阅读原文”申请试用

 
听云 更多文章 想听性能优化的发展趋势与最佳实践?QCon2017北京站等你来 QCon2017 | 光大银行系统应用优化实践 “互联网+保险”:新用户体验时代认知升级新思维 寺库网:直面业务,拥抱新技术架构优化转型 文末有彩蛋|2017年六大APM技术发展趋势
猜您喜欢 腾讯文学内容中心分布式文件系统的设计和实现 Java 语言的几个缺陷之: equals() 比较字符串 技术人员如何面试 - 过来人的经验谈 你的双十一“Go”了吗?强势围观井喷流量下的高可用实践吧 2016·iWeb峰会·上海站全部议程滚烫出炉