MySQL压力测试工具

一、使用MySQL本身的Mysqlslap工具

mysqlslap是mysql自带的基准测试工具,该工具查询数据,语法简单,灵活容易使用.该工具可以模拟多个客户端同时并发的向服务器发出查询更新,给出了性能测试数据而且提供了多种引擎的性能比较。mysqlslap为mysql性能优化前后提供了直观的验证依据,系统运维和DBA人员应该掌握一些常见的压力测试工具,才能准确的掌握线上数据库支撑的用户流量上限及其抗压性等问题。

1.1 更改其默认的最大连接数

在对MySQL进行压力测试之前,需要更改其默认的最大连接数,如下:

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
max_connections=1024
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysql -uroot -p123
mysql> show variables like ‘max_connections‘;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1024  |
+-----------------+-------+
#验证修改已经生效
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1 --number-int-cols=20 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=2000 -uroot -p123 --verbose
#模拟测试两次读写并发,第一次100,第二次200,自动生成SQL脚本,测试表包含20个init字段,30个char字段,每次执行2000查询请求。测试引擎分别是myisam,innodb。
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.325 seconds
        Minimum number of seconds to run all queries: 0.325 seconds
        Maximum number of seconds to run all queries: 0.325 seconds
        Number of clients running queries: 100
        Average number of queries per client: 20
#Myisam第一次100客户端同时发起增查用0.325/s
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.331 seconds
        Minimum number of seconds to run all queries: 0.331 seconds
        Maximum number of seconds to run all queries: 0.331 seconds
        Number of clients running queries: 200
        Average number of queries per client: 10
#Myisam第二次200客户端同时发起增查用0.331/s 
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.661 seconds
        Minimum number of seconds to run all queries: 0.661 seconds
        Maximum number of seconds to run all queries: 0.661 seconds
        Number of clients running queries: 100
        Average number of queries per client: 20
#Innodb第一次100客户端同时发起增查用0.661/s
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.610 seconds
        Minimum number of seconds to run all queries: 0.610 seconds
        Maximum number of seconds to run all queries: 0.610 seconds
        Number of clients running queries: 200
        Average number of queries per client: 10
#Innodb第二次200客户端同时发起增查用0.610/s

可以根据实际需求,一点点的加大并发数量进行压力测试!

关于mysqlslap工具各个选项的详细解释可以通过mysqlslap --help进行查询!

二、使用第三方sysbench工具

2.1 安装sysbench工具

[root@db01 ~]# yum -y install epel-release 
#安装epel源
[root@db01 ~]# yum -y install sysbench
#安装sysbench工具
[root@db01 ~]# sysbench --version
sysbench 1.0.17
#查询sysbench工具的版本信息,确认sysbench工具已经安装

sysbench可以进行以下测试:

  • CPU运算性能测试;
  • 磁盘IO性能测试;
  • 调度程序性能测试;
  • 内存分配及传输速度测试;
  • POSIX线程性能测试;
  • 数据库性能测试(OLTP 基准测试,需要通过 /usr/share/sysbench/ 目录中的 Lua 脚本执行,例如 oltp_read_only.lua 脚本执行只读测试);
  • sysbench还可以通过运行命令时指定自己的Lua脚本进行自定义测试;

2.2 查看sysbench工具的帮助选项

[root@mysql ~]# sysbench --help
Usage:
  sysbench [options]... [testname] [command]

Commands implemented by most tests: prepare run cleanup help # 可用的命令,四个

General options:            # 通用选项
  --threads=N                     要使用的线程数,默认 1 个 [1]
  --events=N                      最大允许的事件个数 [0]
  --time=N                        最大的总执行时间,以秒为单位 [10]
  --forced-shutdown=STRING        在 --time 时间限制到达后,强制关闭之前等待的秒数,默认“off”禁用(number of seconds to wait after the --time limit before forcing shutdown, or ‘off‘ to disable) [off]
  --thread-stack-size=SIZE        每个线程的堆栈大小 [64K]
  --rate=N                        平均传输速率。0 则无限制 [0]
  --report-interval=N             以秒为单位定期报告具有指定间隔的中间统计信息 0 禁用中间报告 [0]
  --report-checkpoints=[LIST,...] 转储完整的统计信息并在指定的时间点重置所有计数器。参数是一个逗号分隔的值列表,表示从测试开始经过这个时间量时必须执行报告检查点(以秒为单位)。报告检查点默认关闭。 []
  --debug[=on|off]                打印更多 debug 信息 [off]
  --validate[=on|off]             尽可能执行验证检查 [off]
  --help[=on|off]                 显示帮助信息并退出 [off]
  --version[=on|off]              显示版本信息并退出 [off]
  --config-file=FILENAME          包含命令行选项的文件
  --tx-rate=N                     废弃,改用 --rate [0]
  --max-requests=N                废弃,改用 --events [0]
  --max-time=N                    废弃,改用 --time [0]
  --num-threads=N                 废弃,改用 --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  指定要使用的数据库驱动程序 (‘help‘ to get list of available drivers)
  --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 和 PostgreSQL
  mysql - MySQL driver
  pgsql - PostgreSQL driver

mysql options:              # MySQL 数据库专用选项
  --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:              # PostgreSQL 数据库专用选项
  --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.

2.3 sysbench测试MySQL性能

① 准备测试数据
[root@db01 ~]# sysbench /usr/share/sysbench/oltp_common.lua help
#查看sysbench自带的lua脚本使用方法
[root@db01 ~]# mysqladmin -uroot -p123 create sbtest;
#必须创建sbtest库,sbtest是sysbench默认使用的库名
[root@db01 ~]# sysbench --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123 /usr/share/sysbench/oltp_common.lua --tables=10 --table_size=100000 prepare
#准备测试所用的表,这些测试表放在测试库sbtest中。这里使用的lua脚本为/usr/share/sysbench/oltp_common.lua
#--tables=10表示创建10个测试表
#--table_size=100000表示每个表中插入10W行数据
#prepare表示这是准备数的过程
② 确认测试数据存在
[root@db01 ~]# mysql -uroot -p123 -e ‘show tables from sbtest;‘
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest10         |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
| sbtest6          |
| sbtest7          |
| sbtest8          |
| sbtest9          |
+------------------+
[root@db01 ~]# mysql -uroot -p123 -e ‘select count(*) from sbtest.sbtest1;‘
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
③ 数据库测试和结果分析

需要注意的是,之前使用的lua脚本为oltp_common.lua,它是一个通用脚本,是被其它lua脚本调用的,它不能直接拿来测试。

所以,我这里用oltp_read_write.lua脚本来做读、写测试。还有很多其它类型的测试,比如只读测试、只写测试、删除测试、大批量插入测试等等。可找到对应的lua脚本进行调用即可。

[root@db01 ~]# sysbench --threads=4 --time=20 --report-interval=5 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123 /usr/share/sysbench/oltp_read_write.lua --tables=10 --table_size=100000 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 4
Report intermediate results every 5 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!
#以下是每5秒返回一次的结果,统计的指标包括:
# 线程数、tps(每秒事务数)、qps(每秒查询数)、
# 每秒的读/写/其它次数、延迟、每秒错误数、每秒重连次数
[ 5s ] thds: 4 tps: 318.10 qps: 6371.34 (r/w/o: 4461.96/1272.39/636.99) lat (ms,95%): 20.37 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 4 tps: 340.15 qps: 6803.43 (r/w/o: 4760.92/1362.21/680.30) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 380.98 qps: 7622.45 (r/w/o: 5336.16/1524.33/761.97) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 387.29 qps: 7741.76 (r/w/o: 5420.03/1547.15/774.58) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            99918   #执行的读操作数量
        write:                           28548   #执行的写操作数量
        other:                           14274   #执行的其他操作数量
        total:                           142740
    transactions:                        7137   (356.13 per sec.)   #执行事务的平均概率
    queries:                             142740 (7122.70 per sec.)  #平均每秒可以进行多少次查询
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0391s    #总消耗时间
    total number of events:              7137        #总请求数量(读、写、其他)

Latency (ms):
         min:                                    2.58
         avg:                                   11.22
         max:                                  450.05
         95th percentile:                       15.00   #采样计算的平均延迟
         sum:                                80067.39

Threads fairness:
    events (avg/stddev):           1784.2500/7.43
    execution time (avg/stddev):   20.0168/0.01
④ cpu/io/内存等测试

sysbench内置的几个测试指标如下:

[root@db01 ~]# sysbench --help
      ..........  # 省略部分内容
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

可以直接help输出测试方法,例如,fileio测试:

[root@db01 ~]# sysbench fileio help
sysbench 1.0.17 (using system LuaJIT 2.0.4)

fileio options:
  --file-num=N                  number of files to create [128]
  --file-block-size=N           block size to use in all IO operations [16384]
  --file-total-size=SIZE        total size of files to create [2G]
  --file-test-mode=STRING       test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
  --file-io-mode=STRING         file operations mode {sync,async,mmap} [sync]
  --file-async-backlog=N        number of asynchronous operatons to queue per thread [128]
  --file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
  --file-fsync-freq=N           do fsync() after this number of requests (0 - don‘t use fsync()) [100]
  --file-fsync-all[=on|off]     do fsync() after each write operation [off]
  --file-fsync-end[=on|off]     do fsync() at the end of test [on]
  --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]
  --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don‘t merge) [0]
  --file-rw-ratio=N             reads/writes ratio for combined test [1.5]
1)测试IO性能
[root@db01 ~]# sysbench fileio --file-num=5 --file-total-size=2G prepare
[root@db01 ~]# ll
总用量 2097200
-rw------- 1 root root 429506560 4月  15 21:21 test_file.0
-rw------- 1 root root 429506560 4月  15 21:21 test_file.1
-rw------- 1 root root 429506560 4月  15 21:21 test_file.2
-rw------- 1 root root 429506560 4月  15 21:21 test_file.3
-rw------- 1 root root 429506560 4月  15 21:21 test_file.4
[root@db01 ~]# sysbench --events=5000 --threads=16 fileio --file-num=5 --file-total-size=2G --file-test-mode=rndrw --file-fsync-freq=0 --file-block-size=16384 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

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


Extra file open flags: (none)
5 files, 409.6MiB each
2GiB total file size
Block size 16KiB
Number of IO requests: 5000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...

Threads started!


File operations:
    reads/s:                      4590.43
    writes/s:                     3067.95
    fsyncs/s:                     122.53

Throughput:         #吞吐量
    read, MiB/s:                  71.73  #表示读带宽
    written, MiB/s:               47.94  #表示写带宽

General statistics:
    total time:                          0.6517s
    total number of events:              5000

Latency (ms):
         min:                                    0.00
         avg:                                    1.61
         max:                                   43.35
         95th percentile:                        5.47
         sum:                                 8035.39

Threads fairness:
    events (avg/stddev):           312.5000/16.19
    execution time (avg/stddev):   0.5022/0.01
2)测试CPU性能
[root@db01 ~]# sysbench cpu --threads=40 --events=10000 --cpu-max-prime=20000 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

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


Prime numbers limit: 20000

Initializing worker threads...

Threads started!

CPU speed:
    events per second:   507.66

General statistics:
    total time:                          10.0370s
    total number of events:              5096

Latency (ms):
         min:                                    1.94
         avg:                                   77.06
         max:                                  775.00
         95th percentile:                      397.39
         sum:                               392676.67

Threads fairness:
    events (avg/stddev):           127.4000/2.05
    execution time (avg/stddev):   9.8169/0.12

MySQL压力测试工具

上一篇:《Linux内核设计与实现》之进程


下一篇:.NET&C#异步编程