记一次sysbench，mysql安装使用

sysbench安装

（1）下载解压

wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"

unzip sysbench-1.0.zip

cd sysbench-1.0

（2）安装依赖

yum install automake libtool –y

 （3）安装

安装之前，确保位于之前解压的sysbench目录中。

./autogen.sh

./configure

export LD_LIBRARY_PATH=/usr/local/mysql/include #这里换成机器中mysql路径下的include

如果不是管理员权限  则需要加上sudo

make

make install

（4）安装成功

[root@test sysbench-1.0]# sysbench --version

sysbench 1.0.9

安装第三步会有mysql_config not found

解决方法：yum install mysql-devel gcc gcc-devel python-devel

1：安装mysql

tar -xvzf mysql-5.7.22-linux-glibc2.12-x86_64.tar.xz

mv mysql-5.7.22-linux-glibc2.12-x86_64 mysql 修改名字

使用方法：

1，在test_api 中创建10个表，每个表有10万条数据，客户端的并发线程数为10，执行时间为120秒，每10秒生成一次报告。

sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=data.dev.c.smarteventcloud.cn --mysql-port=3306 --mysql-user=user --mysql-password=password --mysql-db=test_api --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare

2，将测试结果导出到文件中，便于后续分析。数据报告出来需要等待时间，耐心等待

sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=data.dev.c.smarteventcloud.cn  --mysql-port=3306 --mysql-user=user --mysql-password=password --mysql-db=test_api --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run >> /home/nakal/mysysbench.log

报告内容：

sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 10

Report intermediate results every 10 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 10 tps: 74.46 qps: 1497.55 (r/w/o: 1049.40/189.04/259.11) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 10 tps: 70.66 qps: 1415.50 (r/w/o: 991.24/190.86/233.40) lat (ms,95%): 331.91 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 10 tps: 69.10 qps: 1380.73 (r/w/o: 966.32/185.10/229.30) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 10 tps: 58.30 qps: 1165.89 (r/w/o: 815.99/160.90/189.00) lat (ms,95%): 363.18 err/s: 0.00 reconn/s: 0.00

sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 10

Report intermediate results every 10 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 10s ] thds: 10 tps: 70.41 qps: 1419.93 (r/w/o: 995.69/195.74/228.50) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 10 tps: 62.70 qps: 1253.67 (r/w/o: 877.58/182.60/193.50) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 10 tps: 62.40 qps: 1242.39 (r/w/o: 868.66/185.01/188.71) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 10 tps: 59.50 qps: 1195.00 (r/w/o: 837.80/173.80/183.40) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 10 tps: 59.60 qps: 1191.10 (r/w/o: 833.50/178.60/179.00) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 10 tps: 57.70 qps: 1154.16 (r/w/o: 807.44/174.51/172.21) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 10 tps: 57.10 qps: 1143.28 (r/w/o: 800.98/176.10/166.20) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 10 tps: 56.30 qps: 1126.27 (r/w/o: 788.58/176.30/161.40) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 10 tps: 56.40 qps: 1124.96 (r/w/o: 786.57/177.39/160.99) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 10 tps: 50.90 qps: 1017.97 (r/w/o: 712.95/159.41/145.61) lat (ms,95%): 411.96 err/s: 0.00 reconn/s: 0.00

[ 110s ] thds: 10 tps: 52.70 qps: 1054.21 (r/w/o: 737.94/167.89/148.39) lat (ms,95%): 404.61 err/s: 0.00 reconn/s: 0.00

[ 120s ] thds: 10 tps: 54.08 qps: 1081.96 (r/w/o: 757.29/174.53/150.14) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            98126

        write:                          21250

        other:                          20804

        total:                          140180

    transactions:                        7009  (58.30 per sec.)

    queries:                            140180 (1165.93 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

General statistics:

    total time:                          120.2287s

    total number of events:              7009

Latency (ms):

        min:                                  91.81

        avg:                                  171.38

        max:                                1410.30

        95th percentile:                      369.77

        sum:                              1201220.61

Threads fairness:

    events (avg/stddev):          700.9000/159.13

    execution time (avg/stddev):  120.1221/0.06

参考文献

https://www.cnblogs.com/kismetv/archive/2017/09/30/7615738.html