用sysbench测试mysql性能

安装sysbench：

sudo apt-get install sysbench

OLTP基准测试

在执行之前，首先需要进去mysql创建一个叫sbtest的数据库：

create database sbtest;//如果已有想删除的话，执行下面的//drop database sbtest;

然后生成我们需要用来测试的表：

 sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --mysql-password=XXX --mysql-socket=/var/run/mysqld/mysqld.sock prepare

生成了测试时候之后，就可以运行下面的命令了：

//这个例子才on个了8个并发进程，只读模式，测试时长60秒sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --mysql-password=dingjia --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run

下面的是测试结果：

OLTP test statistics:    queries performed:        read:                            811202        write:                           0        other:                           115886        total:                           927088    transactions:                        57943  (965.63 per sec.)    deadlocks:                           0      (0.00 per sec.)    read/write requests:                 811202 (13518.78 per sec.)    other operations:                    115886 (1931.25 per sec.)Test execution summary:    total time:                          60.0055s    total number of events:              57943    total time taken by event execution: 479.7070    per-request statistics:         min:                                  2.94ms         avg:                                  8.28ms         max:                                 37.36ms         approx.  95 percentile:              10.61msThreads fairness:    events (avg/stddev):           7242.8750/36.52    execution time (avg/stddev):   59.9634/0.00

其中最有价值的是：

1. 总的事务数
2. 每秒事务数
3. 时间统计信息（最小、平均、最大响应时间、以及95%百分比响应时间）
4. 线程公平统计信息(thread-fairness)，用于表示模拟负载的公平性

CPU基准测试

sysbench --test=cpu --cpu-max-prime=20000 --num-threads=2 run

用两个进程计算素数，结果打印出计算出素数的时间。

Maximum prime number checked in CPU test: 20000Test execution summary:    total time:                          13.2352s    total number of events:              10000    total time taken by event execution: 26.4601    per-request statistics:         min:                                  2.43ms         avg:                                  2.65ms         max:                                 13.97ms         approx.  95 percentile:               2.85msThreads fairness:    events (avg/stddev):           5000.0000/135.00    execution time (avg/stddev):   13.2300/0.00

I/O基准测试

首先要创建一个数据集：

sysbench --test=fileio --file-total-size=500M prepare

然后就可以针对不同类型的IO进行不同的测试：

• seqwr：顺序写入
• seqrewq：顺序重写
• seqrd：顺序读取
• rndrd：随机读取
• rndwr：随机写入
• rndrw：混合随机读写

执行命令：

sysbench --test=fileio --file-total-size=500M --file-test-mode=rndwr --init-rng=on --max-time=30 --max-requests=0 run

输出的结果包含大量的信息。和I/O子系统密切相关的包括每秒请求数和总吞吐量，另外，时间信息也是非常有用的。

sysbench 0.4.12:  multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1Initializing random number generator from timer.Extra file open flags: 0128 files, 3.9062Mb each500Mb total file sizeBlock size 16KbNumber of random requests for random IO: 0Read/Write ratio for combined random IO test: 1.50Periodic FSYNC enabled, calling fsync() each 100 requests.Calling fsync() at the end of test, Enabled.Using synchronous I/O modeDoing random write testThreads started!Time limit exceeded, exiting...Done.Operations performed:  0 Read, 1700 Write, 2080 Other = 3780 TotalRead 0b  Written 26.562Mb  Total transferred 26.562Mb  (906.16Kb/sec)   56.64 Requests/sec executed    //每秒请求数Test execution summary:    total time:                          30.0168s    total number of events:              1700    total time taken by event execution: 0.0147    per-request statistics:         min:                                  0.00ms         avg:                                  0.01ms         max:                                  0.06ms         approx.  95 percentile:               0.01msThreads fairness:    events (avg/stddev):           1700.0000/0.00    execution time (avg/stddev):   0.0147/0.00

测试完成后，可以运行下面的命令来删除第一步生成的测试文件

sysbench --test=fileio --file-total-size=500M cleanup