告知 MySQL5.7.18 的使用者分区表使用中存在的陷阱, 避免在该版本上继续踩坑. 同时通过对源码的讲解, 升级 MySQL5.7.18 时分区表性能下降的根本原因, 向 MySQL 源码爱好者展示分区表实现中锁的运用.
问题描述
MySQL 5.7 版本中, 性能相关的改进非常多. 包括临时表相关的性能改进, 连接建立速度的优化和复制分发相关的性能改进等等. 基本上不需要做配置修改, 只需要升级到 5.7 版本, 就能带来不少性能的提升.
我们在测试环境, 把数据库升级到 5.7.18 版本, 验证 MySQL 5.7.18 版本是否符合我们的预期. 观察运行了一段时间, 有开发反馈, 数据库的性能比之前的 5.6.21 版本有下降. 主要的表现特征是遇到比较多的锁超时情况. 开发另外反馈, 性能下降相关的表都是分区表. 更新走的都是主键. 这个反馈引起了我们重视. 我们做了如下尝试:
数据库的版本为 5.7.18, 保留分区表, 性能会下降.
数据库版本为 5.7.18, 把表调整为非分区表, 性能正常.
把数据库的版本回退到 5.6.21 版本, 保留分区表, 性能也是正常
通过上述测试, 我们大致判定, 这个性能下降和 MySQL 5.7 版本升级有关.
问题重现
测试环境的数据库表结构比较多, 并且调用关系也比较复杂. 为了进一步分析并定位问题, 我们抽丝剥茧, 构建了如下一个简单的重现过程.
- // 创建一个测试分区表 t2:
- CREATE TABLE `t2` (
- `id` INT(11) NOT NULL,
- `dt` DATETIME NOT NULL,
- `data` VARCHAR(10) DEFAULT NULL,
- PRIMARY KEY (`id`,`dt`),
- KEY `idx_dt` (`dt`)
- ) ENGINE=INNODB DEFAULT CHARSET=latin1
- /*!50100 PARTITION BY RANGE (to_days(dt))
- (PARTITION p20170218 VALUES Less THAN (736744) ENGINE = InnoDB,
- PARTITION p20170219 VALUES Less THAN (736745) ENGINE = InnoDB,
- PARTITION pMax 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.linuxidc.com/Linux/2018-12/156004.htm