老板让你做一个 MySQL 的性能基准测试,测来测去发现明明机器配置很高,但 tps 就是上不去,为什么?

首先我们很容易想到的就是 InnoDB 缓冲池设置的不够大、 redo log 太小、数据没有充分预热、 sysbench 线程数开的太少... 这些是很常见的原因,今天我们来看一些不那么显而易见的情况。

以下案例的硬件配置为:64core、256Gmemory、raid 10 15K 机械盘。

网络瓶颈

一次压测结果是这样的:

sysbench oltp_read_write --mysql-host=10.18x.xx.104 --mysql-port=3308  \
--mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --tables=10 \
--table-size=10000000 --report-interval=5 --threads=200 --time=600 run

##结果:
SQL statistics:
    queries performed:
        read:                            4682958
        write:                           1337988
        other:                           668994
        total:                           6689940
    transactions:                        334497 (2783.82 per sec.)
    queries:                             6689940 (55676.32 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

结果 tps 只有 2800,显然对不起这么高的硬件,这时候就得观察负载了,一般最明显的一点就是 CPU 使用率低,比如这个案例在我的环境上 CPU 使用率只有36%,而网卡流量很高达到 124MB/s ,差不多就是千兆网卡的极限了(1000Mb/s / 8):

这时候就得意识到“我是不是用错网卡了”?为啥这么说呢,因为一般都会配两块网卡分别访问内外网,快速查看网络接口:ifconfig -a|grep -B 1 inet。立马就看到确实是双网卡,我们压测用的是em3,还有另外一个网络接口 vlan2 :

然后再确认下带宽: ethtool {dev_name} |grep Speed。em3 是千兆带宽,压测时 124MB/s 的流量已经打满了:

所以在sysbench压测时换成 vlan2 万兆网络接口后(即修改 --mysql-host=10.0.xx.104),轻松到1万。如果实在没有万兆网卡怎么办?那就在本地使用回环接口进行测试吧。下图是使用3个网口测试的网络流量图,明显可以看出千兆网口的网络瓶颈:

SSL

MySQL8.0 或者 MySQL5.7 企业版压测时会遇到一个坑:默认开启 SSL,压测结果 tps 只有3700:

sysbench oltp_read_write --mysql-host=10.18x.xx.104 --mysql-port=3308  \
--mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --tables=10 \
--table-size=10000000 --report-interval=5 --threads=200 --time=600 run

##结果:
SQL statistics:
    queries performed:
        read:                            6303388
        write:                           1800968
        other:                           900484
        total:                           9004840
    transactions:                        450242 (3747.71 per sec.)
    queries:                             9004840 (74954.25 per sec.)
Latency (ms):
         min:                                    6.35
         avg:                                   53.31
         max:                                  542.71
         95th percentile:                      104.84
         sum:                             24004566.42
         

和前面说的一样,观察系统负载,发现 CPU 使用率已经用到了 80% ,但是 CPU system 时间反常的高,再去看压测结果 95% 的响应时间也很高需要 100 多毫秒,正是因为需要消耗大量的系统资源进行加密连接:

解决办法就是配置文件写入 skip-ssl 重启 MySQL。其实 sysbench 有个参数 --mysql-ssl[=on|off],看说明只要设置为 off 就可以关闭 ssl 连接,但实测并没有用,通过 select * from status_by_thread where VARIABLE_NAME like '%ssl%';查看连接还是开启 ssl 的,对 ssl 机制比较懵逼,欢迎有了解的大佬留言。

sort_buffer_size

sort_buffer_size 会影响 sysbench 性能吗?官方文档有这样的描述:

也许你会觉得问题不大,但实际看下面这组测试结果:11000tps VS 930tps

## sort_buffer_size=31M
SQL statistics:

    queries performed:
        read:                            16358846
        write:                           4673956
        other:                           2336978
        total:                           23369780
    transactions:                        1168489 (11678.75 per sec.)
    queries:                             23369780 (233574.94 per sec.)

## sort_buffer_size=32M
SQL statistics:
    queries performed:
        read:                            392182
        write:                           112052
        other:                           56026
        total:                           560260
    transactions:                        28013  (930.07 per sec.)
    queries:                             560260 (18601.38 per sec.)

事实就是这么不可思议,当 sort_buffer_size 达到阈值32M后(我测试的阈值和文档给出的阈值256K、2M不一样),内存分配方式发生改变,内存分配效率变低, CPU system 时间剧增:

细说一下内存分配方式变化为什么会引起这个结果,参考:【技术分享 | MySQL 内存管理初探】

malloc() 是 C 标准库提供的内存分配函数,对应到系统调用上,有两种实现方式,即 brk() 和 mmap()。

  1. brk 方式
    对于小块内存(<128K),C 标准库使用 brk() 来分配。也就是通过移动堆顶的位置来分配内存。这些内存释放后并不会立刻归还系统,而是被缓存起来,重复使用。

优缺点:brk() 方式可以减少缺页异常的发生,提高内存访问效率。不过,由于这些内存没有归还系统,所以在内存工作繁忙时,频繁的内存分配和释放会造成内存碎片。

  1. mmap 匿名映射方式
    对于大块内存(>128K),C 标准库使用 mmap() 来分配,也就是在文件映射段找一块空闲内存分配出去。mmap() 方式分配的内存,会在释放时直接归还系统,所以每次 mmap 都会发生缺页异常。

优缺点:mmap() 方式可以将内存及时返回给系统,避免 OOM。但是工作繁忙时,频繁的内存分配会导致大量的缺页异常,使内核的管理负担增大。这也是 malloc 只对大块内存使用 mmap 的原因。

所谓的缺页异常是指进程申请内存后,只分配了虚拟内存。这些所申请的虚拟内存,只有在首次访问时才会分配真正的物理内存,也就是通过缺页异常进入内核中,再由内核来分配物理内存(本质就是建立虚拟内存与物理内存的地址映射)。

brk() 方式申请的堆内存由于释放内存后并不会归还给系统,所以下次申请内存时,并不需要发生缺页异常。

mmap() 方式申请的动态内存会在释放内存后直接归还系统,所以下次申请内存时,会发生缺页异常(增加内核态 CPU 开销)。

总结

当压
测结果不乐观,第一时间去看 CPU 使用率,只要总使用率低,或者 iowait、system 高,都是异常情况,需要去排查原因。my.cnf 规范模板可以解决大部分的压测结果异常的问题,另一方面则需要我们掌握基本的分析方法,再配合一些过往经验,就能测出理想的数据了。

03-05 22:34