《一個MySQL 5.7分區表性能下降的案例分析與排查》要點：
本文介紹了一個MySQL 5.7分區表性能下降的案例分析與排查，希望對您有用。如果有疑問，可以聯系我們。

作者介紹

姜宇祥,2012年加入攜程,10年數據庫核心代碼開發經驗,相關開發涉及達夢、MySQL數據庫.現致力于攜程MySQL的底層研發,為特殊問題定位和處理提供技術支持.

前言：希望通過本文,使MySQL5.7.18的使用者知曉分區表使用中存在的陷阱,避免在該版本上繼續踩坑.同時通過對源碼的分享,升級MySQL5.7.18時分區表性能下降的根本原因,向MySQL源碼愛好者展示分區表實現中鎖的運用.

問題描述

MySQL 5.7版本中,性能相關的改進非常多.包括臨時表相關的性能改進,連接建立速度的優化和復制分發相關的性能改進等等.基本上不需要做配置修改,只需要升級到5.7版本,就能帶來不少性能的提升.

我們在測試環境,把數據庫升級到5.7.18版本,驗證MySQL 5.7.18版本是否符合我們的預期.觀察運行了一段時間,有開發反饋,數據庫的性能比之前的5.6.21版本有下降.主要的表現特征是遇到比較多的鎖超時情況.開發另外反饋,性能下降相關的表都是分區表.更新走的都是主鍵.這個反饋引起了我們重視.我們做了如下嘗試：

1. 數據庫的版本為5.7.18,保留分區表,性能會下降.
2. 數據庫版本為5.7.18,把表調整為非分區表,性能正常.
3. 把數據庫的版本回退到5.6.21版本,保留分區表,性能也是正常.

通過上述測試,我們大致判定,這個性能下降和MySQL5.7版本升級有關.

問題重現

測試環境的數據庫表結構比較多,并且調用關系也比較復雜.為了進一步分析并定位問題,我們抽絲剝繭,構建了如下一個簡單的重現過程：

// 創建一個測試分區表t2:

CREATE TABLE `t2`(

`id` INT(11) NOT NULL,

`dt` DATETIME NOT NULL,

`data` VARCHAR(10) DEFAULT NULL,

PRIMARYKEY (`id`,`dt`),

KEY`idx_dt`(`dt`)

) ENGINE=INNODB DEFAULTCHARSET=latin1

/*!50100 PARTITION BY RANGE (to_days(dt))

(PARTITION p20170218 VALUES LESS THAN (736744)ENGINE = InnoDB,

PARTITIONp20170219 VALUES LESS THAN (736745) ENGINE = InnoDB,

PARTITIONpMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

// 插入測試數據

INSERT INTO t2 VALUES (1, NOW(), ‘1’);

INSERT INTO t2 VALUES (2, NOW(), ‘2’);

INSERT INTO t2 VALUES (3, NOW(), ‘3’);

// SESSION 1 對id = 1的 記錄 做一個更新操作,事務先不提交.

BEGIN;UPDATE t2 SET DATA = ’12’ WHERE id = 1;

// SESSION 2 對id = 2 的記錄做一個更新.

BEGIN;UPDATE t2 SET DATA = ’21’ WHERE id = 2;

在SESSION 2,我們發現,這個更新操作一直在等待.ID是主鍵,按道理,主鍵id = 1 的記錄更新,不至于影響到主鍵id = 2的記錄更新.

查詢information_schema下的innodb_locks這張表.這張表是用于記錄InnoDB事務嘗試申請但還未獲取的鎖,以及阻塞其它事務的事務所擁有的鎖.有兩條記錄：

觀察此時的innodb_locks表,事務id=40021鎖住第3頁的第2行記錄,導致事務id=40022無法進行下去.

我們把數據庫回退到5.6.21版本,則不能重現上述場景.

進一步分析

根據innodb_locks表提供的信息,我們知道問題在于InnoDB鎖定了不恰當的行.該表是memory存儲引擎.我們在memory 存儲引擎的插入接口設置斷點,得到如下堆棧信息.確定是紅框部分,將鎖信息寫入到innodb_locks表中.

并在函數fill_innodb_locks_from_cache中得以確認,每次寫入行的數據,都是從如下代碼中Cache對象中獲取的.

我們知道Cache中保存了事務鎖的信息,因此需要進一步查找Cache中的數據,是如何添加進去的.通過搜索cache對象在innodb代碼中出現的位置,找到函數add_lock_to_cache.在此函數設置斷點進行調試后,發現其內容與填寫innodb_locks表的數據一致.確定該函數使用的lock對象,就是我們要找的鎖對象.

針對lock_t 類型的使用位置進行排查.經過篩選和調試,發現函數RecLock::lock_add中,生成的行鎖被加入到該鎖所在的事務鏈表中.

RecLock::lock_add函數可以推出行鎖的生成原因.因此,通過對該函數進行斷點設置,查看函數堆棧,在如下堆棧內,定位到紅框位置的函數：

針對Partition_helper::handle_ordered_index_scan的如下代碼進行跟蹤,根據該段代碼的分析,m_part_spec.end_part 決定了進行上鎖的最大行數,此處即為非正常行鎖生成的原因.

最終問題歸結到m_part_spec.end_part 的生成原因.通過對end_part 使用地方進行排查,最終在get_partition_set函數中定位到該變量在使用前的初始設置值.從代碼中可以看出,每次單條記錄的update操作,在進行index scan上鎖時,對分區表數目相同的行數進行上鎖.這個是根本原因.

驗證結論

根據之前的分析,每次單條記錄的update操作,會對分區表數目相同的行數進行上鎖.我們嘗試驗證我們的發現.

新增如下兩條記錄：

INSERT INTO t2 VALUES (4, NOW(), ‘4’);

INSERT INTO t2 VALUES (5, NOW(), ‘5’);

// SESSION 1 對id = 1的 記錄 做一個更新操作,事務先不提交.

BEGIN;UPDATE t2 SET DATA = ’12’ WHERE id = 1;

// SESSION 2 現在對id = 4 的記錄做一個更新.

BEGIN;UPDATE t2 SET DATA = ’44’ WHERE id = 4;

我們發現,對id = 4的更新可以正常進行.不會受到id = 1 的更新影響.這是因為id=4的記錄,超過了測試案例的分區個數,不會被鎖住.在實際應用中,分區表所定義分區數不會如測試用例中的只有3個,而是數十個乃至數百個.這樣進行上鎖的結果,將加劇更新情況下的鎖沖突,導致事務處于鎖等待狀態.如下圖所示,每個事務都上N個行鎖,那么這些上鎖記錄互相覆蓋的可能性就極大的提高,也就導致并發下降,效率降低.

結論

通過上述分析,我們非常確認,這個應該是MySQL 5.7版本的一個regression.我們提交了一個Bug到開源社區.Oracle確認是一個問題,需進一步分析調查這個Bug.

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