基于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 ~]#
Linux之sysbench压测之MySQL压测
#!/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'