Mittwoch, 1. Oktober 2014

sysbench: benchmark your mysql storage

0) yum install sysbench -y 
1) mysql -uroot -e "CREATE SCHEMA sbtest;"
2)  sysbench --db-driver=mysql --test=oltp --num-threads=8 --max-requests=10000 --oltp-table-size=2000000 --oltp-test-mode=complex --mysql-host=localhost --mysql-db=sbtest --mysql-table-engine=innodb prepare --mysql-port=3306 --mysql-user=root
3) sysbench --db-driver=mysql --test=oltp --num-threads=8 --max-requests=10000 --oltp-table-size=2000000 --oltp-test-mode=complex --mysql-host=localhost --mysql-db=sbtest --mysql-port=3306 --mysql-user=root  run | tee sysbench.log

4) analyse your sysbench.log

Results on striped 2x3TB SATA-HGST disks /dev/md0:
OLTP test statistics:
    queries performed:
        read:                            140014
        write:                           50005
        other:                           20002
        total:                           210021
    transactions:                        10001  (151.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190019 (2868.92 per sec.)
    other operations:                    20002  (301.99 per sec.)

Test execution summary:
    total time:                          66.2336s
    total number of events:              10001
    total time taken by event execution: 529.6338
    per-request statistics:
         min:                                 15.57ms
         avg:                                 52.96ms
         max:                               1392.39ms
         approx.  95 percentile:             100.09ms

Threads fairness:
    events (avg/stddev):           1250.1250/5.11
    execution time (avg/stddev):   66.2042/0.01