腾讯云布道师：一次性能峰值提升10W的DB调优之旅(2)

发布时间：2021-01-07 14:34 所属栏目：53 来源：网络整理

导读：简单来说 MDL Lock 是 MySQL Server 层中的表锁,主要是为了控制 Server 层 DDL DML 的并发而设计的,但是 5.5 的设计中只有一把大锁,所以到5.6中添加了参数 metadata_locks_hash_instances 来控制分区的数量,进而实

简单来说 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 能扛住更高的压力吗?

又起波澜

（编辑：ASP站长网）