《騰訊云布道師：一次性能峰值提升10W的DB調優之旅》要點：
本文介紹了騰訊云布道師：一次性能峰值提升10W的DB調優之旅，希望對您有用。如果有疑問，可以聯系我們。

作者：張青林,騰訊云布道師、MySQL架構師,隸屬騰訊TEG-基礎架構部-CDB內核開發團隊,專注于MySQL內核研發&相關架構工作,有著服務多個10W級QPS客戶的數據庫優化及穩定性維護經驗.

騰訊云數據庫團隊：繼承騰訊數據庫團隊十多年海量存儲的內部數據庫運營和運維經驗,推出一系列高性能關系型、分布式、文檔型和緩存類數據庫產品,并提供高可用性、自動化運維和易維護的云數據庫綜合解決方案.

前言

經過周末兩天的折騰,在大家的幫助下最終將用戶DB的性能峰值由最初的不到7W的QPS+TPS提升至17W,心情也由最初的忐忑過渡到現在的平靜,現在想來,整個的優化過程感覺還是比較好玩的,趁著現在還有些印象,就把整個排查&優化過程詳細記錄下來,以備不時之需,也希望能給其他人一些啟發.

問題背景

上周團隊聚餐時,老大說有一個用戶使用DB的時候遇到了問題,現有的DB性能無法滿足用戶的性能需求.用戶在對現有的DB進行壓力測試時發現QPS+TPS小于7W/S,繼續加大壓力的時候Load上漲、IdleCPU很低、Threadrunning飆升、性能下降,最終導致網站處理并發能力的下降,無法達到預期的吞吐量.

用戶在對現有邏輯及吞吐量計算的基礎上提出了性能指標,即DB的單機性能QPS+TPS大于10W/S,只有這樣才能滿足業務要求,否則DB就是整個鏈路的瓶頸.

由于用戶的上線時間臨近,上線壓力比較大,老大說周末盡力搞定,如果搞不定,只能上最好的機器來解決性能問題,這樣的話,成本就要上來了.(當時正在吃飯,瞬間感覺壓力山大,不能好好吃肉了有木有……)

現場信息收集

第二天還沒醒就收到老大的RTX消息,然后懷著疑惑的心情火速上線,登錄到機器上,開始了DB性能的調優之旅……

首先,使用orzdba監控工具查看了用戶實例的性能狀態,如下所示：

從上面的性能信息可以發現命中率 100%, 即用戶基本是全內存操作,thread running 較高,CPU 有少量, thread running 彪升,到底線程在做什么呢?

懷著這樣的疑問,然后執行了一下 pstack {pid of mysqld} > pid.info 以獲取實例的內部線程信息,然后使用 pt-pmp pid.info 將堆棧信息進行顯示,發現了如下的信息：

根據上述的 pt-pmp & pstack 文件相結合,可以看到如下堆棧：

根據上面收集的信息我們可以清楚的得出以下結論：

• 應用在執行SQL語句的過程中,table_cache_manager 中的鎖沖突比較嚴重；
• MySQL Server?層中的 MDL_lock 沖突比較重；
• 實例開啟了 Performance_schema 功能；

經過了上面的分析,我們需要著重查看上述問題的相關變量,變量設置的情況會對性能造成直接的影響,執行結果如下：

參數分析

這里我們先來介紹一下上述參數在 MySQL 中的作用 & 含義：

table_open_cache_instances 簡介

table_open_cache_instances 指的是 MySQL 緩存 table 句柄的分區的個數,而每一個 cache_instance 可以包含不超過table_open_cache/table_open_cache_instances 的table_cache_element,MySQL 打開表的過程可以簡單的概括為：

1. 根據線程的 thread_id 確定線程將要使用的 table_cache,即 thread_id % table_cache_instances;
2. 從該 tabel_cache 元素中查找相關系連的 table_cache_element,如果存在轉 3,如果不存在轉 4；
3. 從 2 中查找的table_cache_element 的 free_tables 中出取一個并返回,并調整 table_cache_element 中的 free_tables & used_tables 元素；
4. ?如果 2 中不存在,則重新創建一個 table, 并加入對應的 table_cache_element 的 used_tables的列表；

?從以上過程可以看出,MySQL 在打開表的過程中會首先從 table_cache 中進行查找有沒有可以直接使用的表句柄,有則直接使用,沒有則會創建并加入到對應的 table_cache 中對應的 table_cache_element 中,從剛才提取的現場信息來看,有大量的線程在查找 table_cache 的過程中阻塞著了,而 table_open_cache_instances 的個數為 1, 因此,此參數的設置需要調整,由于 table_open_cache_instances 的大小和 線程 ID & 并發 有關系,考慮當前的并發是1000左右,于是將該植設置為 32；

MySQL 中不同的線程雖然使用各自的 table 句柄,但是共享著同一個table_share,如果想從源碼上了解 table & table_share 以及 兩者之間的相互,可以從變量 table_open_cache, table_open_cache_instances,table_definition_cache 入手,閱讀 Table_cache_manager, Table_cache, Table_cache::get_table 等相關代碼,由于篇幅限制,在此不在詳述.

MDL Lock 的前世今生

?在 5.1 中有一個 binlog log 亂序的問題,詳情及復現方法可以參考這篇文章：《alter table rename 操作導致復制中斷》(http://mysqllover.com/?p=93),MDL_LOCK 就是為了解決上述問題而在 5.5 中引入的.

簡單來說 MDL Lock 是 MySQL Server 層中的表鎖,主要是為了控制 Server 層 DDL & DML 的并發而設計的, 但是 5.5 的設計中只有一把大鎖,所以到5.6中添加了參數 metadata_locks_hash_instances 來控制分區的數量,進而實現大鎖的拆分,雖然鎖的拆分提高了并發的性能,但是仍然存在著不少的性能問題,所以在 5.7.4 中 MDL Lock 的實現方式采用了 lock free 算法,徹底的解決了 Server 層表鎖的性能問題,而參數 metadata_locks_hash_instances 也將會在之后的某個版本中被刪除掉.

參考文檔：metadata_locks_hash_instances(http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_metadata_locks_hash_instances)

由于實例中的表的數目比較多,而 metadata_locks_hash_instances 的參數設置僅為8,因此,為了將底鎖的沖突的可能性,我們將此值設置為 32.

Performance Schema作用&影響

通俗來說,performance schema 是 MySQL 的內部診斷器,用于記錄 MySQL 在運行期間的各種信息,如表鎖情況、mutex 竟爭情況、執行語句的情況等,和 Information Schema 類似的是擁用的信息都是內存信息,而不是存在磁盤上的,但和 information_schema 有以下不同點：

• information_schema?中的信息都是從 MySQL 的內存對象中讀出來的,只有在需要的時候才會讀取這些信息,如 processlist, profile, innodb_trx 等,不需要額外的存儲或者函數調用,而 performance schema 則是通過一系列的回調函數來將這些信息額外的存儲起來,使用的時候進行顯示,因此 performance schema 消耗更多的 CPU & memory 資源；
• Information_schema?中的表是沒有表定義文件的,表結構是在內存中寫死的,而 performation_schema 中的表是有表定義文件的；
• 實現方式不同,Information_schema 只是對內存的 traverse copy, 而 performance_schema 則使用固定的接口來進行實現；
• 作用不同,Information_schema 主要是輕量級的使用,即使在使用的時候也很少會有性能影響,performance_schema 則是 MySQL 的內部監控系統,可以很好的定位性能問題,但也很影響性能；

由以上的分析不難看出,在性能要求比較高的情況下,關閉 performance_schema 是一個不錯的選擇,因此將 performance_schema 關閉.另外關閉 performance_schema 的一個原因則是因為它本身的穩定性,因為之前在使用 performance_schema 的過程中遇到了不穩定的問題,當然,遇到一個問題我們就會修復一個,只是考慮到性能問題,我們暫時將其關閉.

Performance_schema 的詳細使用說明可以參考：

• performance_schema?中文文檔

  (http://keithlan.github.io/2015/07/17/22_performance_schema/)

• MySQL_Performance_Schema 官方文檔

  (https://dev.mysql.com/doc/refman/5.6/en/performance-schema.html)

經過上面的分析和判斷,我們對參數做了如下的調整：

table_open_cache_instances=32

metadata_locks_hash_instances=32

performance_schema=OFF

innodb_purge_threads=4

勉強解決問題

調整了以上參數后,我們重啟實例,然后要求客戶做新一輪的壓力測試,測試部分數據如下：

從以上的測試數據來看, QPS + TPS > 10W 已經滿足要求,通過 perf top -p {pidof mysqld} 命令查看了一下系統負載,發現了一處比較吃 CPU 的地方 ut_delay,詳情如下：

使用 perf record & perf report 進行分析,發現調用比較多的地方是：

mutex_spin_wait,于是斷定 Innodb 底層資源沖突比較嚴重,根據以往的經驗執行如下命令：

在 MySQL 內部,當 innodb 線程獲取 mutex 資源而得不到滿足時,會最多進行 innodb_sync_spin_loops 次嘗試獲取 mutex 資源,每次失敗后會調用 ut_delay(ut_rnd_interval(0, srv_spin_wait_delay),導致 ut_delay 占用了過多的 CPU, 其中 ut_delay 的定義如下：

由于這兩個值的設定取決于實例的負載以及資源的竟爭情況,所以不斷的嘗試設置這兩個參數的值,經過多次的嘗試最終將這兩個參數分別設置為：

innodb_spin_wait_delay = 6, innodb_sync_spin_loops = 20 (請注意這兩個值不是推薦值!!) 才將 ut_delay 的占用資源降下來,最終降低了不必要的 CPU 消耗的同時 idle cpu 也穩定在了 20+,具體資源占用詳情如下：

優化到這個地步似乎達到了客戶要求的性能,即 DB 單機性能為 QPS + TPS > 10W,可是如果并發量在加大,我們的 DB 能扛住更高的壓力嗎?

又起波瀾

經過上面參數的調整,DB 已經不是性能的瓶頸,應用的吞吐量由之前的 1100 -> 1400+,但是離 2000 的吞吐量還比較遠,瓶頸出現在了應用端,為了增加吞吐量,客戶又增加了幾臺客戶端機器,連接數也由之前的 900+ 上升到 1000+,此時發現 DB 雖然能夠響應,但偶爾會出現 thread running 飆高的情況,具體運行狀態如下,其中 mysql_com_tps = (mysql_com_insert + mysql_com_update + mysql_com_delete)：

查找問題原因

Thread running 的偶爾飆升引起了我的注意,說明內部必然有沖突,隨著壓力和并發量的不斷增大,應用可能會受到類似之前的影響,因此很有必要查看其中的原因并盡最大的努力解決之.通過仔細觀察 thread running & mysql com 信息,當 thread running 較高 & com 信息較低的時候,執行了 pt-pmp -p {pid of mysqld},抓到了以下信息：

從上面的現場信息不難看出有很大一部分線程是在執行 read_view 的相關操作中被阻塞著了,那么什么是 read view,它的作用是什么,為什么會有大量的線程執行這個操作的時候被阻塞呢?

什么是 read view

read view 又稱讀視圖,用于存儲事務創建時的活躍事務集合.當事務創建時,線程會對 trx_sys 上全局鎖,然后遍歷當前活躍事務列表,將當前活躍事務的ID存儲在數組中的同時,記錄最大事務 low_limit_id &　最小事務 high_limit_id & 最小序列化事務 low_limit_no.

read view 的作用是什么

InnoDB record 格式包含 {記錄頭,主建,Trx_id,roll_ptr, extra_column} 等信息.

當事務執行時,凡是大于low_limit_id 的數據對于事務是不可見的,凡是事務小于 high_limit_id 的數據都是可見的,事務 ID 是 read_view 數組中的某一個時也是不可見的,Purge thread 在執行 Purge 操作時,凡是小于 low_limit_no 的數據,都是可以被 Purge 的,因此, read view 是 MySQL MVCC 實現的基礎.

為什么會有大量的線程阻塞

事務創建時的步驟如下：

• 對 trx_sys->mutex 全局上鎖；
• 順序掃描 trx_sys->rw_trx_list,對 read_view 中的元素分配內存并進行賦值,主要包括活躍事務ID的集合的創建,low_limit_id , high_limit_id, low_limit_no 等；
• 將該 read_view 添加到有序列表 trx_sys->view_list中；
• 釋放?trx_sys->mutex 鎖；

由于read_view 的創建和銷毀都需要獲取 trx_sys->mutex, 當并發量很大的時候,事務鏈表會比較長,又由于遍歷本身也是一個費時的工作,所以此處便成為了瓶頸,既然我們遇到了這個問題,那么社區應該也有類似的問題.

read view 問題解決過程

首先,我們看一下bug#49169(https://bugs.mysql.com/bug.php?id=49169),read_view_open_now is inefficient with many concurrent sessions, 即當并發量很大時 read_view_open_now 效率低下的問題,問題的原因主要有以下幾個：

• 整個創建過程一直持有 trx_sys->mutex 鎖；
• read_view?的內存在每次創建中被分配,事務提交后被釋放；
• 需要遍歷 trx_sys->trx_list (5.5) 或 trx_sys->rw_list (5.6)；
• 并發較大,活躍事務鏈表過長時,會在 trx_sys->mutex 上有較大的消耗；

該 bug 從 MySQL 5.1 的時候被 mrak 大神提出以來,一直到 MySQL 5.7 才被官方完整的解決,其中的解決過程也挺曲折的,另外 Percona 在 5.5 的時候就也推出了解決問題的辦法,實現也相對簡單好多,但沒有 MySQL 5.7 方法的徹底,咱們分別看一下這兩種解決方法以及 CDB 內核在這方面的改動.

Percona read view 問題改進

Percona 為了解決上述描述的問題,對trx_sys做了以下修改：

• 在 trx_sys下維護一個全局的事務ID的有序集合,事務的 創建 & 銷毀 的同時將事務的 ID 從這個集合中移除；
• 在 trx_sys下維護一個有序的已分配序列號的事務列表,已記錄擁有最小序列號的事務,供 purge 時使用；
• 減少不必要的內存分配,為每一個 trx_t 緩存一個 read_view,read_view 數組的大小根據創建時的活躍全局事務 ID 集合做必要的調整；

做了上面的調整后,事務在創建過程中則不需要遍歷 trx_sys->trx_list(version 5.5),直接使用 memcpy 即可獲得活躍事務的ID,并且緩存的使用也大大減少了內存的不必要分配；

更詳細的信息及源碼可以參考 Alexey (sysbench owner, MySQL 另一大神)提交的代碼,commit message 詳情如下：

MySQL read view 問題改進

為了解決 read view 問題,5.6 做了以下幾件事情：

• 將 5.5 的 trx_list 拆分為 ro_trx_list & rw_trx_list, 由于只讀事務不會對數據進行修改,所以在創建視圖的同時就只需要掃描 rw_trx_list 即可；
• auto-commit-non-locking-ro transactions?的特殊優化；
• 添加語法 START TRANSACTION READ ONLY 用于聲名事務是只讀事務；

經過上面的修改,似乎解決了 read view 的問題,但實際卻不然,因為他只是解決了事務鏈表的長度,創建時遍歷&內存消耗的開銷是沒有解決的,并且使用上述特性需要修改應用程序,這一點是比較困難的,因此,5.7為了徹底的解決 read view 的性能問題,做了以下事情：

1. Refactor the MVCC code
2. Reuse read views for AC-NL-RO selects
3. Use a pool of read views
4. Add MVCC class
5. Use a trx_id to trx_t* map
6. Keep the active trx_id_ts in a vector.
7. Pre-allocate a small cache of record and table locks
8. Avoid extra work when a transaction is tagged as read-only (during commit).
9. General code cleanup
10. Get rid of trx_sys_t::ro_trx_list. Adding and removing a transaction from the ro_trx_list ???is very costly.

經過了上面的代碼重構,5.7 中很少看到 trx_sys->mutex 的性能瓶頸,有想更詳細了解的同學可以看一下這些內容：

mutex" width="560" height="249" />

CDB read view 問題改進

為了解決 Read view 的性能問題,簡單的說 CDB 內核團隊對于Read view 主要做了以下事情：

• backport percona?的 read view 相關修改到 CDB MySQL中；
• 參照 5.7 的實現,在 5.6 中將 ro_trx_list 移除；

經過上面的修改徹底的解決了 read_view 的性能問題,在經歷了大量 穩定性測試 & 性能測試 后,目前灰度發布中.

線上效果

鑒于當前存在的問題,為了解決客戶的燃眉之急,決定上一個新版本,和客戶聯系后,可以重啟實例,然后進行了替換操作,替換后的性能效果如下,可以看到 cpu 使用率、load、thread running 降低的同時 QPS + TPS 性能上升,至此問題真正覺得問題應該解決了,余下的就是等客戶的反饋了.

將監控數據入庫,查看峰值 & 當時的負載情況,詳情如下：

遺留問題 & 展望

真的完美了嗎,其實不是這樣的,我們還有很多的事情要做,因為在解決問題的過程中,我們通過 pstack & pt-pmp 抓到了很多有用的信息,有一些是暫時沒有解決的,如：

• InnoDB內部表鎖沖突嚴重；
• MDL Lock?即使擴大也存在著不小的影響；
• 內存分配也有一些需要優化的地方；
• 執行計劃的計算代價比較高；
• thread running?彪高時沒有可以控制的方法；
• ….

由于時間問題我們暫時將遇到的問題一一記下,一個一個解決,我們相信 CDB 的內核會越來越強大,在提升性能的同時也不斷的提升穩定性,我們一步一步踏在當下,努力變得更好!

文章出處：DBAplus社群(訂閱號ID：dbaplus)

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/4405.html