Benchmarking MySQL using Sysbench

sysbench-mysql.md

Benchmarking MySQL using Sysbench

Setup and Start MySQL on MacOS

brew update
brew install mysql

# start mysql
mysql.server start
mysql.server stop 
mysql.server status

# login
mysql -uroot

Setting Password https://stackoverflow.com/questions/6474775/setting-the-mysql-root-user-password-on-os-x

Create test db sbtest

mysql -uroot
> CREATE DATABASE sbtest;
> CREATE USER sbtest@127.0.0.1 IDENTIFIED BY 'Password1!';
> GRANT ALL PRIVILEGES ON sbtest.* TO sbtest@127.0.0.1;
mysql -usbtest -pPassword1! -h127.0.0.1

Install Sysbench

MacOS

brew install sysbench

sysbench --version
sysbench 1.0.20

RHEL/CentOS

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

sysbench --version

Benchmarking uinsg Sysbench

Prepare for sysbenching

• test name: oltp_common
• command: prepare

Create 3 tables and add 10000 rows per table

sysbench  \
    --mysql-host=127.0.0.1 \
    --mysql-port=3306 \
    --mysql-db=sbtest \
    --mysql-user=sbtest \
    --mysql-password=Password1! \
    --tables=3 \
    --table_size=10000 \
    oltp_common prepare

output

Creating table 'sbtest1'...
Inserting 100 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 100 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 100 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...

Read Write test

• test name: oltp_read_write
• command: run

Generic Read and Write test

sysbench  \
    --mysql-host=127.0.0.1 \
    --mysql-port=3306 \
    --mysql-db=sbtest \
    --mysql-user=sbtest \
    --mysql-password=Password1! \
    --tables=3 \
    --table_size=10000 \
    --threads=3 \
    --time=10 \
    oltp_read_write run

output

SQL statistics:
    queries performed:
        read:                            148022
        write:                           42275
        other:                           21139
        total:                           211436
    transactions:                        10566  (1056.03 per sec.)   
    queries:                             211436 (21132.12 per sec.)
    ignored errors:                      7      (0.70 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0040s
    total number of events:              10566

Latency (ms):
         min:                                    2.25
         avg:                                    2.84
         max:                                    9.74
         95th percentile:                        3.55
         sum:                                29991.25

Threads fairness:
    events (avg/stddev):           3522.0000/1.41
    execution time (avg/stddev):   9.9971/0.00

• QPS (query per sec): 21132.12 per sec
• TPS (transaction per sec): 1056.03 per sec
• total query num: 211436

Sysbench Options and Tests available

Options

--tables: testing table num (default 3） --table_size: testing row num per table（default 10000） --threads: thread num（default 1） --time: tseting duration in sec (default 10 sec)

test name

• oltp_read_write: Generic Read and Write test
• oltp_read_only: Read (SELECT) test
• oltp_write_only Write (INSERT, DELETE, UPDATE) only test
• oltp_insert: INSERT only
• bulk_insert: Bulk insert tests like INSERT INTO...VALUES (?),(?),(?)
• oltp_delete: ...

ref: https://github.com/akopytov/sysbench/tree/1.0/src/lua

References

• Install Sysbench binary package
• sysbench tests: https://github.com/akopytov/sysbench/tree/1.0/src/lua
• Sample Dataset
  • MySQL公式のサンプルデータでSQLの学習環境を作る方法
  • Example Databases
  • sakila