MySQL数据库上云四年打磨,五大经典案例让你不再“蓝瘦”

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL数据库上云四年打磨,五大经典案例让你不再“蓝瘦”

云学习小组 2016-11-15 17:10:47 浏览4586
展开阅读全文

通过以往的经验分析得出,数据库上云问题可能有以下几种情况:

1.         数据库跨平台迁移(PG->MySQL、Oracle->MySQL),淘宝以前就有大量的Oracle迁到MySQL,也是发生过很多问题。

2.         跨版本升级(MySQL:5.1->5.5、5.5->5.6),导致了性能问题。

3.         数据库的执行计划、优化器、参数配置和硬件配置。

4.         云上较明显就是网络延迟(跨可用区域访问、公网延迟、网卡饱满)。

 

应用场景一:一个参数引发的血案

某个客户正在将本地系统迁移上云,在RDS上运行时间明显要比线下自建数据库运行时间要慢1倍,导致客户系统割接延期的风险。

根据经验的沉淀,我们可以分析确认数据库从云上迁移到云下,MySQL没有更改,所以不是跨平台迁移和跨版本升级的原因。

优化器版本

47fc495d097754cf8f0c464a42c38cdba523ee54

接下来,对比优化器版本,可以看到用户的数据库版本也没有问题,故而排除优化器问题。

SQL执行计划

5ad594cfb20a39ecb5cbba24b593c34de9173590

再看SQL执行计划,对比线下和云上的SQL执行计划,通过观察rows可以得出,没有太大变化,排查到这,似乎已经到了山穷水尽的地步。

参数配置

5ce688364a19caf415a3cf7a7ef98cc637bf8b88

接下来检查参数配置,发现用户手工更改了重要的三个参数。

测试验证

f01081f5117df22dbc0fd3f51f860a42a881d6cf

将三个参数一一对比调试,经过测试验证,是tmp_table_size的问题,云上默认是256K,本地是128M,云上实际上是一种朴实型的运行环境,参数值如果调的很大,其他用户的内存消耗可能就会变大,将tmp_table_size调到128M后,性能有所提升,从18秒降低到7秒,基本与本地持平。

总结排查思路

如果线下环境的SQL低于云上SQL。第一,检查执行计划;第二,检查数据库版本和优化器;第三,对比参数和硬件配置;第四,查看网络延迟。

 

应用场景二:上云版本升级带来性能下降

某手机客户端上云,第一次系统切割失败,数据库CPU 100%,需要在第二次割接前排除原因。

问题排除——跨版本升级

933cba053c94eec5fa401989c56c24f8b4fcab93

数据库CPU100%是比较容易排查的, 可以查询数据库中的SQL为什么慢,发现用户本地的MySQL版本是5.5,云上RDS版本是5.6,用户的一条SQL在本地5.5执行只需要零点几秒,而在RDS上需要十多秒,导致所有的线程都堆积起来了。

bf736aa5d0992eec40019adc829a6b1b98d9dcb3

数据库版本发生了变化,最核心的一点就是优化器发生了变化,看图中执行计划中的rows,访问第一个表需要25万行,并且对25万行进行排序,工作量巨大,这就是问题所在。

5b5ed33e750cb4033e7f948cfbcb108627e0bb8d

为了更加确定问题,对比优化器在本地正常的情况下的SQL执行计划,它的rows是非常小的,所以可以推断是block_nested_loop优化器的优化,导致SQL执行计划的转变,进而导致SQL性能下降。

字符串存储时间导致隐士转换

50a04f5dd81ad24891c621a6c69179b2f2a50eb8

这是开发习惯导致的问题,gmt_create用字符串存时间, 5.5版本加个索引,它能够利用索引,SQL是没有问题的。

c3753c56c19c9904b7c213898ec76681a0e05d6f

5.6版本之后,全表扫描280万数据,所以这条SQL肯定慢,这就是导致CPU100%的根源。

总结排查思路

分析SQL执行计划,对比数据库版本和优化器规则。

最佳实践经验:保持数据库版本一致,功能和性能测试缺一不可。

 

应用场景三:数据库上云后性能下降紧急救援

某APP应用上云后数据库CPU100%,系统回滚会出现数据丢失;弹性升级需要时间较长,要在白天业务高峰来临之际消除障碍。

问题排除

对比发现规格配置较小,用户本地物理机的配置是云上RDS的规格两倍,导致慢SQL出现堆积。具体如下:

1.         本地物理机配置:2U机箱,2*Intel E5-2609 v2 4核,内存:64G;磁盘ssd,Raid5;

2.         RDS配置:逻辑CPU8核,内存32G,最大IOPS:12000。

优化SQL

b1896bedaabc96b07d69b7da9d1f26e17ac4cdd5

SQL的执行计划性能较低,走了两个索引的intersect,需要计算大量的数据rows:30137696。第一种解决办法是控制优化器的策略;第二种办法让表走index_finish Time’(‘finish Time)。

6237daf733b5c9018cd991cc9103be66595ad1da

采取第二种办法将idx_delStatus索引删除,索引删除后执行计划恢复正常,性能急速提升。rows只需要40行。

总结排查思路

当性能变慢了,要对比数据库资源配置,分析SQL执行计划。

最佳实践经验:保持数据库资源配置一致,功能和性能测试缺一不可。

 

应用场景四:去“O”上云的护航故事

某大型系统从Oracle迁移到RDS MySQL,迁移到RDS后出现CPU100%,需要紧急解决。

改写子查询

71d7c3a81231bb40aa4881c9d91f11f197ce3d39

在淘宝去“O”过程中也遇到过类似的问题,排查过程中发现是MySQL的子查询诟病,Oracle开发人员会经常使用这样的SQL做子查询,这个SQL可能就是查薪水为5000块人的名字,正常的思维逻辑为先查子查询的结果集,再反带到外面的表去关联,子查询中的结果集是非常小的,循环的次数较少,对性能不会有太大影响。但是在MySQL中就不一样了,MySQL只有一个嵌套循环,MySQL的处理逻辑是遍历employees表中的每一条记录,代入到子查询中去,如果employees表太大,就会导致循环的次数太多,使SQL性能受影响。

最佳经验实践:子查询在5.1、5.5版本中都存在较大风险,将子查询改为关联,使用MySQL5.6的版本可以避免子查询的问题。

 

应用场景五:网络延迟造成的性能下降

某电商系统迁移上云测试过程中发现性能较低,应用代码、数据库配置完全一样。

网络延迟放大

f3c830f8aaf3ddfdf89c0b6f422cc31f8e619adb

通过将SQL日志一行行看,应用日志一行行的对照,发现原来架构应用和DB实在一台服务器上,应用和DB没有网络交互,更致命的是,原来的系统架构一个订单要访问数据库200多次,到云上的效应就扩大了,所以性能自然下降了,只能更改代码,优化系统调用。

最佳实践经验:需要考虑上云后网络延迟对性能的影响,优化应用与数据库的访问;应用和数据库尽量保持在同一个可用区内访问。

 

全部总结来看,系统配置要保持一致,包括版本、参数、规格等;还有考虑网络延迟(带宽、跨机房等)。

 

本文根据阿里云技术专家玄惭于10月14日在2016年杭州云栖大会上的题为“数据库上云经典案例分析”的演讲整理而成。

网友评论

登录后评论
0/500
评论
云学习小组
+ 关注