2. MySQL基准测试

2.1 为什么需要基准测试

基准测试是唯一方便有效的，可以学习系统在一定的工作负载下会发生什么的办法。
基准测试可以完成以下工作:

• 验证基于系统的一些假设，确认是否符合实际情况
• 重现系统中的某些异常行为，以解决这些异常
• 测试系统当前的运行情况。如果不清楚系统当前的性能，就无法确认某些优化的效果如何。
• 模拟比当前系统更高的负载，以找出压力增加时可能遇到的性能瓶颈。
• 规划未来的业务增长。
• 测试应用适应可变环境的能力。如随机的峰值下，不同服务器，不同数据分布下的处理能力。
• 测试不同的硬件，软件和操作系统配置。
• 证明新采购的设备是否配置正确。

2.2 基准测试的策略

一种是针对整个系统的测试，另外一种是单独测试MySQL。这两种分别称为集成式测试以及单组件式测试。
针对整个系统测试的主要原因如下:

• 测试整个应用系统。因为用户关心的不仅仅是mysql的性能，而是应用的整体性能。
• MySQL并不总是性能瓶颈。
• 只有做整体测试，才能发现缓存的影响。
• 整体更能揭示应用的真实表现。

基于MySQL的单组件测试的原因如下:

• 需要比较不同schema或者查询的性能
• 针对某个具体的问题进行测试
• 测试周期短

如果可能最好基于生产环境的数据快照来做，能模拟真实的环境。但是基于真实数据的测试过于复杂和耗时，通常是通过模拟大量的数据和压力在做测试。

2.2.1　测试何种指标

测试的指标通常如下:

• 吞吐量
  吞吐量指的是单位时间内的事务处理数。常用的测试单位是每秒事务数(TPS)，有时也用每分钟事务数(TPM)。
• 响应时间或者延迟
  这个指标用于测试任务所需的整体时间。测试的时间单位可以是微妙，毫秒，秒等。然后计算出平均响应时间，最小响应时间，最大相应时间和占比。也可以用百分比响应时间来代替最大相应时间。如95%的响应时间都在5毫秒内，则表示95%的任务都能在5ms内完成。
• 并发性
  指的是在任意时间内可以有多少个同时发生的并发请求。
  一个设计良好的应用，可以同时打开成百上千个MySQL连接，但可能同时只有少数的连接在执行查询。所以说一个Web站点可以同时有50000个用户访问，实际上却可能只有10-15个并发请求到MySQL数据库(这里也可以考虑做大流量下的缓存等减少数据库的访问)。
  并发性的测量不同与响应时间和吞吐量，它更像是测试的一种属性，也就是在不同并发下数据库的性能如何。
• 可拓展性
  简单来讲就是给系统增加一倍的工作，在理想情况下能获得两倍的结果，如吞吐量增加了一倍。或者增加了一倍的资源，就可以获得两倍的吞吐量。
  当然性能也要在可接受范围里面。实际上随着压力的增加，吞吐量和性能都会变得越来越差，没法做到理想的效果。

2.3 基准测试方法

常见错误测试：

• 使用真实数据的子集而不是全集。需要处理几百个G的数据，却只用1G来测试。使用了当前的数据进行测试，却希望能模拟未来业务大幅度增长后的情况
• 使用错误的数据分布。使用均匀分布的数据测试，而实际中可能有很多热点区域。
• 在多用户场景中，只做单用户的测试。
• 在单服务器上测试分布式应用。
• 与真实用户行为不匹配。web中用户请求到一个页面之后会阅读一段时间，而不是不停的做请求。
• 反复执行同一个查询。　真实的查询不尽相同，可能会导致缓存命中率降低。
• 没有检查错误。
• 忽略了系统预热(warm up)的过程。
• 使用默认的服务器配置。
• 测试时间太短。

如果采用标准的基准测试，应该确认是否采用了合适的测试方案。如，不要使用TPC-H测试电子商务系统,TPC-H是即席查询和决策执行型的基准测试,不适合用在实时查询的OLTP上。

1. 最好的办法是选择一个有代表性的时间段，比如高峰期的一个小时，记录生产系统上的所有查询。
2. 如果是集成式基准测试，可以记录Web服务器上的HTTP请求，也可以打开MySQL的查询日志(Query Log)。如果要重演这些查询，就要确保创建多线程在并行执行，并且对日志中的每个连接都应该创建单独的线程。

即使不需要创建专用的基准测试，最好也详细写下测试规划。毕竟测试可能会多次运行。测试规划应记录测试数据，系统配置的步骤，如何测量和分析结果，以及预热的方案等。

2.3.2 基准测试应该运行多长时间

如果需要测试系统在稳定状态下的性能，那么当然需要在稳定状态下测试观察。
而如果有大量的数据和内存，要达到稳定状态需要非常长的时间。大部分系统都有一些应付突发情况的余量，能够吸收性能尖峰，将一些工作延迟到高峰期之后执行。当对机器持续加压后，余量会被耗尽。系统的短期尖峰也就无妨维持原来的高性能。

一个常见的错误是只进行一系列短期的测试，比如每次60秒。如果没有时间去做基准测试，那么有时候可以去相信别人的测试结果，总比自己得出了错误的结论来的好。

2.3.3 获取系统性能和状态

在做基准测试时，需要尽可能多的收集被测试系统的信息，最好为每轮测试建立一个单独的子目录，将测试结果，配置文件，测试指标，脚本和相关说明等都保存在其中。需要记录的数据包括系统状态和性能指标，　诸如cpu使用率，磁盘I/O，　网络流量统计，Show global status 计数器等等。
以下脚本可以简单的收集mysql的状态。

#!/bin/sh
PASSWORD=youpassword
USER=root
INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=~/Development/mysql/benchmarks/running
mysql -u $USER --password=$PASSWORD -e 'show global variables' >> mysql-variables
while test -e $RUNFILE;do
    file=$(date +%F_%I)
    sleep=$(date +%s.%N|awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
    sleep $sleep
    ts="$(date +"TS %s.%N %F %T")"
    loadavg="$(/usr/bin/uptime)"
    echo "$ts $loadavg" >> $PREFIX-${file}-status
    mysql -u $USER --password=$PASSWORD -e 'show global status' >> $PREFIX-${file}-status &
    echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
    mysql -u $USER --password=$PASSWORD -e 'show engine innodb status\G' >> $PREFIX-${file}-innodbstatus &
    echo "$ts, $loadavg" >> $PREFIX-${file}-processlist
    mysql -u $USER --password=$PASSWORD -e 'show full processlist\G' >> $PREFIX-${file}-processlist &
    echo $ts
done

echo Exiting because $RUNFILE does not exist.

以下脚本是分析status文件的数据

#!/bin/bash

# This script converts SHOW GLOBAL STATUS into a tabulated format, one line
# per sample in the input, with the metrics divided by the time elapsed
# between samples.

awk '
    BEGIN {
        printf "#ts date  time  load  QPS";
        fmt = " %.2f";
    }
    /^TS/ {# the timestamp lines begin with TS.
        ts = substr($2, 1, index($2, ".") -1);
        load = NF-2;
        diff = ts-prev_ts;
        prev_ts= ts;
        printf "\n%s %s %s %s", ts, $3, $4, substr($load, 1, length($load)-1);
    }
    /Queries/ {
        printf fmt, ($2-Quries)/diff;
        Queries=$2
    }
    ' "$@"

2.3.6　绘图

有的问题没法通过平均值看出来，这时候可以用画图的方式可以更容易查看

2.4 基准测试工具

2.4.1 集成式测试工具

• ab
  ab是一个apache http 服务器基准测试工具。可以测试服务器每秒最多可以处理多少个请求。
  只能针对单个URL进行尽可能快的压力测试
• http_load
  这个工具概念上和ab类似，但比ab灵活，可以通过一个输入文件提供多个URL，http_load 在这些url中随机选择进行测试。也可以定制，按照时间比率进行测试。
• JMeter
  功能更多，更复杂。

2.4.2 单组件式测试工具

• mysqlslap
  可以模拟服务器的负载，病输出计时信息。
  测试时可以执行并发连接数，并指定SQL语句，如果没有指定，会自动生成查询schema的select语句
• Percona's TPCC-MySQL Tool
  这是一个类似TPC-C的基准测试工具，在评估大压力下的MySQL时可以用，简单的可以用sysbench替代。
• sysbench
  一款多线程系统压测工具。他可以根据影响数据库服务器性能的各种因素来评估系统的性能。例如可以用来测试文件I/O, 操作系统调度器，内存分配和传输速度，POSIX线程，以及数据库服务器等。sysbench 支持lua脚本语言。
  支持MySQL,操作系统和硬件测试。
• MySQL的benchmark()函数
  可以测试某些特定操作的执行速度。参数是需要执行的次数和表达式。

MariaDB [test]> set @input:='hello world';
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select benchmark(100000,md5(@input));
+-------------------------------+
| benchmark(100000,md5(@input)) |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.017 sec)

MariaDB [test]> select benchmark(100000,sha1(@input));
+--------------------------------+
| benchmark(100000,sha1(@input)) |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.021 sec)

可以看出md5比sha1快上一些
执行后的返回值永远是0，可以通过客户端返回时间来判断执行的时间。
这个函数只是返回服务器执行表达式的时间，而不会涉及分析和优化的开销。
而且表达式必须要这样包含用户定义的变量，否则多次执行同样的表达式会由于命中缓存而影响结果。

2.5 基准测试案例

http_load

5个并发执行10s
http_load -parallel 5 -seconds 10 urls.txt
结果做了简单的统计:
多少连接数，每次连接的耗时等等

(base) ➜  tmp http_load -rate 5 -seconds 10 urls.txt
49 fetches, 1 max parallel, 69776 bytes, in 10.0133 seconds
1424 mean bytes/connection
4.89351 fetches/sec, 6968.36 bytes/sec
msecs/connect: 32.4432 mean, 33.151 max, 27.304 min
msecs/first-response: 19.009 mean, 22.923 max, 12.259 min
HTTP response codes:
  code 200 -- 49

也可以指定　-rate N　执行每秒N个请求。
http_load -rate 5 -seconds 10 urls.txt

2.5.3 sysbench

sysbench 的CPU基准测试

这个测试使用64位整数，测试计算素数直到某个最大值所需要的时间。

sysbench --test=cpu --cpu-max-prime=20000 run

WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.15 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Prime numbers limit: 20000

Initializing worker threads...

Threads started!

CPU speed:
    events per second:   553.57

General statistics:
    total time:                          10.0012s
    total number of events:              5537

Latency (ms):
         min:                                    1.77
         avg:                                    1.81
         max:                                    2.94
         95th percentile:                        1.89
         sum:                                 9999.88

Threads fairness:
    events (avg/stddev):           5537.0000/0.00
    execution time (avg/stddev):   9.9999/0.00

sysbench的文件I/O基准测试

文件I/O(fileio) 基准测试可以测试系统在不同I/O负载下的性能。这对于比较不同的磁盘驱动器，不同的RAID卡，不同的RAID模式，都很有帮助。
可以根据测试结果来调整I/O子系统。
测试的第一步是准备阶段，生成测试用到的数据文件，生成的数据文件至少要比内存大。如果生成的数据能完全放入内存中，则操作系统缓存大部分的数据，导致测试结果无法体现I/O密集型的工作负载。
首先通过下面的命令创建一个数据集:
sysbench --test=fileio --file-total-size=150G prepare
第二部分就是运行阶段
测试10G文件的顺序写和随机写的能力
sysbench fileio --file-total-size=10G --file-test-mode=rndrw --time=300 run
分别展示了文件操作数，吞吐量，统计，延迟等等信息，

sysbench 1.0.15 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Extra file open flags: (none)
128 files, 80MiB each
10GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Initializing worker threads...

Threads started!


File operations:
    reads/s:                      0.00
    writes/s:                     2190.83
    fsyncs/s:                     2804.36

Throughput:
    read, MiB/s:                  0.00
    written, MiB/s:               34.23

General statistics:
    total time:                          10.0359s
    total number of events:              50033

Latency (ms):
         min:                                    0.00
         avg:                                    0.20
         max:                                    5.88
         95th percentile:                        0.67
         sum:                                 9918.60

Threads fairness:
    events (avg/stddev):           50033.0000/0.00
    execution time (avg/stddev):   9.9186/0.00

sysbench fileio --file-total-size=10G --file-test-mode=seqwr run

sysbench 1.0.15 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Extra file open flags: (none)
128 files, 80MiB each
10GiB total file size
Block size 16KiB
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing sequential write (creation) test
Initializing worker threads...

Threads started!


File operations:
    reads/s:                      0.00
    writes/s:                     9191.44
    fsyncs/s:                     11776.73

Throughput:
    read, MiB/s:                  0.00
    written, MiB/s:               143.62

General statistics:
    total time:                          10.0080s
    total number of events:              209749

Latency (ms):
         min:                                    0.01
         avg:                                    0.05
         max:                                    7.18
         95th percentile:                        0.08
         sum:                                 9932.04

Threads fairness:
    events (avg/stddev):           209749.0000/0.00
    execution time (avg/stddev):   9.9320/0.00

可以看出，顺序读写的速度大概是随机读写的5倍左右。
测试完毕后通过sysbench fileio cleanup清除测试文件。

sysbench的OLTP基准测试

下面是模拟一张超过百万行记录的事务处理系统的工作负载。

sysbench /usr/share/sysbench/oltp_insert.lua --db-driver=mysql --threads=8 --mysql-user=root --mysql-password=abigail prepare

执行run命令
返回中包含以下信息

• 总的事务数
• 每秒事务数
• 时间统计信息
• 线程公平性统计信息(thread-fairness),用于表示模拟负载的公平性

(base) ➜  tmp sysbench /usr/share/sysbench/oltp_insert.lua --table-size=1000000 --db-driver=mysql --threads=8 --mysql-user=root --mysql-password=abigail run
sysbench 1.0.15 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 8
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            0
        write:                           60621
        other:                           0
        total:                           60621
    transactions:                        60621  (6057.43 per sec.)
    queries:                             60621  (6057.43 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0024s
    total number of events:              60621

Latency (ms):
         min:                                    0.50
         avg:                                    1.32
         max:                                   11.65
         95th percentile:                        2.03
         sum:                                79719.63

Threads fairness:
    events (avg/stddev):           7577.6250/18.39
    execution time (avg/stddev):   9.9650/0.00

sysbench的其他特性

• 内存(memory)
• 线程(thread)
• 互斥锁(mutex)
  测试互斥锁的性能，方式是模拟所有线程在同一时刻并发运行，并都短暂请求互斥锁。
• 顺序写(seqwr)
  测试顺序写的性能。　这对于测试系统的实际性能瓶颈很重要，可以用来测试RAID控制器的高速缓存的性能状况，如果测试结果异常则需要引起重视。比如，如果RAID控制器写缓存没有电池保护，而磁盘压力达到了3000次请求/秒,就可能是不太安全的。