一、测试环境准备

操作系统：centos7.5
处理器：Intel 8核 Current Speed: 2000 MHz， Max Speed: 2000 MHz，三级缓存
内存：16G
磁盘：100G SSD
数据库：mysql 5.6.28
sysbench：1.0.18

二、测试工具

1、sysbench

安装文档： https://github.com/akopytov/sysbench#installing-from-binary-packages

1
2
3

# centos7.5使用官方镜像安装
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

帮助：

[root@10-10-135-172 benchmark]# sysbench --help

[root@10-10-135-172 benchmark]# sysbench fileio help
sysbench 1.0.18 (using bundled LuaJIT 2.1.0-beta2)

fileio options:
  --file-num=N                  number of files to create [128]
  --file-block-size=N           block size to use in all IO operations [16384]
  --file-total-size=SIZE        total size of files to create [2G]
  --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
  --file-io-mode=STRING         file operations mode {sync,async,mmap} [sync]
  --file-async-backlog=N        number of asynchronous operatons to queue per thread [128]
  --file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
  --file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100]
  --file-fsync-all[=on|off]     do fsync() after each write operation [off]
  --file-fsync-end[=on|off]     do fsync() at the end of test [on]
  --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]
  --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0]
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]

2、tpcc-mysql

github： https://github.com/Percona-Lab/tpcc-mysql

mkdir -p /var/lib/mysql/benchmark
cd /var/lib/mysql/benchmark
wget https://github.com/Percona-Lab/tpcc-mysql/archive/master.zip -O tpcc-mysql.zip
unzip -d ./ tpcc-mysql.zip
cd /var/lib/mysql/benchmark/tpcc-mysql-master/src
yum install mysql-devel
make

编译完成后，生成 tpcc_load tpcc_start：

三、测试目标

处理器性能
内存性能
磁盘顺序读写，随机读写性能
mysql 吞吐量，响应时间

四、测试步骤

1、准备获取系统性能和状态的脚本

vi /root/benchmark/1-collect-system-status.sh

#!/bin/sh

mkdir -p status_data

INTERVAL=5
PREFIX=./status_data/$INTERVAL-sec-status
RUNFILE=/root/benchmark/running

mysql -uroot -p123456 -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
    tfile=$(date +%F_%I)
    sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
    sleep $sleep
    ts="$(date +"TS %s.%N %F %T")"
    loadavg="$(uptime)"
    echo "$ts $loadavg" >> $PREFIX-${tfile}-status
    mysql -uroot -p123456 -e 'SHOW GLOBAL STATUS' >> $PREFIX-${tfile}-status &
    echo "$ts $loadavg" >> $PREFIX-${tfile}-innodbstatus
    mysql -uroot -p123456 -e 'SHOW GLOBAL STATUS\G' >> $PREFIX-${tfile}-innodbstatus &
    echo "$ts $loadavg" >> $PREFIX-${tfile}-processlist
    mysql -uroot -p123456 -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${tfile}-processlist &
    echo $ts
done
echo Exiting because $RUNFILE does not exist.

分析收集到的数据

vi /root/benchmark/2-system-status-analyzer.sh

#!/bin/bash

awk '
    BEGIN {
        printf "#ts date time load QPS";
        fmt = " %.2f";
    }
    /^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-Queries)/diff;
        Queries=$2
    }
' "$@"

用法示例

1	sh 2-system-status-analyzer.sh status_data/5-sec-status-2019-11-19_10-status

2、使用sysbench服务器性能测试

1)、cpu测试

计算20000个素数。8核cpu，分别测试 1，4，8，12 线程时的 CPU表现

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

1	sysbench --test=cpu --threads=4 --cpu-max-prime=20000 run

1	sysbench --test=cpu --threads=8 --cpu-max-prime=20000 run

结论：随着线程数增加，计算能力线性增加；

1	sysbench --test=cpu --threads=12 --cpu-max-prime=20000 run

结论：当线程数超过cpu核数之后，随着线程数增加，计算能力不再增加，甚至略微下降，这是因为线程上下文切换以后的时间损耗；

2)、文件I/O测试

① 准备30G的数据

1	sysbench --test=fileio --file-total-size=30G prepare

② 运行阶段

seqwr：顺序写入

1	sysbench --test=fileio --file-test-mode=seqwr --file-total-size=30G --time=60 run

结论：可以看到，顺序写的过程中 CPU占用很低，但是 buff、cache、block out、in、cs(上下文切换)都很高，这是因为 linux 写磁盘用了页缓存和 DMA。

seqrewr：顺序重写

1	sysbench --test=fileio --file-test-mode=seqrewr --file-total-size=30G --time=60 run

seqrd：顺序读取

1	sysbench --test=fileio --file-test-mode=seqrd --file-total-size=30G --time=60 run

rndrd：随机读取

1	sysbench --test=fileio --file-test-mode=rndrd --file-total-size=30G --time=60 run

结论： SSD是没有寻道时间的，但是随机读（46.95MiB/s）仍然比顺序读（184.46MiB/s）要慢很多

rndwr：随机写入

1	sysbench --test=fileio --file-test-mode=rndwr --file-total-size=30G --time=60 run

随机写（26.25MiB/s）更慢了，顺序写的速度有138.38MiB/s

rndrw：混合随机读/写

1	sysbench --test=fileio --file-test-mode=rndrw --file-total-size=30G --time=60 run

混合随机读写的速度更是龟速，然而我们平时业务场景中使用最多的就是这种情况，所以一定要注意，能顺序写的，一定不随机写

③ 清除数据

sysbench –test=fileio –file-total-size=30G cleanup

3)、内存测试

测试8K顺序分配

1	sysbench --test=memory --memory-access-mode=seq --memory-block-size=8K --memory-total-size=100G --threads=12 --events=10000 run

结论：可以看到 CPU 占用极高，内存写入速度为 10138.32 MiB / sec

测试8K随机分配

1	sysbench --test=memory --memory-access-mode=rnd --memory-block-size=8K --memory-total-size=100G --threads=12 --events=10000 run

结论：cpu占用极高，随机分配速度为 1381.41 MiB / sec，只有顺序分配的 1/7 左右，内存都有顺序写都有差距。

3、sysbench对mysql进行OLTP测试

1	sysbench /usr/share/sysbench/oltp_read_write.lua help

1) 、新建数据库 sbtest

1	mysqladmin create sbtest -uroot -p

2) 、运行状态收集脚本

1	sh /root/benchmark/1-collect-system-status.sh

3)、准备1百万条数据

可以在/usr/share/sysbench/中找到需要使用的lua脚本：

# 开始插入数据
sysbench /usr/share/sysbench/oltp_read_write.lua --threads=64 --time=600 --histogram=on --mysql-host=10.10.135.172 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table-size=10000000 prepare

# 关键参数说明：
--threads=64           测试时使用的线程数
--time=600             测试时间
--histogram=on         在报告中是否输出关于延迟的直方图，建议开启
--table_size=10000000  数据表的大小

4)、运行压测程序

1	sysbench /usr/share/sysbench/oltp_read_write.lua --threads=64 --time=600 --histogram=on --mysql-host=10.10.135.172 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --table-size=10000000 run

总事务数：820388
每秒事务数：1367.22 per sec
总查询数：16407760
每秒查询数：27344.47 per sec

请求分布直方图

[root@10-10-135-172 benchmark]#  sysbench /usr/share/sysbench/oltp_read_write.lua --threads=64 --time=600 --histogram=on --mysql-host=10.10.135.172 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --table-size=10000000 run
sysbench 1.0.18 (using bundled LuaJIT 2.1.0-beta2)

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


Initializing worker threads...

Threads started!

 Latency histogram (values are in milliseconds)
       value  ------------- distribution ------------- count
       4.329 |                                         64
       4.407 |*****                                    1030
       4.487 |****************                         3445
       4.569 |**************************               5587
       4.652 |****************************             5867
       4.737 |*************************                5250
       4.823 |********************                     4190
       4.910 |****************                         3476
       4.999 |**************                           2922
       5.090 |************                             2605
       5.183 |***********                              2329
       5.277 |*********                                1991
       5.373 |*********                                1813
       5.470 |********                                 1600
       5.570 |*******                                  1476
       5.671 |******                                   1279
       5.774 |******                                   1178
       5.879 |*****                                    1043
       5.986 |*****                                    974
       6.095 |****                                     924
       6.205 |****                                     843
       6.318 |****                                     768
       6.433 |****                                     786
       6.550 |****                                     839
       6.669 |****                                     943
       6.790 |*****                                    1012
       6.913 |*****                                    1126
       7.039 |******                                   1171
       7.167 |******                                   1310
       7.297 |******                                   1305
       7.430 |******                                   1291
       7.565 |*****                                    1167
       7.702 |*****                                    1072
       7.842 |*****                                    1066
       7.985 |*****                                    1051
       8.130 |*****                                    1102
       8.277 |*****                                    1081
       8.428 |*****                                    1120
       8.581 |*****                                    1129
       8.737 |******                                   1244
       8.895 |*******                                  1444
       9.057 |********                                 1692
       9.222 |*********                                1984
       9.389 |**********                               2161
       9.560 |**********                               2057
       9.734 |*********                                1944
       9.910 |*********                                1930
      10.090 |********                                 1773
      10.274 |********                                 1753
      10.460 |********                                 1698
      10.651 |********                                 1605
      10.844 |*******                                  1553
      11.041 |********                                 1601
      11.242 |********                                 1636
      11.446 |********                                 1649
      11.654 |********                                 1667
      11.866 |********                                 1673
      12.081 |********                                 1733
      12.301 |*********                                1821
      12.524 |*********                                1838
      12.752 |*********                                1844
      12.984 |**********                               2031
      13.219 |*********                                1978
      13.460 |**********                               2206
      13.704 |************                             2449
      13.953 |*************                            2681
      14.207 |*************                            2675
      14.465 |*************                            2744
      14.728 |*************                            2694
      14.995 |*************                            2667
      15.268 |************                             2652
      15.545 |************                             2650
      15.828 |*************                            2671
      16.115 |************                             2644
      16.408 |*************                            2757
      16.706 |*************                            2760
      17.010 |*************                            2845
      17.319 |**************                           2933
      17.633 |***************                          3217
      17.954 |***************                          3105
      18.280 |****************                         3294
      18.612 |*****************                        3587
      18.950 |*****************                        3704
      19.295 |******************                       3845
      19.645 |******************                       3763
      20.002 |******************                       3743
      20.366 |******************                       3852
      20.736 |******************                       3763
      21.112 |******************                       3891
      21.496 |*******************                      4076
      21.886 |*******************                      4108
      22.284 |********************                     4191
      22.689 |*********************                    4400
      23.101 |**********************                   4702
      23.521 |***********************                  4863
      23.948 |************************                 5072
      24.384 |************************                 5043
      24.827 |*************************                5224
      25.278 |************************                 5196
      25.737 |*************************                5285
      26.205 |**************************               5442
      26.681 |**************************               5452
      27.165 |***************************              5727
      27.659 |****************************             5867
      28.162 |*****************************            6097
      28.673 |******************************           6367
      29.194 |******************************           6414
      29.725 |******************************           6441
      30.265 |*******************************          6512
      30.815 |*******************************          6479
      31.375 |********************************         6828
      31.945 |********************************         6876
      32.525 |**********************************       7128
      33.116 |**********************************       7241
      33.718 |***********************************      7372
      34.330 |************************************     7548
      34.954 |************************************     7653
      35.589 |*************************************    7788
      36.236 |*************************************    7808
      36.894 |*************************************    7910
      37.565 |**************************************   8106
      38.247 |***************************************  8177
      38.942 |***************************************  8311
      39.650 |**************************************** 8451
      40.370 |**************************************   8058
      41.104 |***************************************  8309
      41.851 |***************************************  8260
      42.611 |**************************************** 8481
      43.385 |**************************************** 8493
      44.173 |**************************************** 8478
      44.976 |**************************************** 8394
      45.793 |***************************************  8369
      46.625 |**************************************** 8398
      47.472 |**************************************** 8459
      48.335 |**************************************** 8455
      49.213 |**************************************** 8446
      50.107 |***************************************  8245
      51.018 |***************************************  8215
      51.945 |***************************************  8215
      52.889 |**************************************   8087
      53.850 |**************************************   8119
      54.828 |**************************************   8043
      55.824 |*************************************    7959
      56.839 |**************************************   8043
      57.871 |************************************     7746
      58.923 |************************************     7650
      59.993 |***********************************      7522
      61.083 |***********************************      7535
      62.193 |**********************************       7250
      63.323 |**********************************       7254
      64.474 |**********************************       7118
      65.645 |*********************************        6983
      66.838 |********************************         6868
      68.053 |********************************         6890
      69.289 |*******************************          6688
      70.548 |*******************************          6530
      71.830 |******************************           6437
      73.135 |*****************************            6127
      74.464 |******************************           6278
      75.817 |****************************             5952
      77.194 |***************************              5767
      78.597 |**************************               5581
      80.025 |**************************               5522
      81.479 |*************************                5374
      82.959 |*************************                5217
      84.467 |***********************                  4957
      86.002 |***********************                  4947
      87.564 |**********************                   4767
      89.155 |**********************                   4673
      90.775 |*********************                    4499
      92.424 |*********************                    4358
      94.104 |********************                     4230
      95.814 |*******************                      4021
      97.555 |******************                       3871
      99.327 |******************                       3750
     101.132 |*****************                        3665
     102.969 |****************                         3465
     104.840 |****************                         3322
     106.745 |***************                          3286
     108.685 |**************                           3019
     110.659 |**************                           2963
     112.670 |*************                            2707
     114.717 |*************                            2712
     116.802 |************                             2528
     118.924 |***********                              2379
     121.085 |***********                              2269
     123.285 |**********                               2138
     125.525 |**********                               2105
     127.805 |*********                                1861
     130.128 |*********                                1820
     132.492 |********                                 1735
     134.899 |*******                                  1575
     137.350 |*******                                  1502
     139.846 |*******                                  1412
     142.387 |******                                   1302
     144.974 |******                                   1231
     147.608 |*****                                    1056
     150.290 |*****                                    1015
     153.021 |****                                     927
     155.801 |****                                     879
     158.632 |****                                     815
     161.514 |***                                      685
     164.449 |***                                      696
     167.437 |***                                      613
     170.479 |***                                      591
     173.577 |**                                       499
     176.731 |**                                       457
     179.942 |**                                       447
     183.211 |**                                       360
     186.540 |**                                       352
     189.929 |*                                        298
     193.380 |*                                        304
     196.894 |*                                        224
     200.472 |*                                        208
     204.114 |*                                        204
     207.823 |*                                        173
     211.599 |*                                        155
     215.443 |*                                        113
     219.358 |*                                        129
     223.344 |                                         106
     227.402 |                                         82
     231.534 |                                         67
     235.740 |                                         66
     240.024 |                                         43
     244.385 |                                         53
     248.825 |                                         30
     253.346 |                                         31
     257.950 |                                         26
     262.636 |                                         19
     267.408 |                                         25
     272.267 |                                         12
     277.214 |                                         11
     282.251 |                                         11
     287.379 |                                         11
     292.601 |                                         5
     297.917 |                                         11
     303.330 |                                         5
     308.842 |                                         6
     314.453 |                                         14
     320.167 |                                         10
     325.984 |                                         16
     331.907 |                                         11
     337.938 |                                         2
     344.078 |                                         8
     350.330 |                                         10
     356.695 |                                         11
     363.176 |                                         10
     369.775 |                                         15
     376.494 |                                         12
     383.334 |                                         14
     390.299 |                                         10
     397.391 |                                         5
     404.611 |                                         11
     411.963 |                                         2
     419.448 |                                         11
     427.069 |                                         7
     434.829 |                                         6
     442.730 |                                         6
     450.774 |                                         5
     458.964 |                                         2
     475.794 |                                         1
     484.439 |                                         2
     493.242 |                                         1
     559.501 |                                         1
 
SQL statistics:
    queries performed:
        read:                            11485432
        write:                           3281552
        other:                           1640776
        total:                           16407760
    transactions:                        820388 (1367.22 per sec.)
    queries:                             16407760 (27344.47 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          600.0372s
    total number of events:              820388

Latency (ms):
         min:                                    4.30
         avg:                                   46.80
         max:                                  556.16
         95th percentile:                      112.67
         sum:                             38398062.70

Threads fairness:
    events (avg/stddev):           12818.5625/107.50
    execution time (avg/stddev):   599.9697/0.01

[root@10-10-135-172 benchmark]#

5)、清理数据

1	sysbench /usr/share/sysbench/oltp_read_write.lua --threads=64 --time=600 --histogram=on --mysql-host=10.10.135.172 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --table-size=10000000 cleanup

6)、分析收集到的状态

$ sh 2-system-status-analyzer.sh status_data/5-sec-status-2019-11-19_06-status > status.txt
$ vi status.txt
unix时间戳    时间            系统负载 QPS(数据库)
1574160560 18:49:20 18:49:20 51.53 28179.80
1574160565 18:49:25 18:49:25 51.80 26713.20
1574160570 18:49:30 18:49:30 50.86 27784.60
1574160575 18:49:35 18:49:35 51.19 26912.40
1574160580 18:49:40 18:49:40 52.30 27048.20
1574160585 18:49:45 18:49:45 52.99 26843.00
1574160590 18:49:50 18:49:50 53.87 27478.80
1574160595 18:49:55 18:49:55 54.61 28724.60
1574160600 18:50:00 18:50:00 55.68 27960.00
1574160605 18:50:05 18:50:05 55.46 25874.40
1574160610 18:50:10 18:50:10 56.07 25936.40
1574160615 18:50:15 18:50:15 52.54 25914.20
1574160620 18:50:20 18:50:20 52.66 27237.60
1574160625 18:50:25 18:50:25 52.92 27507.20
1574160630 18:50:30 18:50:30 53.89 26005.40
1574160635 18:50:35 18:50:35 55.02 26745.00
1574160640 18:50:40 18:50:40 54.86 25643.80
1574160645 18:50:45 18:50:45 55.75 24751.20
1574160650 18:50:50 18:50:50 53.69 27608.80
1574160655 18:50:55 18:50:55 52.51 26590.20
1574160660 18:51:00 18:51:00 51.83 27411.60
1574160665 18:51:05 18:51:05 49.68 28504.20
1574160670 18:51:10 18:51:10 50.27 20451.00
1574160675 18:51:15 18:51:15 46.24 1.40
1574160680 18:51:20 18:51:20 42.62 2.20
1574160685 18:51:25 18:51:25 39.21 1.40
1574160690 18:51:30 18:51:30 36.07 1.80
1574160695 18:51:35 18:51:35 33.18 2.40
1574160700 18:51:40 18:51:40 30.52 1.00

$ cat status_data/5-sec-status-2019-11-19_06-status | grep 'TS ' > load_status.txt
$ vi load_status.txt

TS 1574160295.047916883 18:44:55  18:44:55 up 1 day,  2:22,  5 users,  load average: 52.13, 24.94, 9.96
TS 1574160300.059352736 18:45:00  18:45:00 up 1 day,  2:22,  5 users,  load average: 53.08, 25.59, 10.25
TS 1574160305.042101201 18:45:05  18:45:05 up 1 day,  2:22,  5 users,  load average: 50.91, 25.60, 10.33
TS 1574160310.052530257 18:45:10  18:45:10 up 1 day,  2:22,  5 users,  load average: 51.72, 26.19, 10.60
TS 1574160315.046490819 18:45:15  18:45:15 up 1 day,  2:22,  5 users,  load average: 52.78, 26.83, 10.89
TS 1574160320.008893450 18:45:20  18:45:20 up 1 day,  2:22,  5 users,  load average: 53.68, 27.45, 11.18
TS 1574160325.203247981 18:45:25  18:45:25 up 1 day,  2:22,  5 users,  load average: 54.67, 28.09, 11.47
TS 1574160330.118228229 18:45:30  18:45:30 up 1 day,  2:22,  5 users,  load average: 55.57, 28.72, 11.77
TS 1574160335.016869072 18:45:35  18:45:35 up 1 day,  2:22,  5 users,  load average: 55.61, 29.17, 12.00
TS 1574160340.042057908 18:45:40  18:45:40 up 1 day,  2:22,  5 users,  load average: 54.84, 29.45, 12.19
TS 1574160345.056853963 18:45:45  18:45:45 up 1 day,  2:23,  5 users,  load average: 55.65, 30.04, 12.47
TS 1574160350.059495691 18:45:50  18:45:50 up 1 day,  2:23,  5 users,  load average: 55.84, 30.50, 12.72
TS 1574160355.238934528 18:45:55  18:45:55 up 1 day,  2:23,  5 users,  load average: 56.65, 31.09, 13.00
TS 1574160360.115909774 18:46:00  18:46:00 up 1 day,  2:23,  5 users,  load average: 56.76, 31.54, 13.24
TS 1574160365.067470620 18:46:05  18:46:05 up 1 day,  2:23,  5 users,  load average: 57.34, 32.08, 13.52
TS 1574160370.111400548 18:46:10  18:46:10 up 1 day,  2:23,  5 users,  load average: 55.39, 32.09, 13.62
TS 1574160375.074730081 18:46:15  18:46:15 up 1 day,  2:23,  5 users,  load average: 53.76, 32.14, 13.74
TS 1574160380.210612654 18:46:20  18:46:20 up 1 day,  2:23,  5 users,  load average: 54.34, 32.62, 13.99
TS 1574160385.102457806 18:46:25  18:46:25 up 1 day,  2:23,  5 users,  load average: 50.87, 32.26, 13.97
TS 1574160390.117591568 18:46:30  18:46:30 up 1 day,  2:23,  5 users,  load average: 50.48, 32.49, 14.14
TS 1574160395.076076309 18:46:35  18:46:35 up 1 day,  2:23,  5 users,  load average: 51.40, 32.98, 14.40
TS 1574160400.191211227 18:46:40  18:46:40 up 1 day,  2:23,  5 users,  load average: 52.09, 33.43, 14.65
TS 1574160405.153420741 18:46:45  18:46:45 up 1 day,  2:24,  5 users,  load average: 49.20, 33.14, 14.65
TS 1574160410.013108072 18:46:50  18:46:50 up 1 day,  2:24,  5 users,  load average: 49.58, 33.49, 14.87
TS 1574160415.084485731 18:46:55  18:46:55 up 1 day,  2:24,  5 users,  load average: 48.82, 33.59, 15.00
TS 1574160420.011562959 18:47:00  18:47:00 up 1 day,  2:24,  5 users,  load average: 47.55, 33.58, 15.10
TS 1574160425.018923271 18:47:05  18:47:05 up 1 day,  2:24,  5 users,  load average: 48.95, 34.11, 15.37

4、tpcc-mysql对mysql进行测试

Build binaries

cd src ; make ( you should have mysql_config available in $PATH)

Load data

create database mysqladmin create tpcc1000

create tables mysql tpcc1000 < create_table.sql

create indexes and FK ( this step can be done after loading data) mysql tpcc1000 < add_fkey_idx.sql

populate data

simple step tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "" -w 1000 |hostname:port| |dbname| |user| |password| |WAREHOUSES| ref. tpcc_load –help for all options

load data in parallel check load.sh script

Start benchmark

./tpcc_start -h127.0.0.1 -P3306 -dtpcc1000 -uroot -w1000 -c32 -r10 -l10800

|hostname| |port| |dbname| |user| |WAREHOUSES| |CONNECTIONS| |WARMUP TIME| |BENCHMARK TIME|

ref. tpcc_start –help for all options

1)、创建数据库和表结构

cd tpcc-mysql-master

mysqladmin create tpcc1000 -uroot -p123456
mysql -uroot -p123456 tpcc1000 < create_table.sql
mysql -uroot -p123456 tpcc1000 < add_fkey_idx.sql

2)、加载数据

1
2
3

./tpcc_load -h10.10.135.172 -P3306 -d tpcc1000 -u root -p 123456 -w 10
# 1 代表1个仓库大小，10代表 10个仓库大小
# sh load.sh tpcc1000 1000

加载完后查看数据量

select count(*) from tpcc1000.customer;
select count(*) from tpcc1000.district;
select count(*) from tpcc1000.history;
select count(*) from tpcc1000.item;
select count(*) from tpcc1000.new_orders;
select count(*) from tpcc1000.order_line;
select count(*) from tpcc1000.orders;
select count(*) from tpcc1000.stock;
select count(*) from tpcc1000.warehouse;

3)、运行状态收集脚本

1	sh /root/benchmark/1-collect-system-status.sh

4)、执行测试

# -w 指定仓库数量
# -c 指定并发连接数
# -r 指定开始测试前进行warmup的时间，进行预热后，测试效果更好
# -l 指定测试持续时间
# -i 指定生成报告间隔时长
# -f 指定生成的报告文件名

# 真实测试场景中，建议预热时间不小于5分钟，持续压测时长不小于30分钟，否则测试数据可能不具参考意义
# 耗时：1800, 即 30分钟
./tpcc_start -h10.10.135.172 -P3306 -dtpcc1000 -uroot -p123456 -w10 -c32 -r300 -l1800 > tpcc_output.txt

top

vmstat 1

执行结果

5)、分析收集到的状态数据

$ sh 2-system-status-analyzer.sh status_data/5-sec-status-2019-11-19_06-status > status.txt
$ vi status.txt
unix时间戳    时间              系统负载 QPS(数据库)
1574177360 2019-11-19 23:29:20 22.00 37156.60
1574177365 2019-11-19 23:29:25 20.24 36821.80
1574177370 2019-11-19 23:29:30 21.26 36879.80
1574177375 2019-11-19 23:29:35 19.56 37217.60
1574177380 2019-11-19 23:29:40 20.63 37646.80
1574177385 2019-11-19 23:29:45 21.62 35293.20
1574177390 2019-11-19 23:29:50 19.89 36457.80
1574177395 2019-11-19 23:29:55 18.30 37464.00
1574177400 2019-11-19 23:30:00 19.72 36722.60
1574177405 2019-11-19 23:30:05 20.78 37913.60
1574177410 2019-11-19 23:30:10 19.12 36294.00
1574177415 2019-11-19 23:30:15 17.58 35279.20
1574177420 2019-11-19 23:30:20 18.98 39051.20
1574177425 2019-11-19 23:30:25 20.10 37700.20
1574177430 2019-11-19 23:30:30 18.49 37632.20
1574177435 2019-11-19 23:30:35 17.01 38068.00
1574177440 2019-11-19 23:30:40 15.65 38218.40
1574177445 2019-11-19 23:30:45 14.48 34143.40
1574177450 2019-11-19 23:30:50 13.40 7794.60
1574177455 2019-11-19 23:30:55 12.40 7744.80
1574177460 2019-11-19 23:31:00 11.41 613.40
1574177465 2019-11-19 23:31:05 10.50 2.20
1574177470 2019-11-19 23:31:10 9.66 1.40
1574177475 2019-11-19 23:31:15 8.88 1.80
1574177480 2019-11-19 23:31:20 8.17 1.80
1574177485 2019-11-19 23:31:25 7.52 1.80
1574177490 2019-11-19 23:31:30 6.92 2.00
1574177495 2019-11-19 23:31:35 6.36 2.00
1574177500 2019-11-19 23:31:40 5.85 1.60

vi tpcc_output.txt

***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '10.10.135.172'
option P with value '3306'
option d with value 'tpcc1000'
option u with value 'root'
option p with value '123456'
option w with value '10'
option c with value '32'
option r with value '300'
option l with value '1800'
<Parameters>
     [server]: 10.10.135.172   -- 主机
     [port]: 3306              -- 端口
     [DBname]: tpcc1000        -- 压测的数据库
       [user]: root            -- 账号
       [pass]: 123456          -- 密码
  [warehouse]: 10              -- 仓库数
 [connection]: 32              -- 并发线程数 
     [rampup]: 300 (sec.)      -- 数据预热时长 
    [measure]: 1800 (sec.)     -- 压测时长

RAMP-UP TIME.(300 sec.)       --预热结束

MEASURING START.              --开始压测

-- 每10秒输出一次压测数据
-- 以逗号分隔，共9列
-- 第1列，第N次10秒
-- 第2列，New-Order在本轮取样时间内成功执行事务的次数(即吞吐量)
-- 第3列，New-Order在本轮取样时间内，95%事务 在响应时间内（ms）完成
-- 第4列，New-Order在本轮取样时间内，99%事务 在响应时间内完成
-- 第5列，New-Order在本轮取样时间内，本轮测试最大响应时间（ms）
-- 第6列，Payment 有效事务数（吞吐量） | 最大响应时间（ms）
-- 第7列，Order-Status 有效事务数（吞吐量） | 最大响应时间（ms）
-- 第8列，Delivery 有效事务数（吞吐量） | 最大响应时间（ms）
-- 第9列，Stock-Level 有效事务数（吞吐量） | 最大响应时间（ms）
  10, trx: 6322, 95%: 58.880, 99%: 80.384, max_rt: 465.298, 6319|311.356, 632|147.998, 633|518.363, 632|840.708
  20, trx: 6402, 95%: 57.161, 99%: 74.165, max_rt: 108.881, 6399|102.155, 640|54.569, 640|187.683, 641|125.100
  30, trx: 6215, 95%: 58.616, 99%: 83.674, max_rt: 240.631, 6224|149.658, 623|135.217, 622|332.418, 621|168.763
  40, trx: 6365, 95%: 57.109, 99%: 76.190, max_rt: 136.020, 6362|140.110, 637|35.815, 637|142.510, 636|154.135
  50, trx: 6426, 95%: 56.973, 99%: 76.281, max_rt: 131.508, 6427|171.652, 641|45.733, 640|145.872, 643|156.595
  60, trx: 6198, 95%: 58.476, 99%: 80.384, max_rt: 229.300, 6197|238.621, 620|37.124, 621|269.572, 619|261.585
  70, trx: 6418, 95%: 57.366, 99%: 76.648, max_rt: 116.301, 6421|115.735, 643|42.165, 643|172.855, 643|151.629
  80, trx: 6406, 95%: 57.815, 99%: 76.877, max_rt: 115.657, 6396|99.119, 639|39.815, 640|152.090, 640|159.341
  90, trx: 5841, 95%: 62.047, 99%: 87.229, max_rt: 290.416, 5836|291.379, 584|42.550, 585|301.866, 584|142.273
 100, trx: 6168, 95%: 56.362, 99%: 76.717, max_rt: 125.903, 6183|115.971, 618|47.143, 619|158.824, 618|162.145
 110, trx: 6249, 95%: 57.573, 99%: 78.200, max_rt: 138.199, 6232|126.211, 624|40.234, 623|155.532, 624|152.938
 120, trx: 6107, 95%: 58.511, 99%: 78.694, max_rt: 121.301, 6125|146.152, 611|37.128, 608|156.622, 609|126.521
 130, trx: 6076, 95%: 58.424, 99%: 84.227, max_rt: 346.854, 6074|266.228, 608|48.112, 608|361.008, 610|140.032
 140, trx: 6365, 95%: 55.992, 99%: 75.599, max_rt: 115.579, 6367|110.699, 637|47.943, 637|156.809, 634|126.359
....
1760, trx: 6386, 95%: 56.599, 99%: 74.588, max_rt: 115.095, 6386|103.424, 638|44.586, 640|143.890, 637|139.005
1770, trx: 6148, 95%: 59.039, 99%: 76.510, max_rt: 124.202, 6149|99.945, 615|37.548, 612|152.861, 615|157.265
1780, trx: 5831, 95%: 59.786, 99%: 82.061, max_rt: 291.887, 5826|335.447, 583|47.130, 585|365.992, 583|156.420
1790, trx: 6161, 95%: 57.676, 99%: 78.294, max_rt: 130.952, 6162|132.039, 616|46.349, 616|149.911, 615|173.092
1800, trx: 6164, 95%: 56.295, 99%: 74.098, max_rt: 117.632, 6169|131.690, 616|39.105, 616|202.839, 618|126.449

STOPPING THREADS................................          -- 结束压测

# 成功(success,简写sc)次数，延迟(late,简写lt)次数，重试(retry,简写rt)次数，失败(failure,简写fl)次数
<Raw Results>                                             -- 第一次统计结果
  [0] sc:35421 lt:1093057  rt:0  fl:0 avg_rt: 30.0 (5)    -- New-Order，新订单业务
  [1] sc:457714 lt:670742  rt:0  fl:0 avg_rt: 15.6 (5)    -- Payment，支付业务统计
  [2] sc:96544 lt:16304  rt:0  fl:0 avg_rt: 3.6 (5)       -- Order-Status，订单状态业务统计
  [3] sc:87922 lt:24927  rt:0  fl:0 avg_rt: 72.5 (80)     -- Delivery，发货业务统计
  [4] sc:1565 lt:111282  rt:0  fl:0 avg_rt: 62.8 (20)     -- Stock-Level，库存业务统计
 in 1800 sec.

<Raw Results2(sum ver.)>                                  -- 第二次统计结果，其他同上
  [0] sc:35421  lt:1093057  rt:0  fl:0
  [1] sc:457717  lt:670764  rt:0  fl:0
  [2] sc:96544  lt:16304  rt:0  fl:0
  [3] sc:87922  lt:24927  rt:0  fl:0
  [4] sc:1565  lt:111283  rt:0  fl:0

<Constraint Check> (all must be [OK])     -- 下面所有业务逻辑结果都必须为 OK 才行
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]    -- 支付成功次数(上述统计结果中sc + lt)必须大于43.0%，否则结果为NG，而不是OK
   Order-Status: 4.35% (>= 4.0%) [OK]     --订单状态，其他同上
       Delivery: 4.35% (>= 4.0%) [OK]     -- 发货，其他同上
    Stock-Level: 4.35% (>= 4.0%) [OK]     -- 库存，其他同上
 [response time (at least 90% passed)]    -- 响应耗时指标必须超过90%通过才行
      New-Order: 3.14%  [NG] *            -- 标准5ms，只有3.14% 响应时间合格
        Payment: 40.56%  [NG] *           -- 标准5ms，
   Order-Status: 85.55%  [NG] *           -- 标准5ms，
       Delivery: 77.91%  [NG] *           -- 标准80ms，
    Stock-Level: 1.39%  [NG] *            -- 标准20ms，

<TpmC>
                 37615.934 TpmC          -- TpmC结果值{每分钟事务数，该值是第一次统计结果中的新订单事务数除以总耗时分钟数，例如本例中是：（35421+1093057）/30 ≈ 37615.93..}；1000tpmC相当于2000个事务；

tpcc-mysql的业务逻辑及其相关的几个表作用如下：

New-Order：新订单，一次完整的订单事务，几乎涉及到全部表

Payment：支付，主要对应 orders、history 表

Order-Status：订单状态，主要对应 orders、order_line 表

Delivery：发货，主要对应 order_line 表

Stock-Level：库存，主要对应 stock 表

其它表说明:

客户：主要对应 customer 表

地区：主要对应 district 表

商品：主要对应 item 表

仓库：主要对应 warehouse 表

6)、清理数据

1	drop database tpcc1000;

7)、使用gunplot绘图

① 安装 gunplot

yum install -y gnuplot

gnuplot -V
# gnuplot 4.6 patchlevel 2

帮助文档： http://gnuplot.info/docs_4.6/gnuplot.pdf

② 准备分析脚本和绘图脚本

gunplot-analyze-tpcc.sh

#!/bin/bash
TIMESLOT=1

if [ -n "$2" ]
then
    TIMESLOT=$2
    echo "Defined $2"
fi

cat $1 | grep -v HY000 | grep -v payment | grep -v neword | \
awk -v timeslot=$TIMESLOT '\
BEGIN { FS="[,():|]"; s=0; cntr=0; aggr=0 } \
/MEASURING START/ {s=1} \
/STOPPING THREADS/ {s=0} \
/0/ { \
    if (s==1) { cntr++; aggr+=$2; } \
    if (cntr==timeslot ) { printf ("%d %3f %d\n",$1,$5,$3) ; cntr=0; aggr=0  } \
}'

解释：把 140, trx: 6365, 95%: 55.992, 99%: 75.599, max_rt: 115.579, 6367|110.699, 637|47.943, 637|156.809, 634|126.359中的 “[,():|]” 字符去掉以后，就剩下了140 trx 6365 95% 55.992 99% 75.599 max_rt 115.579 6367 110.699 637 47.943 637 156.809 634 126.359。$1就是140， $5就是55.992，打印的语法就跟c语言一样

帮助文档：man awk

③ 对 tpcc-mysql 测试生成的数据文件进行提取

1	sh gunplot-analyze-tpcc.sh tpcc_output.txt > tpcc_output_transfer.txt

④ 使用tpcc-graph-build.sh脚本生成图表

画95%的事务响应时间图：gunplot-graph-build-rt.sh

#!/bin/bash
### goto user homedir and remove previous file
rm -f '$2'
gnuplot << EOP
### set data source file
datafile = '$1'
### set graph type and size
set terminal jpeg size 720,640
### set titles
set grid x y
set xlabel "Time (sec)"
set ylabel "Response Times(sec)"
### set output filename
set output '$2'
### build graph
# plot datafile using 1:2 title "95%-rt" with lines,datafile using 1:3 title "max_rt" with lines axes x1y1
plot datafile using 1:2 title "95%-rt" with lines axes x1y1
EOP

画间隔时间内完成的事务数的图：gunplot-graph-build-trx.sh

#!/bin/bash
### goto user homedir and remove previous file
rm -f '$2'
gnuplot << EOP
### set data source file
datafile = '$1'
### set graph type and size
set terminal jpeg size 720,640
### set titles
set grid x y
set xlabel "Time (sec)"
set ylabel "Transaction Nums"
### set output filename
set output '$2'
### build graph
# plot datafile using 1:2 title "95%-rt" with lines,datafile using 1:3 title "max_rt" with lines axes x1y1
plot datafile using 1:3 title "Transaction Nums" with lines axes x1y1
EOP

1	sh gunplot-graph-build-rt.sh tpcc_output_transfer.txt tpcc_output.jpg

1	sh gunplot-graph-build-trx.sh tpcc_output_transfer.txt tpcc_output.jpg

五、mysql性能调优

可以看到tpcc-mysql基准测试中 rt项全部不合格，所以需要对mysql进行性能调优