服务器性能，数据库压测工具sysbench

sysbench@TOC

服务器性能，数据库压测工具sysbench

安装sysbench

1. sudo apt -y install sysbench；
   sysbench --help命令执行成功表示安装成功
2. 下载测试脚本；
   wget https://github.com/akopytov/sysbench/archive/1.0.15.zip
   如果命令执行不成功，可以把网址用浏览器打开，浏览器会自动下载

测试命令

往mysql写入数据：sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-db=test --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=4000 --mysql-user=root --mysql-password= --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare

执行压测：sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-db=test --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=4000 --mysql-user=root --mysql-password= --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run >> …/mysql_sysbench_10.log

清理数据：sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root --mysql-socket=/opt/mysql/run/mysql.sock cleanup

测试结果：
Running the test with following options:
Number of threads: 1
Report intermediate results every 10 second(s)
Initializing random number generator from current time

Initializing worker threads…

Threads started!

[ 10s ] thds: 1 tps: 27.38 qps: 548.75 (r/w/o: 384.38/109.51/54.85) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 1 tps: 56.84 qps: 1136.70 (r/w/o: 795.69/227.34/113.67) lat (ms,95%): 23.10 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 1 tps: 60.70 qps: 1214.74 (r/w/o: 850.13/243.21/121.40) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 1 tps: 49.60 qps: 991.31 (r/w/o: 694.11/198.00/99.20) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 1 tps: 60.00 qps: 1199.21 (r/w/o: 839.20/240.00/120.00) lat (ms,95%): 22.28 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 1 tps: 55.70 qps: 1114.97 (r/w/o: 780.78/222.79/111.40) lat (ms,95%): 28.16 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 1 tps: 56.40 qps: 1127.82 (r/w/o: 789.42/225.60/112.80) lat (ms,95%): 26.20 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 1 tps: 61.20 qps: 1223.96 (r/w/o: 856.77/244.79/122.40) lat (ms,95%): 20.00 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 1 tps: 44.20 qps: 884.22 (r/w/o: 619.01/176.80/88.40) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 1 tps: 54.10 qps: 1081.98 (r/w/o: 757.39/216.40/108.20) lat (ms,95%): 24.83 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 1 tps: 55.70 qps: 1114.01 (r/w/o: 779.80/222.80/111.40) lat (ms,95%): 22.69 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 1 tps: 58.50 qps: 1169.97 (r/w/o: 818.98/233.99/117.00) lat (ms,95%): 23.52 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 89656
write: 25616
other: 12808
total: 128080
transactions: 6404 (53.36 per sec.)
queries: 128080 (1067.25 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 120.0077s
total number of events: 6404

Latency (ms):
min: 11.86
avg: 18.73
max: 619.98
95th percentile: 26.20
sum: 119955.66

Threads fairness:
events (avg/stddev): 6404.0000/0.00
execution time (avg/stddev): 119.9557/0.00

sysbench 1.0.11 (using system LuaJIT 2.1.0-beta3)

生成csv对比文件
线程数+qps : cat mysql_sysbench_*.log | egrep " cat|threads:|queries:" | tr -d “\n” | sed ‘s/Number of threads: /\n/g’ | sed ‘s/[/\n/g’ | sed ‘s/[A-Za-z/]{1,}: //g’| sed ‘s/ .//g’ | awk {'print $1 KaTeX parse error: Expected 'EOF', got '}' at position 3: 3'}̲ | sed 's/(/,/g…’ |sed ‘1i threads,cstat_on_qps’ > cstat_on_qps.csv

线程数+qps+耗时 : cat mysql_sysbench_*.log | egrep " cat|threads:|queries:|percentile:" | tr -d “\n” | sed ‘s/Number of threads: /\n/g’ | sed ‘s/[/\n/g’ | sed ‘s/[A-Za-z/]{1,}😕/g’| sed ‘s/ .//g’ | awk {'print $1 $3 $5 KaTeX parse error: Expected 'EOF', got '}' at position 3: 7'}̲| sed 's/(/,/g'…’ |sed ‘1i threads,cstat_on_qps,costtime’