mysql benchmark基准测试

git项目地址:

https://github.com/akopytov/sysbench

利用sysbench很容易对mysql做性能基准测试(当然这个工具很强大,除了测试主流数据库性能,还能测试其它方面,详情自己看官网项目文档)

 

mac上的用法:

一、安装 

brew install sysbench

 

二、先在mysql上创建一个专门的测试数据库,比如test

 

三、利用sysbench先生成测试数据 

sysbench --test=oltp --oltp-table-size=5000000 --oltp-table-name=t_test \
--mysql-table-engine=innodb \
--mysql-host=localhost \
--mysql-db=test \
--mysql-user=root \
--mysql-password=*** \
prepare

上面这一堆的意思,是在本机localhost的test数据库的t_test中创建500万条测试数据,表的数据库引擎为innodb.

tips:如果想知道还有哪些参数可用,可输入命令

sysbench --test=oltp help

centos上如果用yum install sysbench安装的话,运行时可能会出现

FATAL: no database driver specified
FATAL: failed to initialize database driver!

可以加上参数 --db-driver=mysql ,原因是yum方式安装时,同时安装了mysql与其它数据库的驱动,不指定驱动类型的话,sysbench不知道你是要测试是mysql,还是oracle之类的其它数据库

   

四、运行测试

sysbench --test=oltp --oltp-table-size=5000000 --oltp-table-name=t_test \
--mysql-table-engine=innodb \
--mysql-host=localhost \
--mysql-db=test \
--mysql-user=root \
--mysql-password=*** \
run

最后一个prepare改成run就行了,测试完后,会输出类似以下结果:

sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 1

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
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (514.48 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (9775.14 per sec.)
    other operations:                    20000  (1028.96 per sec.)

Test execution summary:
    total time:                          19.4371s
    total number of events:              10000
    total time taken by event execution: 19.3581
    per-request statistics:
         min:                                  1.65ms
         avg:                                  1.94ms
         max:                                 13.48ms
         approx.  95 percentile:               2.34ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   19.3581/0.00

一般我们比较关心:

    transactions:                        10000  (514.48 per sec.)

    read/write requests:                 190000 (9775.14 per sec.)

这代表每秒能处理的事务数,以及每秒的读写次数。

 

五、清理测试数据

sysbench --test=oltp --oltp-table-size=5000000 --oltp-table-name=t_test \
--mysql-table-engine=innodb \
--mysql-host=localhost \
--mysql-db=test \
--mysql-user=root \
--mysql-password=*** \
cleanup

最后一个换成cleanup即可。 

 

春晚实在太无聊了,还不如写二行代码有意思,祝各位园友猴年大吉,身体健康,心想事成!

posted @ 2016-02-07 21:54 菩提树下的杨过 阅读(...) 评论(...) 编辑 收藏