Linux之sysbench压测之MySQL压测

基于OLTP的混合测试 大概是70%的读(select) 30%的写(insert, update, delete)

还是一样的分别对1 4 8 16 32 64线程做测试

[root@DS-VM-Node160 ~]# for i in 1 4 8 16 32 64; do sysbench --test=oltp --test=/usr/local/sysbench/tests/db/oltp.lua --db-driver=mysql --oltp-table-size=10000000 --mysql-db=sysbench --mysql-user=sysbench --mysql-password=lookback --mysql-host=10.10.231.149 --mysql-port=3317 --max-time=60 --max-requests=0 --report-interval=10 --oltp-reconnect-mode=random --num-threads=$i run; sleep 10; done
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored


Initializing worker threads...

Threads started!

[  10s] threads: 1, tps: 83.00, reads: 1163.38, writes: 332.29, response time: 12.98ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 1, tps: 84.50, reads: 1183.00, writes: 338.10, response time: 12.51ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 1, tps: 80.10, reads: 1121.40, writes: 320.30, response time: 13.60ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 1, tps: 81.20, reads: 1136.70, writes: 324.50, response time: 13.27ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 1, tps: 80.20, reads: 1122.20, writes: 320.80, response time: 13.80ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 1, tps: 79.10, reads: 1107.40, writes: 316.40, response time: 13.60ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            68348
        write:                           19528
        other:                           9764
        total:                           97640
    transactions:                        4882   (81.35 per sec.)
    read/write requests:                 87876  (1464.38 per sec.)
    other operations:                    9764   (162.71 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0090s
    total number of events:              4882
    total time taken by event execution: 59.9981s
    response time:
         min:                                 10.01ms
         avg:                                 12.29ms
         max:                                 29.47ms
         approx.  95 percentile:              13.42ms

Threads fairness:
    events (avg/stddev):           4882.0000/0.00
    execution time (avg/stddev):   59.9981/0.00

sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored


Initializing worker threads...

Threads started!

[  10s] threads: 4, tps: 303.20, reads: 4247.93, writes: 1212.78, response time: 14.95ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 4, tps: 297.20, reads: 4161.81, writes: 1189.40, response time: 15.57ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 4, tps: 298.90, reads: 4182.80, writes: 1195.00, response time: 15.74ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 4, tps: 301.50, reads: 4221.20, writes: 1206.00, response time: 15.42ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 4, tps: 304.30, reads: 4259.90, writes: 1217.20, response time: 15.11ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 4, tps: 301.80, reads: 4226.19, writes: 1207.20, response time: 15.39ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            253022
        write:                           72292
        other:                           36146
        total:                           361460
    transactions:                        18073  (301.16 per sec.)
    read/write requests:                 325314 (5420.91 per sec.)
    other operations:                    36146  (602.32 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0109s
    total number of events:              18073
    total time taken by event execution: 240.0009s
    response time:
         min:                                 10.58ms
         avg:                                 13.28ms
         max:                                 82.75ms
         approx.  95 percentile:              15.37ms

Threads fairness:
    events (avg/stddev):           4518.2500/12.81
    execution time (avg/stddev):   60.0002/0.00

sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored


Initializing worker threads...

Threads started!

[  10s] threads: 8, tps: 486.29, reads: 6817.09, writes: 1945.37, response time: 21.73ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 8, tps: 464.60, reads: 6502.01, writes: 1859.10, response time: 24.37ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 8, tps: 452.40, reads: 6335.70, writes: 1809.10, response time: 25.32ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 8, tps: 481.40, reads: 6739.09, writes: 1925.70, response time: 22.48ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 8, tps: 487.50, reads: 6825.02, writes: 1949.90, response time: 22.82ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 8, tps: 472.80, reads: 6618.50, writes: 1891.20, response time: 24.48ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            398412
        write:                           113832
        other:                           56916
        total:                           569160
    transactions:                        28458  (474.20 per sec.)
    read/write requests:                 512244 (8535.66 per sec.)
    other operations:                    56916  (948.41 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0122s
    total number of events:              28458
    total time taken by event execution: 479.9931s
    response time:
         min:                                 11.19ms
         avg:                                 16.87ms
         max:                                176.10ms
         approx.  95 percentile:              23.57ms

Threads fairness:
    events (avg/stddev):           3557.2500/24.95
    execution time (avg/stddev):   59.9991/0.00

sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored


Initializing worker threads...

Threads started!

[  10s] threads: 16, tps: 725.89, reads: 10178.43, writes: 2905.15, response time: 32.72ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 16, tps: 759.80, reads: 10633.90, writes: 3038.60, response time: 29.91ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 16, tps: 675.40, reads: 9465.25, writes: 2706.98, response time: 29.60ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 16, tps: 609.30, reads: 8522.44, writes: 2431.51, response time: 34.80ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 16, tps: 711.10, reads: 9952.21, writes: 2844.40, response time: 32.09ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 16, tps: 764.80, reads: 10712.80, writes: 3061.00, response time: 28.87ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            594706
        write:                           169916
        other:                           84958
        total:                           849580
    transactions:                        42479  (707.77 per sec.)
    read/write requests:                 764622 (12739.77 per sec.)
    other operations:                    84958  (1415.53 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0185s
    total number of events:              42479
    total time taken by event execution: 960.0062s
    response time:
         min:                                 11.75ms
         avg:                                 22.60ms
         max:                               2114.27ms
         approx.  95 percentile:              31.21ms

Threads fairness:
    events (avg/stddev):           2654.9375/34.92
    execution time (avg/stddev):   60.0004/0.01

sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored


Initializing worker threads...

Threads started!

[  10s] threads: 32, tps: 1054.28, reads: 14789.93, writes: 4220.12, response time: 46.70ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 32, tps: 1019.60, reads: 14272.00, writes: 4077.50, response time: 46.95ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 32, tps: 1017.60, reads: 14249.59, writes: 4070.70, response time: 45.84ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 32, tps: 1022.60, reads: 14315.41, writes: 4089.70, response time: 51.62ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 32, tps: 991.00, reads: 13871.07, writes: 3963.69, response time: 50.64ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 32, tps: 1066.00, reads: 14925.93, writes: 4265.41, response time: 47.03ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            864388
        write:                           246968
        other:                           123484
        total:                           1234840
    transactions:                        61742  (1028.69 per sec.)
    read/write requests:                 1111356 (18516.37 per sec.)
    other operations:                    123484 (2057.37 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0202s
    total number of events:              61742
    total time taken by event execution: 1920.1881s
    response time:
         min:                                 14.07ms
         avg:                                 31.10ms
         max:                                453.06ms
         approx.  95 percentile:              47.93ms

Threads fairness:
    events (avg/stddev):           1929.4375/26.81
    execution time (avg/stddev):   60.0059/0.01

sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored


Initializing worker threads...

Threads started!

[  10s] threads: 64, tps: 1122.86, reads: 15809.64, writes: 4517.04, response time: 93.74ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 64, tps: 1071.11, reads: 14969.68, writes: 4267.72, response time: 103.60ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 64, tps: 1110.60, reads: 15542.30, writes: 4437.10, response time: 101.33ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 64, tps: 1143.60, reads: 16019.19, writes: 4576.00, response time: 96.88ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 64, tps: 1065.70, reads: 14933.69, writes: 4268.30, response time: 107.65ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 64, tps: 1034.70, reads: 14464.44, writes: 4141.41, response time: 112.02ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            917686
        write:                           262196
        other:                           131098
        total:                           1310980
    transactions:                        65549  (1091.93 per sec.)
    read/write requests:                 1179882 (19654.74 per sec.)
    other operations:                    131098 (2183.86 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0304s
    total number of events:              65549
    total time taken by event execution: 3840.5321s
    response time:
         min:                                 17.21ms
         avg:                                 58.59ms
         max:                                767.73ms
         approx.  95 percentile:             102.18ms

Threads fairness:
    events (avg/stddev):           1024.2031/18.06
    execution time (avg/stddev):   60.0083/0.01

[root@DS-VM-Node160 ~]#

测试完成后做下清理动作

[root@DS-VM-Node160 ~]# sysbench --test=oltp  --test=/usr/local/sysbench/tests/db/oltp.lua --mysql-db=sysbench --mysql-user=sysbench --mysql-password=lookback --mysql-host=10.10.231.149 --mysql-port=3317 cleanup
sysbench 0.5:  multi-threaded system evaluation benchmark

Dropping table 'sbtest1'...
[root@DS-VM-Node160 ~]# 

#!/bin/bash
#########################################################################
# File Name: mysql_bench.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: Thu 09 Jun 2016 02:53:20 AM CST
#########################################################################

user=${1:-sysbench}
password=${2:-lookback}
host=${3:-localhost}
port=${4:-3306}
dbname=${5:-sysbench}
threads=(${6:-1 4 8 16 32 64})
luafile=${7:-/usr/local/sysbench/tests/db/oltp.lua}

creattb() {
    mysql -u${user} -p${password} -h${host} -P${port} -e \
        "DROP DATABASE IF EXISTS ${dbname};"
    mysql -u${user} -p${password} -h${host} -P${port} -e \
        "CREATE DATABASE IF NOT EXISTS ${dbname} DEFAULT CHARACTER SET utf8;"
    sysbench --test=oltp --test=${luafile} \
        --db-driver=mysql --oltp-table-size=1000000 \
        --mysql-db=${dbname} --mysql-user=${user} \
        --mysql-password=${password} --mysql-host=${host} --mysql-port=${port} \
        prepare
}

benching() {
    for i in ${threads[*]}; do
        echo -e "\n======\033[31mPerforming test RW-${i}T\033[0m======"
        sysbench --test=oltp --test=${luafile} \
            --db-driver=mysql --oltp-table-size=1000000 \
            --mysql-db=${dbname} --mysql-user=${user} \
            --mysql-password=${password} --mysql-host=${host} --mysql-port=${port} \
            --max-time=60 --max-requests=0 --report-interval=10 \
            --oltp-reconnect-mode=random --num-threads=$i \
            run
        sleep 10
    done
}

cleantb() {
    sysbench --test=oltp  --test=/usr/local/sysbench/tests/db/oltp.lua \
        --mysql-db=sysbench --mysql-user=sysbench \
        --mysql-password=lookback --mysql-host=10.10.231.149 \
        --mysql-port=3317 \
        cleanup
    mysql -u${user} -p${password} -h${host} -P${port} -e \
        "DROP DATABASE IF EXISTS ${dbname};"
}

creattb $@ && benching $@ && cleantb $@

###使用方法 bash 脚本名字  MySQL用户名 MySQL密码 MySQL主机 MySQL端口 压测用的库 压测用的线程
[root@DS-VM-Node160 ~]# bash mysql_bench.sh sysbench lookback 10.10.231.149 3317 sysbench '1 4 8'