Install

macOS

On macOS, up-to-date sysbench packages are available from Homebrew:

# Add --with-postgresql if you need PostgreSQL support
brew install sysbench

Debian/Ubuntu

$ curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash; sudo apt -y install sysbench

I/O测试

# 生成测试文件
sysbench --file-num=5 --file-total-size=2G fileio prepare

# 开始测试：创建5个文件，总共2G，每个文件大概400M
sysbench --file-num=5 --file-total-size=2G --file-test-mode=seqwr --time=300 --max-requests=0 fileio run

# 清理测试文件
sysbench fileio cleanup

–file-test-mode 参数

seqwr：顺序写入
seqrewr：顺序重写
seqrd：顺序读取
rndrd：随机读取
rndwr：随机写入
rndrw：随机读取/写入

结果

sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Extra file open flags: (none)
128 files, 8MiB each
1GiB total file size
Block size 16KiB
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing sequential write (creation) test
Initializing worker threads...

Threads started!


File operations:
    reads/s:                      0.00
    writes/s:                     110126.24
    fsyncs/s:                     140961.68

Throughput:
    read, MiB/s:                  0.00
    written, MiB/s:               1720.72

General statistics:
    total time:                          300.0004s
    total number of events:              75326492

Latency (ms):
         min:                                    0.00
         avg:                                    0.00
         max:                                   10.80
         95th percentile:                        0.00
         sum:                               284419.14

Threads fairness:
    events (avg/stddev):           75326492.0000/0.00
    execution time (avg/stddev):   284.4191/0.00

MySQL 测试

创建 Tables

创建DB

# on my linux, mysql -uroot -p1234 -h 127.0.0.1 -P 3307 -e "create database sw_benchmark_test;"
$ mysql -uroot -p123456 -e "create database sw_benchmark_test;"

创建 Tables

# on my linux, sysbench /usr/share/sysbench/oltp_read_only.lua --time=3600 --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=root --mysql-password=1234 --mysql-db=sw_benchmark_test --table-size=1000000 --tables=10 --threads=32 --events=999999999 prepare
$ sysbench /usr/local/Cellar/sysbench/1.0.20_1/share/sysbench/oltp_read_only.lua --time=3600 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sw_benchmark_test --table-size=1000000 --tables=10 --threads=32 --events=999999999 prepare
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

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

oltp_common.lua ：等执行的测试脚本（我们使用sysbench 自带的 lua 测试脚本）
- ubuntu下，则在 /usr/share/sysbench/ 目录下
- macOS下，且使用brew安装，/usr/local/Cellar/sysbench/<version>/share/sysbench/
--time ：Limit for total execution time in seconds. 0 means no limit, default is 10s
--events ：Limit for total number of requests. 0 (the default) means no limit, default is no limit
MySQL 相关参数
- --mysql-host ：MySQL server host
- --mysql-port ：MySQL server port
- --mysql-user ：MySQL server 账号
- --mysql-password ：MySQL server 密码
- --mysql-db ：MySQL Server 数据库。
--table-size ：表记录条数。
--tables ：表名
--threads ：The total number of worker threads to create
--report-interval ：以秒为单位定期报告具有指定间隔的中间统计信息，默认为 0 ，表示禁用中间报告。 > 艿艿：这个一定要记得设置下，例如说设置个 10s ，不然一脸懵逼。

执行测试

# on my linux, sysbench /usr/share/sysbench/oltp_read_only.lua --time=60 --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=root --mysql-password=1234 --mysql-db=sw_benchmark_test --table-size=1000000 --tables=10 --threads=32 --events=999999999 run
$ sysbench /usr/local/Cellar/sysbench/1.0.20_1/share/sysbench/oltp_read_only.lua --time=60 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sw_benchmark_test --table-size=1000000 --tables=10 --threads=32 --events=999999999 run

oltp_read_write.lua ：执行的测试脚本。此时，我们在 /usr/share/sysbench/ 下，寻找我们想要测试的场景。 oltp_read_write.lua ，表示混合读写，在一个事务中，默认比例是：select:update_key:update_non_key:delete:insert = 14:1:1:1:1 。这也是为什么，我们测试出来的 TPS 和 QPS 的比例，大概在 1:18~20 左右。相当于说，一个事务中，有 18 个读写操作。

结果

32 线程 + 每个表 100w 数据

macOS

SQL statistics:
    queries performed:
        read:                            5292616
        write:                           0
        other:                           756088
        total:                           6048704
    transactions:                        378044 (6288.90 per sec.)
    queries:                             6048704 (100622.37 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1125s
    total number of events:              378044

Latency (ms):
         min:                                    1.45
         avg:                                    5.08
         max:                                  230.75
         95th percentile:                        5.47
         sum:                              1919649.01

Threads fairness:
    events (avg/stddev):           11813.8750/35.77
    execution time (avg/stddev):   59.9890/0.01

Ubuntu

SQL statistics:
    queries performed:
        read:                            1319108
        write:                           0
        other:                           188444
        total:                           1507552
    transactions:                        94222  (1568.11 per sec.)
    queries:                             1507552 (25089.68 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0851s
    total number of events:              94222

Latency (ms):
         min:                                   10.72
         avg:                                   20.40
         max:                                  405.98
         95th percentile:                       26.20
         sum:                              1921877.18

Threads fairness:
    events (avg/stddev):           2944.4375/18.18
    execution time (avg/stddev):   60.0587/0.01

16 线程 + 每个表 100w 数据

清理数据

# on my linux, sysbench /usr/share/sysbench/oltp_read_only.lua --time=300 --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=root --mysql-password=1234 --mysql-db=sw_benchmark_test --table-size=1000000 --tables=10 --threads=16 --events=999999999 cleanup

$ sysbench /usr/local/Cellar/sysbench/1.0.20_1/share/sysbench/oltp_read_write.lua --time=300 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sw_benchmark_test --table-size=1000000 --tables=10 --threads=16 --events=999999999 cleanup

Demo

macOS

# create a db
$ mysql -uroot -p123456 -e "create database sw_benchmark_test_oltp_read_only;"

# prepare
$ sysbench --db-driver=mysql --table-size=10000 --tables=16 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 /usr/local/Cellar/sysbench/1.0.20_1/share/sysbench/oltp_insert.lua --time=100 --threads=32 --mysql-db=sw_benchmark_test_oltp_read_only prepare

# run
$ sysbench --db-driver=mysql --table-size=10000 --tables=16 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 /usr/local/Cellar/sysbench/1.0.20_1/share/sysbench/oltp_insert.lua --time=100 --threads=32 --mysql-db=sw_benchmark_test_oltp_read_only run

# cleanup
$ sysbench --db-driver=mysql --table-size=10000 --tables=16 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 /usr/local/Cellar/sysbench/1.0.20_1/share/sysbench/oltp_insert.lua --time=100 --threads=32 --mysql-db=sw_benchmark_test_oltp_read_only cleanup

Ubuntu

# create a db
$ mysql -uroot -p1234 -h 127.0.0.1 -P 3307 -e "create database sw_benchmark_test_oltp_read_only;"

$ sysbench /usr/share/sysbench/oltp_insert.lua --time=100 --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=root --mysql-password=1234 --mysql-db=sw_benchmark_test_oltp_read_only --table-size=10000 --tables=10 --threads=32 prepare

$ sysbench /usr/share/sysbench/oltp_insert.lua --time=100 --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=root --mysql-password=1234 --mysql-db=sw_benchmark_test_oltp_read_only --table-size=10000 --tables=10 --threads=32 run

$ sysbench /usr/share/sysbench/oltp_insert.lua --time=100 --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=root --mysql-password=1234 --mysql-db=sw_benchmark_test_oltp_read_only --table-size=10000 --tables=10 --threads=32 cleanup

Results

macOS

32 thread

SQL statistics:
    queries performed:
        read:                            0
        write:                           6441316
        other:                           0
        total:                           6441316
    transactions:                        6441316 (64327.23 per sec.)
    queries:                             6441316 (64327.23 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          100.1333s
    total number of events:              6441316

Latency (ms):
         min:                                    0.14
         avg:                                    0.50
         max:                                  385.16
         95th percentile:                        0.57
         sum:                              3189279.38

Threads fairness:
    events (avg/stddev):           201291.1250/183.45
    execution time (avg/stddev):   99.6650/0.00

64 thead

SQL statistics:
    queries performed:
        read:                            0
        write:                           7708843
        other:                           0
        total:                           7708843
    transactions:                        7708843 (76823.84 per sec.)
    queries:                             7708843 (76823.84 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          100.3441s
    total number of events:              7708843

Latency (ms):
         min:                                    0.15
         avg:                                    0.83
         max:                                  289.68
         95th percentile:                        0.94
         sum:                              6383889.35

Threads fairness:
    events (avg/stddev):           120450.6719/132.23
    execution time (avg/stddev):   99.7483/0.00

Ubuntu

32 thead

SQL statistics:
    queries performed:
        read:                            0
        write:                           207612
        other:                           0
        total:                           207612
    transactions:                        207612 (2075.71 per sec.)
    queries:                             207612 (2075.71 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          100.0185s
    total number of events:              207612

Latency (ms):
         min:                                    4.93
         avg:                                   15.41
         max:                                  149.84
         95th percentile:                       36.24
         sum:                              3199635.37

Threads fairness:
    events (avg/stddev):           6487.8750/10.08
    execution time (avg/stddev):   99.9886/0.00

64 thead

SQL statistics:
    queries performed:
        read:                            0
        write:                           334827
        other:                           0
        total:                           334827
    transactions:                        334827 (3347.67 per sec.)
    queries:                             334827 (3347.67 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          100.0167s
    total number of events:              334827

Latency (ms):
         min:                                    5.52
         avg:                                   19.11
         max:                                  309.84
         95th percentile:                       41.10
         sum:                              6398917.00

Threads fairness:
    events (avg/stddev):           5231.6719/8.59
    execution time (avg/stddev):   99.9831/0.00

Compare with https://help.aliyun.com/document_detail/171818.html

【MySQL】Benchmark - SysBench

Install

macOS

Debian/Ubuntu

I/O测试

MySQL 测试

创建 Tables

执行测试

结果

32 线程 + 每个表 100w 数据

16 线程 + 每个表 100w 数据

清理数据

Demo

macOS

Ubuntu

Results

macOS

Ubuntu

Reference

FEATURED TAGS

FRIENDS

TOC