Sysbench: MySQL基准测试工具

发布时间: 更新时间: 总字数:990 阅读时间:2m 作者: IP上海 分享 网址

SysBench 是一款模块化、跨平台和多线程的(MySQL、PGSQL)基准测试工具,用于评估对在高负载条件下运行数据库的系统性能。

介绍

sysbench 是一款基于 LuaJIT 的可编写脚本的多线程基准测试工具。它最常用于数据库基准测试,但也可用于创建不涉及数据库服务器的任意复杂工作负载。

已支持的数据库驱动程序:

  • mysql: MySQL 驱动程序
  • pgsql: PostgreSQL 驱动程序

测试类型:

  • fileio: 文件 I/O 测试
  • cpu: CPU 性能测试
  • memory: 内存功能速度测试
  • threads: 线程子系统性能测试
  • mutex: Mutex 性能测试

安装

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

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

# mac
brew install sysbench
  • 本文档使用的版本
$ sysbench --version
sysbench 1.0.20

help

sysbench-help ...
$ sysbench --help
Usage:
  sysbench [options]... [testname] [command]

Commands implemented by most tests: prepare run cleanup help

General options:
  --threads=N                     number of threads to use [1]
  --events=N                      limit for total number of events [0]
  --time=N                        limit for total execution time in seconds [10]
  --forced-shutdown=STRING        number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
  --thread-stack-size=SIZE        size of stack per thread [64K]
  --rate=N                        average transactions rate. 0 for unlimited rate [0]
  --report-interval=N             periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
  --report-checkpoints=[LIST,...] dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
  --debug[=on|off]                print more debugging info [off]
  --validate[=on|off]             perform validation checks where possible [off]
  --help[=on|off]                 print help and exit [off]
  --version[=on|off]              print version and exit [off]
  --config-file=FILENAME          File containing command line options
  --tx-rate=N                     deprecated alias for --rate [0]
  --max-requests=N                deprecated alias for --events [0]
  --max-time=N                    deprecated alias for --time [0]
  --num-threads=N                 deprecated alias for --threads [1]

Pseudo-Random Numbers Generator options:
  --rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special]
  --rand-spec-iter=N number of iterations used for numbers generation [12]
  --rand-spec-pct=N  percentage of values to be treated as 'special' (for special distribution) [1]
  --rand-spec-res=N  percentage of 'special' values to use (for special distribution) [75]
  --rand-seed=N      seed for random number generator. When 0, the current time is used as a RNG seed. [0]
  --rand-pareto-h=N  parameter h for pareto distribution [0.2]

Log options:
  --verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]

  --percentile=N       percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
  --histogram[=on|off] print latency histogram in report [off]

General database options:

  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers) [mysql]
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
  --db-debug[=on|off] print database-specific debug information [off]


Compiled-in database drivers:
  mysql - MySQL driver
  pgsql - PostgreSQL driver

mysql options:
  --mysql-host=[LIST,...]          MySQL server host [localhost]
  --mysql-port=[LIST,...]          MySQL server port [3306]
  --mysql-socket=[LIST,...]        MySQL socket
  --mysql-user=STRING              MySQL user [sbtest]
  --mysql-password=STRING          MySQL password []
  --mysql-db=STRING                MySQL database name [sbtest]
  --mysql-ssl[=on|off]             use SSL connections, if available in the client library [off]
  --mysql-ssl-cipher=STRING        use specific cipher for SSL connections []
  --mysql-compression[=on|off]     use compression, if available in the client library [off]
  --mysql-debug[=on|off]           trace all client library calls [off]
  --mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205]
  --mysql-dry-run[=on|off]         Dry run, pretend that all MySQL client API calls are successful without executing them [off]

pgsql options:
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]

Compiled-in tests:
  fileio - File I/O test
  cpu - CPU performance test
  memory - Memory functions speed test
  threads - Threads subsystem performance test
  mutex - Mutex performance test

See 'sysbench <testname> help' for a list of options for each test.

使用

支持的测试用例(OLTP(Online Transaction Processing) 是一种数据处理类型,包括执行多个并发的事务)

  • 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: …

详细信息参考:

$ ls /usr/share/sysbench/
bulk_insert.lua  oltp_insert.lua        oltp_read_write.lua        oltp_write_only.lua
oltp_common.lua  oltp_point_select.lua  oltp_update_index.lua      select_random_points.lua
oltp_delete.lua  oltp_read_only.lua     oltp_update_non_index.lua  select_random_ranges.lua

文件 I/O 测试

$ sysbench --file-total-size=1G fileio prepare
...
1073741824 bytes written in 3.88 seconds (264.17 MiB/sec).

$ sysbench --file-total-size=1G --file-test-mode=rndrw --time=300 --max-requests=0 fileio run

oltp_common 测试数据准备

  • 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

oltp_read_write 读写测试

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

指标说明:

  • QPS (query per sec)
  • TPS (transaction per sec)

其他

# 准备数据
sysbench --time=300 --mysql-host=<ip> --mysql-port=3306 --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table-size=1000000 --tables=10 --threads=32 --events=999999999 oltp_common prepare

# 运行测试
sysbench --time=300 --mysql-host=<ip> --mysql-port=3306 --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table-size=1000000 --tables=10 --threads=16 --events=999999999  --report-interval=10 oltp_read_write run

# 清理数据
sysbench --time=300 --mysql-host=<ip> --mysql-port=3306 --mysql-user=<user> --mysql-password=<password> --mysql-db=sbtest --table-size=1000000 --tables=10 --threads=16 --events=999999999 --report-interval=10 oltp_read_write cleanup

参考

  1. https://github.com/akopytov/sysbench
Home Archives Categories Tags Statistics
本文总阅读量 次 本站总访问量 次 本站总访客数