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

作者介紹

姜宇祥,2012年加入攜程,10年數(shù)據(jù)庫核心代碼開發(fā)經(jīng)驗(yàn),相關(guān)開發(fā)涉及達(dá)夢、MySQL數(shù)據(jù)庫.現(xiàn)致力于攜程MySQL的底層研發(fā),為特殊問題定位和處理提供技術(shù)支持.

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

問題描述

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

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

1. 數(shù)據(jù)庫的版本為5.7.18,保留分區(qū)表,性能會下降.
2. 數(shù)據(jù)庫版本為5.7.18,把表調(diào)整為非分區(qū)表,性能正常.
3. 把數(shù)據(jù)庫的版本回退到5.6.21版本,保留分區(qū)表,性能也是正常.

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

問題重現(xiàn)

測試環(huán)境的數(shù)據(jù)庫表結(jié)構(gòu)比較多,并且調(diào)用關(guān)系也比較復(fù)雜.為了進(jìn)一步分析并定位問題,我們抽絲剝繭,構(gòu)建了如下一個簡單的重現(xiàn)過程：

// 創(chuàng)建一個測試分區(qū)表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) */

// 插入測試數(shù)據(jù)

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的 記錄 做一個更新操作,事務(wù)先不提交.

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

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

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

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

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

觀察此時的innodb_locks表,事務(wù)id=40021鎖住第3頁的第2行記錄,導(dǎo)致事務(wù)id=40022無法進(jìn)行下去.

我們把數(shù)據(jù)庫回退到5.6.21版本,則不能重現(xiàn)上述場景.

進(jìn)一步分析

根據(jù)innodb_locks表提供的信息,我們知道問題在于InnoDB鎖定了不恰當(dāng)?shù)男?該表是memory存儲引擎.我們在memory 存儲引擎的插入接口設(shè)置斷點(diǎn),得到如下堆棧信息.確定是紅框部分,將鎖信息寫入到innodb_locks表中.

并在函數(shù)fill_innodb_locks_from_cache中得以確認(rèn),每次寫入行的數(shù)據(jù),都是從如下代碼中Cache對象中獲取的.

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

針對lock_t 類型的使用位置進(jìn)行排查.經(jīng)過篩選和調(diào)試,發(fā)現(xiàn)函數(shù)RecLock::lock_add中,生成的行鎖被加入到該鎖所在的事務(wù)鏈表中.

RecLock::lock_add函數(shù)可以推出行鎖的生成原因.因此,通過對該函數(shù)進(jìn)行斷點(diǎn)設(shè)置,查看函數(shù)堆棧,在如下堆棧內(nèi),定位到紅框位置的函數(shù)：

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

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

驗(yàn)證結(jié)論

根據(jù)之前的分析,每次單條記錄的update操作,會對分區(qū)表數(shù)目相同的行數(shù)進(jìn)行上鎖.我們嘗試驗(yàn)證我們的發(fā)現(xiàn).

新增如下兩條記錄：

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

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

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

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

// SESSION 2 現(xiàn)在對id = 4 的記錄做一個更新.

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

我們發(fā)現(xiàn),對id = 4的更新可以正常進(jìn)行.不會受到id = 1 的更新影響.這是因?yàn)閕d=4的記錄,超過了測試案例的分區(qū)個數(shù),不會被鎖住.在實(shí)際應(yīng)用中,分區(qū)表所定義分區(qū)數(shù)不會如測試用例中的只有3個,而是數(shù)十個乃至數(shù)百個.這樣進(jìn)行上鎖的結(jié)果,將加劇更新情況下的鎖沖突,導(dǎo)致事務(wù)處于鎖等待狀態(tài).如下圖所示,每個事務(wù)都上N個行鎖,那么這些上鎖記錄互相覆蓋的可能性就極大的提高,也就導(dǎo)致并發(fā)下降,效率降低.

結(jié)論

通過上述分析,我們非常確認(rèn),這個應(yīng)該是MySQL 5.7版本的一個regression.我們提交了一個Bug到開源社區(qū).Oracle確認(rèn)是一個問題,需進(jìn)一步分析調(diào)查這個Bug.

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/1951.html