Home > database > 一次数据库上云迁移性能下降的排查

一次数据库上云迁移性能下降的排查

October 27th, 2015

背景介绍:

某客户目前正在将本地的业务系统迁移上云,测试过程中发现后台运营系统,在rds上运行时间明显要比线下PC上自建数据库运行时间要慢1倍,导致客户系统割接延期的风险。用户线下一台PC服务器的性能居然还比顶配的RDS跑的快,这让用户对RDS的性能产生了质疑,需要立刻调查原因。

问题分析:

通常SQL的执行时间在同等数据量的情况下发生变化主要有以下一些场景,其主要原因是由于优化器生成的执行计划发生了改变,这样则会导致SQL的执行时间发生较大的变化,当然可能变慢,也有可能变快,变慢是我们不想看到的场景:

1、  数据库跨平台迁移(PG->MySQL、ORALCE->MySQL)

多次遇到数据库从Oracle迁移到MySQL后,由于MySQL的优化器在低版本(5.6以下版本)对子查询的优化较差,导致系统迁移到MySQL后,系统中大量的子查询SQL堆积一直没有返回,导致数据库连接数跑满,数据库的CPU 100%。

2、  跨版本升级(MySQL:5.1->5.5、5.5->5.6)

也曾经遇到由于数据库在版本(5.5->5.6)升级后原先正常执行的SQL变得奇慢务必,导致整个升级迁移不得不回退,其主要原因高版本(5.6)的优化器策略与低版本(5.5)不同,导致了SQL执行计划发生变化,进而导致了sql的性能急剧下降;

问题排查:

1.确定优化器版本:

先确认用户本地的数据库版本和RDS的版本是否是一致的:用户本地的版本5.6.25,RDS的版本5.6.16,所以在大版本上没有太大的区别;由于在小版本上有一些差异,需要确认一下优化器中支持的优化类型是否一致,发现优化类型没有区别:

OPTIMIZER_SWITCH:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,

index_merge_intersection=on,engine_condition_pushdown=on,

index_condition_pushdown=on,mrr=on,mrr_cost_based=on,

block_nested_loop=on,batched_key_access=off,materialization=on,

semijoin=on,loosescan=on,firstmatch=on,

subquery_materialization_cost_based=on,use_index_extensions=on

2.确定SQL执行计划:

既然优化器的版本是一致的,所以接下来在确认以下SQL的执行计划是否一致,由于这些SQL都是后台运行统计分析使用,所以都非常的复杂,有可能某些表的统计信息不准确,则可能导致执行计划发生变化。对比用户和RDS两边的SQL执行计划,并没有发现执行计划发生了特别大的变化,一些小表的执行顺序发生了一些变化,不过没有太大的影响,因为执行计划的所涉及总rows没有太大的变化:rows=39900*1*1*140*285*1*1*1*1*1*1*1;

3.确定参数配置:

在优化器以及SQL执行计划上没有太多的进展后,我们又开始关注用户的数据库参数配置与RDS是否有差异,因为RDS的一些性能参数是保持官方默认的配置,是否在这里出了问题,所以将用户本地数据库的配置文件拉出来进行了对比,发现了重大线索,用户的参数文件中特意调大很多会话级别的内存参数,而在RDS这些参数都是默认的配置:

用户配置:

join_buffer_size = 128M

read_rnd_buffer_size = 128M

tmp_table_size = 128M

RDS配置

join_buffer_size = 1M

read_buffer_size = 1M

tmp_table_size =256K

可以看到用户调整的这些会话级别的内存参数,可以帮助每一个查询的中间计算结果尽可能的在内存中完成,避免查询的中间结果落盘导致性能的下降,但由于这些参数都是会话级别的参数,一个查询就会分配对应大小的内存,则会导致数据库的内存消耗非常快,可能会导致数据库出现OOM,当然如果是一些后台执行不是很频繁的查询,通过调整相关的参数,确实可以提升SQL的执行性能。在调整了上述参数后,尤其是tmp_table_size与用户配置一致后大部分的SQL性能与用户本地的持平,该参数用于决定内部内存临时表的最大值,每个线程都要分配(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值),如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,优化查询语句的时候,要避免使用临时表,如果实在避免不了的话,要保证这些临时表是存在内存中的。如果需要的话并且你有很多group by语句,并且你有很多内存,增大tmp_table_size(和max_heap_table_size)的值。

4.确定硬件配置:

在解决了大部分查询性能后,还发现还有一些SQL的执时间还是存在一些差异,所以在软件配置上没有太大的斩获后,我们的思路想到了是否是硬件配置出现了问题。由于数据库的内存配置都是比较大的,我们自然而然的想到了CPU的配置是否是一致,对比发现用户本地的PC服务器的CPU主频配置比RDS的CPU主频配置高出了20%,同时使用纯CPU计算的SQL在两边的环境进行压测,压测中也发现用户环境SQL的执行时间是RDS的一倍,所以解决办法就是升级主机的CPU主频配置,或者从业务或者数据库层面对SQL进行优化。

总结:

  1. 本次排查问题的过程中,在SQL执行计划上耗费了较多时间,可以对比执行计划中消耗的rows来判断执行计划是否存在较多的问题;
  2. 参数的不一致是导致此次问题的重要原因,由于有一些SQL不会受限于这些参数的影响,导致没有对这些参数进行深入的测试;
  3. 硬件问题是此次问题没有想到的一个点,所以当性能出现问题后,不仅要对比软件配置,同时也需要关注硬件上的配置是否一致。
Categories: database Tags:
Comments are closed.