MySQL 8.0 新特性：Resource Group 与写入性能优化实战

原创

王文安@DBA

修改于 2020-11-19 10:29:56

1.4K0

修改于 2020-11-19 10:29:56

前言

离 8.0 正式发布已经有一段时间了，随着小版本的不断迭代，MySQL 8.0 也加入了很多有意思/有价值的特性，例如原子 DDL，WriteSet复制，二级密码等，在专栏中也有非常多的介绍。本文介绍的特性是 Resource Group，即资源组，主要用来调度 MySQL 的资源用，其实是一个兼顾了实用性和技巧性的功能，且刚好能解决 MySQL 8.0 对写入性能的一个“负优化”。

资源组

简介

MySQL 本身是一个单进程多线程的架构，除了处理用户连接的线程外，还有很多的后台线程，例如 flush_thread，purge_thread，read_thread，write_thread 等。在旧版本中，这些线程都是同样的优先级，共享所有的 CPU 资源，也无法像 cgroup 一样把一些线程绑定到固定的核心上，独占一部分 CPU 资源。

而面对实际业务的时候，根据业务的特点来调度资源，应对不同的业务高峰期是常用的手段之一，比如业务高峰期来临之前，让业务相关的线程独占一部分 CPU 资源，提高业务处理能力。

在 MySQL 8.0 中就可以使用资源组来满足这一类需求。使用方式也比较简单：创建一个资源组，然后把线程绑定到对应的资源组即可。

如何使用

创建资源组的语法如下：

CREATE RESOURCE GROUP group_name
    TYPE = {SYSTEM|USER}
    [VCPU [=] vcpu_spec [, vcpu_spec] ...]
    [THREAD_PRIORITY [=] N]
    [ENABLE|DISABLE]

vcpu_spec: {N | M - N}

TYPE：设置资源组的类型，MySQL 的后台线程只能绑定到 SYSTEM 的资源组，用户线程只能绑定到 USER。
VCPU：用逗号分隔的序号列表，或者是用类似于 1-10 的范围形式。
THREAD_PRIORITY：设置优先级，数字越小，优先级越高。

绑定资源组的语法如下：

 SET RESOURCE GROUP group_name FOR thread_id1,thread_id2 ......

绑定是即时生效的，命令执行完之后立刻就能看到效果。

写入性能优化

简介

这个问题实际上是跑 8.0 基准测试的时候发现的：同配置的情况下，8.0 的写入性能相比 5.7 是下降的，而且下降的幅度并不能当做随机误差来看待。因此研究了一下 8.0 的变化，发现有一项优化：把 log_writer 和 log_flusher 拆分成了单独的线程。使用双 1 的事务提交策略时，每次提交事务都会需要写 log，是不是这两个线程单独拆出来之后，因为抢不到 CPU 资源影响到了写入性能？

测试一下

这次测试的环境：

腾讯云的高 IO 型 CVM，本地 NVME 磁盘作为存储，16 核心，64GB 内存，debian 9.13。
MySQL 8.0.22，sysbench-1.0.20。
事务提交策略为双 1，数据量为 5 张表，每张表 1000 万行数据，可以全部加载到 buffer_pool。

为了使用资源组，需要先在系统层配置一下环境：

apt-get install libcap2-bin
setcap cap_sys_nice+ep /usr/sbin/mysqld
getcap /usr/sbin/mysqld
显示：/usr/sbin/mysqld = cap_sys_nice+ep 即可

重启 MySQL 之后，预热好数据，开启 write_only 的测试，然后再设置资源组：

CREATE RESOURCE GROUP bg_thread1 TYPE = system VCPU = 0  THREAD_PRIORITY = -20;
CREATE RESOURCE GROUP bg_thread2 TYPE = system VCPU = 1  THREAD_PRIORITY = -20;
CREATE RESOURCE GROUP other_thread TYPE = system VCPU = 2-15  THREAD_PRIORITY = 0;

通过系统表找到 log_writer 和 log_flusher 的 thread_id，以及测试线程的 ID：

mysql> select THREAD_ID,NAME from performance_schema.threads;
+-----------+---------------------------------------------+
| THREAD_ID | NAME                                        |
+-----------+---------------------------------------------+
|         1 | thread/sql/main                             |
|       783 | thread/sql/one_connection                   |
|         3 | thread/innodb/io_ibuf_thread                |
|         4 | thread/innodb/io_log_thread                 |
 |        45 | thread/innodb/log_checkpointer_thread       |
|        46 | thread/innodb/log_flush_notifier_thread     |
|        47 | thread/innodb/log_flusher_thread            |
|        48 | thread/innodb/log_write_notifier_thread     |
|        49 | thread/innodb/log_writer_thread             |
|        50 | thread/innodb/srv_lock_timeout_thread       |
|        51 | thread/innodb/srv_error_monitor_thread      |
|        52 | thread/innodb/srv_monitor_thread            |
......

thread/sql/one_connection 就是用户连接，log_flusher_thread 和 log_writer_thread 就是要找的系统内部线程。

由于测试的用户连接过多，可以写个简单的脚本生成用户线程的绑定语句：

#!/bin/bash

ID_LIST=`mysql -Ne "select concat(THREAD_ID,',') from performance_schema.threads where name = 'thread/sql/one_connection';"`

echo "SET RESOURCE GROUP other_thread FOR" ${ID_LIST%,*}";"

绑定系统内部线程的语句如下：

SET RESOURCE GROUP bg_thread1 FOR 47;
SET RESOURCE GROUP bg_thread2 FOR 49;

执行完毕之后，sysbench 输出的结果如下：

[ 380s ] thds: 48 tps: 7329.74 qps: 29318.27 (r/w/o: 0.00/29318.27/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 390s ] thds: 48 tps: 7385.89 qps: 29543.85 (r/w/o: 0.00/29543.85/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 400s ] thds: 48 tps: 7245.54 qps: 28982.46 (r/w/o: 0.00/28982.46/0.00) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 410s ] thds: 48 tps: 7326.35 qps: 29305.91 (r/w/o: 0.00/29305.91/0.00) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 420s ] thds: 48 tps: 7315.21 qps: 29260.75 (r/w/o: 0.00/29260.75/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 430s ] thds: 48 tps: 7305.01 qps: 29221.02 (r/w/o: 0.00/29221.02/0.00) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 440s ] thds: 48 tps: 7352.34 qps: 29408.77 (r/w/o: 0.00/29408.77/0.00) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00
[ 450s ] thds: 48 tps: 7356.35 qps: 29424.11 (r/w/o: 0.00/29424.11/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 460s ] thds: 48 tps: 7269.48 qps: 29079.13 (r/w/o: 0.00/29079.13/0.00) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 470s ] thds: 48 tps: 7295.22 qps: 29179.68 (r/w/o: 0.00/29179.58/0.10) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 480s ] thds: 48 tps: 7258.87 qps: 29037.09 (r/w/o: 0.00/29036.89/0.20) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 490s ] thds: 48 tps: 7312.73 qps: 29248.64 (r/w/o: 0.00/29248.64/0.00) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 500s ] thds: 48 tps: 7351.52 qps: 29408.80 (r/w/o: 0.00/29408.80/0.00) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 510s ] thds: 48 tps: 8298.04 qps: 33191.17 (r/w/o: 0.00/33190.97/0.20) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 520s ] thds: 48 tps: 8386.27 qps: 33543.08 (r/w/o: 0.00/33543.08/0.00) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 530s ] thds: 48 tps: 8379.00 qps: 33516.81 (r/w/o: 0.00/33516.71/0.10) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 540s ] thds: 48 tps: 8340.53 qps: 33363.82 (r/w/o: 0.00/33363.82/0.00) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 550s ] thds: 48 tps: 8401.08 qps: 33603.44 (r/w/o: 0.00/33603.44/0.00) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 560s ] thds: 48 tps: 8290.48 qps: 33163.43 (r/w/o: 0.00/33163.13/0.30) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 570s ] thds: 48 tps: 8263.53 qps: 33051.53 (r/w/o: 0.00/33051.43/0.10) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00

可以看到从 510s 之后，写入的 QPS 发生了比较明显的变化，提升了约 4000，大概 13%。

而对比一下改之前和改之后的 CPU 使用情况：