一次資料庫上雲遷移效能下降的排查

背景介紹：

某客戶目前正在將本地的業務系統遷移上雲，測試過程中發現後臺運營系統，在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. 硬體問題是此次問題沒有想到的一個點，所以當效能出現問題後，不僅要對比軟體配置，同時也需要關注硬體上的配置是否一致。