MySQL

【MySQL】Benchmark - SysBench

Posted by 西维蜀黍 on 2021-11-07, Last Modified on 2022-02-19

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

Reference

FEATURED TAGS
algorithm algorithmproblem architecturalpattern architecture aws c# cachesystem codis compile concurrentcontrol database dataformat datastructure debug design designpattern distributedsystem django docker domain engineering freebsd git golang grafana hackintosh hadoop hardware hexo http hugo ios iot java javaee javascript kafka kubernetes linux linuxcommand linuxio lock macos markdown microservices mysql nas network networkprogramming nginx node.js npm oop openwrt operatingsystem padavan performance programming prometheus protobuf python redis router security shell software testing spring sql systemdesign truenas ubuntu vmware vpn windows wmware wordpress xml zookeeper
FRIENDS

TOC