Testing MySQL speed on OS X

test_mysql.md

• Install sysbench:

brew install sysbench

• Create a new database called "dbtest"
• Find the test file:

mdfind oltp_read_only.lua

• Prepare test:

sysbench --db-driver=mysql --mysql-user=[USER NAME] --mysql-password=[PWD] --mysql-db=dbtest --range_size=100 --table_size=100000 --tables=2 --threads=1 --events=0 --time=60 --rand-type=uniform [FULL PATH TO TEST FILE] prepare

• Execute test (runs 60 seconds):

sysbench --db-driver=mysql --mysql-user=[USER NAME] --mysql-password=[PWD] --mysql-db=dbtest --range_size=100 --table_size=100000 --tables=2 --threads=1 --events=0 --time=60 --rand-type=uniform [FULL PATH TO TEST FILE] run

• Clean the DB

sysbench --db-driver=mysql --mysql-user=[USER NAME] --mysql-password=[PWD] --mysql-db=dbtest --range_size=100 --table_size=100000 --tables=2 --threads=1 --events=0 --time=60 --rand-type=uniform [FULL PATH TO TEST FILE] cleanup

Example output: Important are "transactions per sec" and "queries per sec"

SQL statistics:
    queries performed:
        read:                            284326
        write:                           0
        other:                           40618
        total:                           324944
    transactions:                        20309  (338.46 per sec.)
    queries:                             324944 (5415.42 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0017s
    total number of events:              20309

Latency (ms):
         min:                                    1.23
         avg:                                    2.95
         max:                                   17.32
         95th percentile:                        4.03
         sum:                                59968.23

Threads fairness:
    events (avg/stddev):           20309.0000/0.00
    execution time (avg/stddev):   59.9682/0.00