【MySQL】sysbench压测服务器及结果解读

nanatsg 2018-03-27

展开全文

前言：最近新到一批机器，需要对机器进行压测，主要压测范围包括CPU测试、磁盘IO测试、线程测试、OLTP测试等，那么sysbench就可以满足我们的压测需求。下面我们简单来看下sysbench的安装使用以及压测结果的解读。

一、sysbench安装

sysbench安装其实非常简单，可以参考下git，其实MySQL官网也可以下到，不过版本比较老了，最新的版本是sysbench1.1.0，这里我们讲下源码编译安装。
上传sysbench到服务器、解压、安装：

[root@localhost tmp]# unzip sysbench-master.zip[root@localhost tmp]# cd sysbench-master[root@localhost sysbench-master]# ./autogen.sh[root@localhost sysbench-master]# ./configure[root@localhost sysbench-master]# make -j[root@localhost sysbench-master]# make install

其实可以查看下README.md,上面也写了如何安装

## Build and Install shell    ./autogen.sh    # Add --with-pgsql to build with PostgreSQL support    ./configure    make -j    make install

二、压测过程及压测结果解读

1.cpu测试

[root@localhost storage]# sysbench --test=cpu --cpu-max-prime=2000000 runWARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Running the test with following options:Number of threads: 1Initializing random number generator from current timePrime numbers limit: 2000000Initializing worker threads...Threads started!CPU speed:    events per second:     0.63Throughput:    events/s (eps):                      0.6334    time elapsed:                        11.0520s    total number of events:              7Latency (ms):         min:                                 1571.86         avg:                                 1578.84         max:                                 1620.53         95th percentile:                     1618.78         sum:                                11051.91Threads fairness:    events (avg/stddev):           7.0000/0.00    execution time (avg/stddev):   11.0519/0.00

cpu测试主要是进行素数的加法运算，上面我们的例子中，指定了最大的质数发生器数量为 2000000，可以看出服务器此次测试执行时间大约为11.05秒
2.磁盘IO测试

[root@localhost storage]# sysbench --test=fileio --num-threads=16 --file-total-size=30G --file-test-mode=rndrw prepareWARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.WARNING: --num-threads is deprecated, use --threads insteadsysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)128 files, 245760Kb each, 30720Mb totalCreating files for the test...Extra file open flags: (none)Creating file test_file.0Creating file test_file.1Creating file test_file.2Creating file test_file.3Creating file test_file.4Creating file test_file.5Creating file test_file.6Creating file test_file.7Creating file test_file.8Creating file test_file.9Creating file test_file.10Creating file test_file.11Creating file test_file.12Creating file test_file.13Creating file test_file.14Creating file test_file.15Creating file test_file.16Creating file test_file.17Creating file test_file.18Creating file test_file.19Creating file test_file.20Creating file test_file.21Creating file test_file.22Creating file test_file.23Creating file test_file.24Creating file test_file.25Creating file test_file.26Creating file test_file.27Creating file test_file.28Creating file test_file.29Creating file test_file.30Creating file test_file.31Creating file test_file.32Creating file test_file.33Creating file test_file.34Creating file test_file.35Creating file test_file.36Creating file test_file.37Creating file test_file.38Creating file test_file.39Creating file test_file.40Creating file test_file.41Creating file test_file.42Creating file test_file.43Creating file test_file.44Creating file test_file.45Creating file test_file.46Creating file test_file.47Creating file test_file.48Creating file test_file.49Creating file test_file.50Creating file test_file.51Creating file test_file.52Creating file test_file.53Creating file test_file.54Creating file test_file.55Creating file test_file.56Creating file test_file.57Creating file test_file.58Creating file test_file.59Creating file test_file.60Creating file test_file.61Creating file test_file.62Creating file test_file.63Creating file test_file.64Creating file test_file.65Creating file test_file.66Creating file test_file.67Creating file test_file.68Creating file test_file.69Creating file test_file.70Creating file test_file.71Creating file test_file.72Creating file test_file.73Creating file test_file.74Creating file test_file.75Creating file test_file.76Creating file test_file.77Creating file test_file.78Creating file test_file.79Creating file test_file.80Creating file test_file.81Creating file test_file.82Creating file test_file.83Creating file test_file.84Creating file test_file.85Creating file test_file.86Creating file test_file.87Creating file test_file.88Creating file test_file.89Creating file test_file.90Creating file test_file.91Creating file test_file.92Creating file test_file.93Creating file test_file.94Creating file test_file.95Creating file test_file.96Creating file test_file.97Creating file test_file.98Creating file test_file.99Creating file test_file.100Creating file test_file.101Creating file test_file.102Creating file test_file.103Creating file test_file.104Creating file test_file.105Creating file test_file.106Creating file test_file.107Creating file test_file.108Creating file test_file.109Creating file test_file.110Creating file test_file.111Creating file test_file.112Creating file test_file.113Creating file test_file.114Creating file test_file.115Creating file test_file.116Creating file test_file.117Creating file test_file.118Creating file test_file.119Creating file test_file.120Creating file test_file.121Creating file test_file.122Creating file test_file.123Creating file test_file.124Creating file test_file.125Creating file test_file.126Creating file test_file.12732212254720 bytes written in 55.25 seconds (556.00 MiB/sec).[root@localhost storage]# sysbench --test=fileio --num-threads=16 --file-total-size=30G --file-test-mode=rndrw runWARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.WARNING: --num-threads is deprecated, use --threads insteadsysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Running the test with following options:Number of threads: 16Initializing random number generator from current timeExtra file open flags: (none)128 files, 240MiB each30GiB total file sizeBlock size 16KiBNumber of IO requests: 0Read/Write ratio for combined random IO test: 1.50Periodic FSYNC enabled, calling fsync() each 100 requests.Calling fsync() at the end of test, Enabled.Using synchronous I/O modeDoing random r/w testInitializing worker threads...Threads started!Throughput:         read:  IOPS=68491.05 1070.17 MiB/s (1122.16 MB/s)         write: IOPS=45660.96 713.45 MiB/s (748.11 MB/s)         fsync: IOPS=146103.57Latency (ms):         min:                                  0.00         avg:                                  0.06         max:                                  3.61         95th percentile:                      0.31         sum:                             156128.82

可以看到，磁盘的读IOPS可以达到68491.05,写IOPS可以达到45660.96，fsync可以达到146103.57
3.线程测试

[root@localhost storage]# sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 runWARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.WARNING: --num-threads is deprecated, use --threads insteadsysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Running the test with following options:Number of threads: 64Initializing random number generator from current timeInitializing worker threads...Threads started!Throughput:    events/s (eps):                      14851.1802    time elapsed:                        10.0036s    total number of events:              148565Latency (ms):         min:                                    0.02         avg:                                    4.31         max:                                   24.49         95th percentile:                       10.46         sum:                               639889.93Threads fairness:    events (avg/stddev):           2321.3281/42.94    execution time (avg/stddev):   9.9983/0.00

(发送64次/个测试线程请求，每次/个线程请求产生/生成100个数量，每个线程的锁数量为2) ，测试执行时间为10秒
4.内存测试

[root@localhost storage]# sysbench --test=memory --memory-block-size=8k --memory-total-size=40G runWARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Running the test with following options:Number of threads: 1Initializing random number generator from current timeRunning memory speed test with the following options:  block size: 8KiB  total size: 40960MiB  operation: write  scope: globalInitializing worker threads...Threads started!Total operations: 5242880 (1443403.42 per second)40960.00 MiB transferred (11276.59 MiB/sec)Throughput:    events/s (eps):                      1443403.4239    time elapsed:                        3.6323s    total number of events:              5242880Latency (ms):         min:                                    0.00         avg:                                    0.00         max:                                    0.02         95th percentile:                        0.00         sum:                                 2926.10Threads fairness:    events (avg/stddev):           5242880.0000/0.00    execution time (avg/stddev):   2.9261/0.00

上述参数指定了本次测试整个过程是在内存中传输 40G 的数据量，每个 block 大小为 8K。测试结果显示：
执行时间为3.6323秒，每秒传输速度为11276.59Mb每秒
5.OLTP测试
(1)100线程
数据准备：

[root@localhost storage]# sysbench /usr/local/share/sysbench/oltp_common.lua \> --mysql-user=tpcc --mysql-password=tpcc --mysql-socket=/tmp/mysql3306.sock \> --tables=10 --table_size=20000000 --threads=100   --max-requests=0 preparesysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)Initializing worker threads...Creating table 'sbtest1'...Creating table 'sbtest4'...Creating table 'sbtest2'...Creating table 'sbtest9'...Creating table 'sbtest6'...Creating table 'sbtest8'...Creating table 'sbtest7'...Creating table 'sbtest5'...Creating table 'sbtest10'...Creating table 'sbtest3'...Inserting 20000000 records into 'sbtest1'Inserting 20000000 records into 'sbtest7'Inserting 20000000 records into 'sbtest4'Inserting 20000000 records into 'sbtest9'Inserting 20000000 records into 'sbtest6'Inserting 20000000 records into 'sbtest8'Inserting 20000000 records into 'sbtest2'Inserting 20000000 records into 'sbtest5'Inserting 20000000 records into 'sbtest3'Inserting 20000000 records into 'sbtest10'Creating a secondary index on 'sbtest4'...Creating a secondary index on 'sbtest1'...Creating a secondary index on 'sbtest10'...Creating a secondary index on 'sbtest3'...Creating a secondary index on 'sbtest6'...Creating a secondary index on 'sbtest8'...Creating a secondary index on 'sbtest7'...Creating a secondary index on 'sbtest9'...Creating a secondary index on 'sbtest5'...Creating a secondary index on 'sbtest2'...

开始测试：

[root@localhost storage]# sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=tpcc --mysql-password=tpcc --table_size=20000000 --tables=10 --threads=100 --time=3600 --report-interval=10 --mysql-socket=/tmp/mysql3306.sock run > /tmp/40_100.log

测试过程中，可以tail -f /tmp/40_100.log查看输出
测试结果：这里只展示统计部分

SQL statistics:    queries performed:        read:                            143195290        write:                           40912940        other:                           20456470        total:                           204564700    transactions:                        10228235 (2841.15 per sec.)    queries:                             204564700 (56823.07 per sec.)    ignored errors:                      0      (0.00 per sec.)    reconnects:                          0      (0.00 per sec.)Throughput:    events/s (eps):                      2841.1536    time elapsed:                        3600.0289s    total number of events:              10228235Latency (ms):         min:                                    2.49         avg:                                   35.19         max:                                 2017.54         95th percentile:                       39.65         sum:                            359973625.38Threads fairness:    events (avg/stddev):           102282.3500/5194.20    execution time (avg/stddev):   3599.7363/0.01

(2)200线程
数据准备：

[root@localhost storage]# sysbench /usr/local/share/sysbench/oltp_common.lua \> --mysql-user=tpcc --mysql-password=tpcc --mysql-socket=/tmp/mysql3306.sock \> --tables=10 --table_size=20000000 --threads=200   --max-requests=0 prepare

开始测试：

[root@localhost storage]# sysbench /usr/local/share/sysbench/oltp_read_write.lua \--mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=tpcc \--mysql-password=tpcc --table_size=20000000 --tables=10 --threads=200 --time=3600 \--report-interval=10 --mysql-socket=/tmp/mysql3306.sock run > /tmp/40_200.log

测试结果：

SQL statistics:    queries performed:        read:                            142876258        write:                           40821788        other:                           20410894        total:                           204108940    transactions:                        10205447 (2834.80 per sec.)    queries:                             204108940 (56696.02 per sec.)    ignored errors:                      0      (0.00 per sec.)    reconnects:                          0      (0.00 per sec.)Throughput:    events/s (eps):                      2834.8009    time elapsed:                        3600.0577s    total number of events:              10205447Latency (ms):         min:                                    2.39         avg:                                   70.55         max:                                 2011.65         95th percentile:                       80.03         sum:                            719978372.22Threads fairness:    events (avg/stddev):           51027.2350/3840.19    execution time (avg/stddev):   3599.8919/0.02

(3)300线程
数据准备：

[root@localhost storage]# sysbench /usr/local/share/sysbench/oltp_common.lua \> --mysql-user=tpcc --mysql-password=tpcc --mysql-socket=/tmp/mysql3306.sock \> --tables=10 --table_size=20000000 --threads=300   --max-requests=0 prepare

开始测试：

sysbench /usr/local/share/sysbench/oltp_read_write.lua \--mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest \--mysql-user=tpcc --mysql-password=tpcc --table_size=20000000 --tables=10 --threads=300 \--time=3600 --report-interval=10 --mysql-socket=/tmp/mysql3306.sock run > /tmp/40_300.log

测试结果：

SQL statistics:    queries performed:        read:                            144281438        write:                           41223268        other:                           20611634        total:                           206116340    transactions:                        10305817 (2862.66 per sec.)    queries:                             206116340 (57253.12 per sec.)    ignored errors:                      0      (0.00 per sec.)    reconnects:                          0      (0.00 per sec.)Throughput:    events/s (eps):                      2862.6561    time elapsed:                        3600.0891s    total number of events:              10305817Latency (ms):         min:                                    2.64         avg:                                  104.79         max:                                 4576.60         95th percentile:                      123.28         sum:                           1079986824.04Threads fairness:    events (avg/stddev):           34352.7233/2459.96    execution time (avg/stddev):   3599.9561/0.03

生成压测图
安装gnuplot

plot 'D:\40_100.log' using 9 with linepoint title '100 threads','D:\40_200.log' using 9 with linepoint title '200 threads','D:\40_300.log' using 9 with linepoint title '300 threads';

多线程QPS对比图

多线程TPS对比图

三、压测注意事项

1.在OLTP测试过程中，数据库初始化后，冷备data目录，每次测试完毕后删除当前数据目录，拷贝data目录重新启动，为的是每次开始测试前环境保持一致。
2.测试完成后，需要再系统层做一些清理的工作
shell>sync #刷新脏数据到磁盘
shell>echo 3 >/proc/sys/vm/drop_cache #清除cache
shell>swapoff -a && swapon -a 释放swap
3.压测过程中需要修改参数max_prepared_stmt_count默认为16382，修改为65536
4.如果是SSD硬盘的话，innodb_io_capacity和innodb_io_capacity_max两个参数也要相应调大，建议调整到50000以上。
5.对于测试数据的准备，我们通过show engine innodb status观察Free buffers,尽量模拟生产环境，让测试数据填满整个buffer pool。我这里innodb_buffer_pool_size设置的40G，测试表数量10张，每张表数据2000万，差不多填满buffer pool。

下篇会给大家带来InnoDB引擎和TokuDB引擎的压力测试。