1. 安装

[root@sysbench ~]#tar zxf sysbench.tar.gz
[root@sysbench ~]#cd sysbench
[root@sysbench ~]# ./autogen.sh
[root@sysbench~]#./configure -–prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
[root@sysbench ~]#make
[root@sysbench ~]#make install

注:请正确指定MySQL includes文件和libs 文件位置

2. 使用Sysbech 进行MySQL 基准性能测试
- 首先创建测试数据库sbtest

mysql>create database sbtest;

- Prepare 数据

[root@sysbench~]#sysbench  --test=$test_dir/parallel_prepare.lua --mysql-host=$host_ip --mysql-user=$username --mysql-password=$password --mysql-port=$port_num --oltp-table-size=$table_size  --oltp-tables-count=$table_count --num-threads=$para_threads  run

- 预热数据

[root@sysbench~]#sysbench  --test=$test_dir/oltp.lua --mysql-host=$host_ip --mysql-user=$username --mysql-password=$password --mysql-port=$port_num --oltp-table-size=$table_size  --oltp-tables-count=$table_count --num-threads=64 --max-time=120  --max-requests=0 --oltp-auto-inc=off  run

- run 压力

[root@sysbench~]#sysbench  --test=$test_dir/oltp.lua --mysql-host=$host_ip --mysql-user=$username --mysql-password=$password --mysql-port=$port_num --oltp-table-size=$table_size  --oltp-tables-count=$table_count --num-threads=64 --max-time=120  --max-requests=0 --oltp-auto-inc=off  run

- clean 数据

[root@sysbench~]#sysbench  --test=$test_dir/oltp.lua --mysql-host=$host_ip --mysql-user=$username --mysql-password=$password --mysql-port=$port_num --oltp-table-size=$table_size  --oltp-tables-count=$table_count --num-threads=64 --max-time=120  --max-requests=0 --oltp-auto-inc=off  cleanup

自动测试脚本如下:

#!/bin/bash
host_ip=144.0.32.147
username=test
password='111111'
port_num=3306
table_size=5000000
table_count=1
para_threads=24
requests=50000
outfile="500W_Size_Mux_Thread_Oracle_147.out"
test_dir=/usr/local/src/sysbench/sysbench/tests/db

#prepare db  in parallel
sysbench  --test=$test_dir/parallel_prepare.lua --mysql-host=$host_ip --mysql-user=$username --mysql-password=$password --mysql-port=$port_num --oltp-table-size=$table_size  --oltp-tables-count=$table_count --num-threads=$para_threads  run

sysbench  --test=$test_dir/oltp.lua --mysql-host=$host_ip --mysql-user=$username --mysql-password=$password --mysql-port=$port_num --oltp-table-size=$table_size  --oltp-tables-count=$table_count --num-threads=64 --max-time=120  --max-requests=0 --oltp-auto-inc=off  run  

for arg in `seq 4 4 256`
do 
	sysbench  --test=$test_dir/oltp.lua --mysql-host=$host_ip --mysql-user=$username --mysql-password=$password --mysql-port=$port_num --oltp-table-size=$table_size  --oltp-tables-count=$table_count --num-threads=$arg --report-interval=10 --max-requests=$requests --oltp-auto-inc=off  run  >> $outfile
done

3. 结果分析

Number of threads: 16

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            26225724
        write:                           9366330
        other:                           3746532
        total:                           39338586
    transactions:                        1873266 (2601.71 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 35592054 (49432.47 per sec.)
    other operations:                    3746532 (5203.42 per sec.)

Test execution summary:
    total time:                          720.0136s
    total number of events:              1873266
    total time taken by event execution: 11506.8251
    per-request statistics:
         min:                                  2.37ms
         avg:                                  6.14ms
         max:                                400.48ms
         approx.  95 percentile:              14.90ms

Threads fairness:
    events (avg/stddev):           117079.1250/275.62
    execution time (avg/stddev):   719.1766/0.01

如上所示,结果中包含了相当多的信息。其中最有价值的信息如下;
总的事务数 1873266
每秒事务数 2601.71 per sec.
时间统计信息(最小,平均,最大响应时间,以及95%百分比响应时间)
线程公平性统计信息
最最重要的是每秒事务数(2601.71 per sec.)和95%响应时间

Trackback

no comment untill now

Add your comment now

切换到手机版