项目地址 https://launchpad.net/sysbench (目前已迁移至https://github.com/akopytov/sysbench)

下载源码包,解压,

执行autogen.sh, 可能会提示缺少automake 和libtoolize  ,自行yum install automake  libtool 即可

之后./configure

mysql 自行编译安装的可能会提示 configure: error: mysql_config executable not found 需要添加如下参数

 
  
之后make 即可...  make完之后会在源码目录的sysbench下生成一个sysbench二进制文件
 
  
直接执行即可看到相关的参数信息..
 
  
 
Q:  mysql 若是自行编译安装的话,执行sysbench可能会遇到错误提示
 
error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: 
No such file or directory
A:
 
  
 
  
CPU 性能测试 / CPU performance test
CPU测试使用64位整数，测试计算素数直到某个最大值所需要的时间 
  
./sysbench --test=cpu  --num-threads=2  --cpu-max-prime=20000   run 
 
  
sysbench 0.5:  multi-threaded system evaluation benchmark
 
  
Running the test with following options: 
Number of threads: 2 
Random number generator seed is 0 and will be ignored
 
  

Prime numbers limit: 20000
 
  
Threads started!
 
  

General statistics: 
    total time:                          29.3082s 
    total number of events:              10000 
    total time taken by event execution: 58.5971s 
    response time: 
         min:                                  2.86ms 
         avg:                                  5.86ms 
         max:                                107.92ms 
         approx.  95 percentile:               3.10ms
 
  
Threads fairness: 
    events (avg/stddev):           5000.0000/7.00 
    execution time (avg/stddev):   29.2985/0.00
 
  
 
 
  
cpu测试主要是进行素数的加法运算，在上面的例子中，指定了最大的素数为 20000，自己可以根据机器cpu的性能来适当调整数值。
 
  
其中 --num-threads The total number of worker threads to create 
 
  
默认为1, 一般设置为CPU核数(线程数)即可,设置过大也不会对性能带来影响 
 
  
  •  

    cpu options: 
      --cpu-max-prime=N      upper limit for primes generator [10000]
 
  
线程(thread)测试  / Threads subsystem performance test
 
  
测试线程调度器的性能。对于高负载情况下测试线程调度器的行为非常有用。
 
  
多核性能，线程调度 线程并发执行，循环响应信号量花费的时间
 
  
./sysbench --test=threads --num-threads=2 run  
 
  
sysbench 0.5:  multi-threaded system evaluation benchmark
 
  
Running the test with following options: 
Number of threads: 2 
Random number generator seed is 0 and will be ignored
 
  

Threads started!
 
  

General statistics: 
    total time:                          3.9964s 
    total number of events:              10000 
    total time taken by event execution: 7.9829s 
    response time: 
         min:                                  0.76ms 
         avg:                                  0.80ms 
         max:                                  2.22ms 
         approx.  95 percentile:               0.82ms
 
  
Threads fairness: 
    events (avg/stddev):           5000.0000/0.00 
    execution time (avg/stddev):   3.9914/0.00 
 
  
 
   
threads options: 
  --thread-yields=N      number of yields to do per request [1000] 
  --thread-locks=N       number of locks per thread [8]
 
  
 
  
互斥锁(mutex) /Mutex performance test
并发线程同时申请互斥锁循环一定次数花费的时间 测试互斥锁的性能 
  
方式是模拟所有线程在同一时刻并发运行，并都短暂请求互斥锁。
 
  
./sysbench --test=mutex --num-threads=2 --mutex-locks=1000000 run 
 
  
sysbench 0.5:  multi-threaded system evaluation benchmark
 
  
Running the test with following options: 
Number of threads: 2 
Random number generator seed is 0 and will be ignored
 
  

Threads started!
 
  

General statistics: 
    total time:                          0.0874s 
    total number of events:              2 
    total time taken by event execution: 0.1312s 
    response time: 
         min:                                 43.82ms 
         avg:                                 65.60ms 
         max:                                 87.37ms 
         approx.  95 percentile:              87.38ms
 
  
Threads fairness: 
    events (avg/stddev):           1.0000/0.00 
    execution time (avg/stddev):   0.0656/0.02
 
  
 
   
mutex options: 
  --mutex-num=N        total size of mutex array [4096] 
  --mutex-locks=N      number of mutex locks to do per thread [50000] 
  --mutex-loops=N      number of empty loops to do inside mutex lock [10000] 
 
  
 
  
内存测试 / Memory functions speed test
以不同块大小传输一定数量的数据,测试内存读写性能 
  
sysbench --test=memory --memory-block-size=8K --memory-total-size=2G  --num-threads=2 run 
 
  
sysbench 0.5:  multi-threaded system evaluation benchmark
 
  
Running the test with following options: 
Number of threads: 2 
Random number generator seed is 0 and will be ignored
 
  

Threads started!
 
  
Operations performed: 262144 (743947.40 ops/sec)
 
  
2048.00 MB transferred (5812.09 MB/sec)
 
  

General statistics: 
    total time:                          0.3524s 
    total number of events:              262144 
    total time taken by event execution: 0.4492s 
    response time: 
         min:                                  0.00ms 
         avg:                                  0.00ms 
         max:                                100.97ms 
         approx.  95 percentile:               0.00ms
 
  
Threads fairness: 
    events (avg/stddev):           131072.0000/20186.00 
    execution time (avg/stddev):   0.2246/0.02
 
  
 
   
memory options: 
  --memory-block-size=SIZE    size of memory block for test [1K] 
  --memory-total-size=SIZE    total size of data to transfer [100G] 
  --memory-scope=STRING       memory access scope {global,local} [global] 
  --memory-hugetlb=[on|off]   allocate memory from HugeTLB pool [off] 
  --memory-oper=STRING        type of memory operations {read, write, none} [write] 
  --memory-access-mode=STRING memory access mode {seq,rnd} [seq]
 
  
 
  
文件IO基准测试 / File I/O test
 
  
测试系统在不同IO负载下的IOPS性能
 
  
准备工作(创建测试文件)
 
  
sysbench --test=fileio --num-threads=2 --file-total-size=4G --file-test-mode=rndrw prepare 
 
  
测试
 
  
sysbench --test=fileio --num-threads=2 --file-total-size=4G --file-test-mode=rndrw run
 
  
清理测试文件
 
  
sysbench --test=fileio --num-threads=2 --file-total-size=4G --file-test-mode=rndrw cleanup
 
  
./sysbench --test=fileio --num-threads=2 --file-total-size=4G --file-test-mode=rndrw run 
sysbench 0.5:  multi-threaded system evaluation benchmark
 
  
Running the test with following options: 
Number of threads: 2 
Random number generator seed is 0 and will be ignored
 
  

Extra file open flags: 0 
128 files, 32Mb each 
4Gb total file size 
Block size 16Kb 
Number of IO requests: 10000 
Read/Write ratio for combined random IO test: 1.50 
Periodic FSYNC enabled, calling fsync() each 100 requests. 
Calling fsync() at the end of test, Enabled. 
Using synchronous I/O mode 
Doing random r/w test 
Threads started!
 
  
Operations performed:  5999 reads, 4001 writes, 12800 Other = 22800 Total 
Read 93.734Mb  Written 62.516Mb  Total transferred 156.25Mb  (9.1877Mb/sec) 
  588.01 Requests/sec executed
 
  
General statistics: 
    total time:                          17.0064s 
    total number of events:              10000 
    total time taken by event execution: 0.6489s 
    response time: 
         min:                                  0.00ms 
         avg:                                  0.06ms 
         max:                                 97.59ms 
         approx.  95 percentile:               0.03ms
 
  
Threads fairness: 
    events (avg/stddev):           5000.0000/618.00 
    execution time (avg/stddev):   0.3245/0.06
 
  
 
   
fileio options: 
  --file-num=N                  number of files to create [128] 
  --file-block-size=N           block size to use in all IO operations [16384] 
  --file-total-size=SIZE        total size of files to create [2G] 
  --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw} 
  seqwr 顺序写入 seqrewr 顺序重写 seqrd 顺序读取 rndrd 随机读取 rndwr 随机写入 rndrw 混合随机读/写 
  --file-io-mode=STRING         file operations mode {sync,async,mmap} [sync] 
  --file-extra-flags=STRING     additional flags to use on opening files {sync,dsync,direct} [] 
  --file-fsync-freq=N           do fsync() after this number of requests (0 - don't use fsync()) [100] 
  --file-fsync-all=[on|off]     do fsync() after each write operation [off] 
  --file-fsync-end=[on|off]     do fsync() at the end of test [on] 
  --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync] 
  --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0] 
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]
 
  
 
  
 
   
引用网上的一段总结
 
  
 
  
 
   
性能测试【找范围内最大素数{时间越短越好}】
 
  
 
  
 
   
线程调度【线程并发执行，循环响应信号量花费的时间{越少越好}】
 
  
 
  
 
   
互斥锁【并发线程同时申请互斥锁循环一定次数花费的时间{越少越好}】
 
  
 
  
 
   
内存【以不同块大小传输一定数量的数据吞吐量大小{越大越好}】
 
  
 
  
 
   
IO【不同场景下IOPS{越大越好}】
 
  
 
  
 
 
  
 
 
  
 
 
  
 
 
  
  •  
    MySQL数据库测试 
 
  
sysbench 0.5通过一系列LUA脚本来替换之前的oltp，来模拟更接近真实的基准测试环境。这些测试脚本包含：insert.lua、oltp.lua、 parallel_prepare.lua、select_random_points.lua、update_index.lua、 delete.lua oltp_simple.lua、select.lua、select_random_ranges.lua、 update_non_index.lua，脚本使用方式基本类似。 
 
  
sysbench 0.5默认使用sbtest库，但是需要自己手工先创建好，也可以使用--mysql-db指定，其他非默认项指定选项： 
 
  
  1. -mysql-host 
  2. -mysql-port 
  3. -mysql-socket 
  4. -mysql-user 
  5. -mysql-password 
  6. -mysql-db 
  7. -mysql-ssl 
 
  
  • prepare 
    生成表并插入数据，可使用parallel_prepare.lua脚本来并行准备数据。 
 
  
  1. –db-driver 服务器类型 mysql | drizzle,默认为mysql 
  2. –mysql-table-engine 表存数引擎 
  3. –myisam-max-rows MyISAM表MAX_ROWS选项(用于大表) 
  4. –oltp-tables-count 生成表数量[sbtest1、sbtest2...] 
  5. –oltp-table-size 生成表的行数 
  6. –oltp-secondary ID列生成二级索引而不是主键 
  7. –oltp-auto-inc 设置ID列是否自增 on | off，默认为on 
 
  
   注意  -oltp-tables-count 和 –oltp-table-size之类的参数 ,sysbench 参数写错了不会提示出错,会按原参数默认值执行
 
  
 
   
./sysbench --test=tests/db/oltp.lua --mysql-user=sysbench --oltp-tables-count=10 --oltp-table-size=100000 --mysql-password=sysbench --mysql-host=192.168.200.2 --report-interval=1 --num-threads=16 run 
sysbench 0.5:  multi-threaded system evaluation benchmark
 
   
Running the test with following options: 
Number of threads: 16 
Report intermediate results every 1 second(s) 
Random number generator seed is 0 and will be ignored
 
   

Threads started!
 
   
OLTP test statistics: 
    queries performed: 
        read:                            140000 
        write:                           40000 
        other:                           20000 
        total:                           200000 
    transactions:                        10000  (375.41 per sec.)##(这就是大家评测的TPS,即每秒处理的事务数) 
    read/write requests:                 180000 (6757.36 per sec.)###(每秒读写次数)
    other operations:                    20000  (750.82 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
   
 
   
General statistics: 
    total time:                          26.6376s 
    total number of events:              10000 
    total time taken by event execution: 425.6943s 
    response time: 
         min:                                 11.18ms 
         avg:                                 42.57ms 
         max:                                194.44ms 
         approx.  95 percentile:              78.24ms
 
   
Threads fairness: 
    events (avg/stddev):           625.0000/6.95 
    execution time (avg/stddev):   26.6059/0.01
 
   

    execution time (avg/stddev):   46.9500/0.01
 
  
 
  
如何避免压测时受到缓存的影响  有2点建议 
a、填充测试数据比物理内存还要大，至少超过 innodb_buffer_pool_size 值，不能将数据全部装载到内存中，除非你的本意就想测试全内存状态下的MySQL性能。 
b、每轮测试完成后，都重启mysqld实例，并且用下面的方法删除系统cache，释放swap（如果用到了swap的话），甚至可以重启整个OS。
 
  
[root@imysql.com]# sync  -- 将脏数据刷新到磁盘
[root@imysql.com]# echo 3 > /proc/sys/vm/drop_caches  -- 清除OS Cache
[root@imysql.com]# swapoff -a && swapon -a