干貨 | 一個MySQL 5.7分區(qū)表性能下降的案例分析
前言：希望通過本文，使MySQL5.7.18的使用者知曉分區(qū)表使用中存在的陷阱，避免在該版本上繼續(xù)踩坑。同時通過對源碼的分享，升級MySQL5.7.18時分區(qū)表性能下降的根本原因，向MySQL源碼愛好者展示分區(qū)表實現(xiàn)中鎖的運用。
問題描述
MySQL 5.7版本中，性能相關(guān)的改進非常多。包括臨時表相關(guān)的性能改進，連接建立速度的優(yōu)化和復制分發(fā)相關(guān)的性能改進等等。基本上不需要做配置修改，只需要升級到5.7版本，就能帶來不少性能的提升。
我們在測試環(huán)境，把數(shù)據(jù)庫升級到5.7.18版本，驗證MySQL 5.7.18版本是否符合我們的預期。觀察運行了一段時間，有開發(fā)反饋，數(shù)據(jù)庫的性能比之前的5.6.21版本有下降。主要的表現(xiàn)特征是遇到比較多的鎖超時情況。開發(fā)另外反饋，性能下降相關(guān)的表都是分區(qū)表。更新走的都是主鍵。這個反饋引起了我們重視。我們做了如下嘗試：
- 數(shù)據(jù)庫的版本為5.7.18, 保留分區(qū)表，性能會下降。
- 數(shù)據(jù)庫版本為5.7.18，把表調(diào)整為非分區(qū)表，性能正常。
- 把數(shù)據(jù)庫的版本回退到5.6.21版本，保留分區(qū)表，性能也是正常
通過上述測試，我們大致判定，這個性能下降和MySQL5.7版本升級有關(guān)。
問題重現(xiàn)
測試環(huán)境的數(shù)據(jù)庫表結(jié)構(gòu)比較多，并且調(diào)用關(guā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的 記錄 做一個更新操作，事務先不提交。
- 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事務嘗試申請但還未獲取的鎖，以及阻塞其他事務的事務所擁有的鎖。有兩條記錄：
觀察此時的innodb_locks表，事務id=40021鎖住第3頁的第2行記錄，導致事務id=40022無法進行下去。
我們把數(shù)據(jù)庫回退到5.6.21版本，則不能重現(xiàn)上述場景。
進一步分析
根據(jù)innodb_locks表提供的信息，我們知道問題在于InnoDB鎖定了不恰當?shù)男小Ｔ摫硎莔emory存儲引擎。我們在memory 存儲引擎的插入接口設(shè)置斷點，得到如下堆棧信息。確定是紅框部分，將鎖信息寫入到innodb_locks表中。
并在函數(shù)fill_innodb_locks_from_cache中得以確認，每次寫入行的數(shù)據(jù)，都是從如下代碼中Cache對象中獲取的。
我們知道Cache中保存了事務鎖的信息，因此需要進一步查找Cache中的數(shù)據(jù)，是如何添加進去的。通過搜索cache對象在innodb代碼中出現(xiàn)的位置，找到函數(shù)add_lock_to_cache。在此函數(shù)設(shè)置斷點進行調(diào)試后，發(fā)現(xiàn)其內(nèi)容與填寫innodb_locks表的數(shù)據(jù)一致。確定該函數(shù)使用的lock對象，就是我們要找的鎖對象。
針對lock_t 類型的使用位置進行排查。經(jīng)過篩選和調(diào)試，發(fā)現(xiàn)函數(shù)RecLock::lock_add中，生成的行鎖被加入到該鎖所在的事務鏈表中。
RecLock::lock_add函數(shù)可以推出行鎖的生成原因。因此，通過對該函數(shù)進行斷點設(shè)置，查看函數(shù)堆棧，在如下堆棧內(nèi)，定位到紅框位置的函數(shù)：
針對Partition_helper::handle_ordered_index_scan的如下代碼進行跟蹤，根據(jù)該段代碼的分析，m_part_spec.end_part 決定了進行上鎖的***行數(shù)，此處即為非正常行鎖生成的原因。
最終問題歸結(jié)到m_part_spec.end_part 的生成原因。通過對end_part 使用地方進行排查，最終在get_partition_set函數(shù)中定位到該變量在使用前的初始設(shè)置值。從代碼中可以看出，每次單條記錄的update操作，在進行index scan上鎖時，對分區(qū)表數(shù)目相同的行數(shù)進行上鎖。這個是根本原因。
驗證結(jié)論
根據(jù)之前的分析，每次單條記錄的update操作，會對分區(qū)表數(shù)目相同的行數(shù)進行上鎖。我們嘗試驗證我們的發(fā)現(xiàn)。
新增如下兩條記錄：
- 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 現(xiàn)在對id = 4 的記錄做一個更新。
- BEGIN;UPDATE t2 SET DATA = '44' WHERE id = 4;
我們發(fā)現(xiàn)，對id = 4的更新可以正常進行。不會受到id = 1 的更新影響。這是因為id=4的記錄，超過了測試案例的分區(qū)個數(shù)，不會被鎖住。在實際應用中，分區(qū)表所定義分區(qū)數(shù)不會如測試用例中的只有3個，而是數(shù)十個乃至數(shù)百個。這樣進行上鎖的結(jié)果，將加劇更新情況下的鎖沖突，導致事務處于鎖等待狀態(tài)。如下圖所示，每個事務都上N個行鎖，那么這些上鎖記錄互相覆蓋的可能性就極大的提高，也就導致并發(fā)下降，效率降低。
結(jié)論
通過上述分析，我們非常確認，這個應該是MySQL 5.7版本的一個regression。我們提交了一個Bug到開源社區(qū)。Oracle確認是一個問題，需進一步分析調(diào)查這個Bug。
