最近需要对mysql进行压测,于是学习一下mysql自带的mysqlslap命令


启动Mysql

操作系统: Ubuntu18

我选用的是docker方式启动,因为只是本地测试学习,选的镜像是mysql的5.7.22版本

下载Docker镜像

caobo@caobo-server:~$ docker pull mysql:5.7.22
5.7.22: Pulling from library/mysql
be8881be8156: Pull complete
c3995dabd1d7: Pull complete
9931fdda3586: Pull complete
be38c1ab6adb: Pull complete
0e4cc8d0eef6: Pull complete
076630dca16e: Pull complete
c3cdaa36d52c: Pull complete
d91287b16b88: Pull complete
58ce724b2692: Pull complete
de12957e6222: Pull complete
b7aa5badca43: Pull complete
Digest: sha256:8f96d261d3ae48f2f62ec6510ccd45ae0eae2f2c9192cfcec35f4af5884cdfcf
Status: Downloaded newer image for mysql:5.7.22
caobo@caobo-server:~$ docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mysql               5.7.22              c356247174ed        2 days ago          372MB

安装mysql的client

caobo@caobo-server:~$ sudo apt install mysql-client

启动容器

密码是123456

caobo@caobo-server:~$ docker run --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.22
0757a0409615d21accd04b5f7c178a776a33c7aee7c2c278ed961fea9cb14a73

检查启动情况

caobo@caobo-server:~$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
0757a0409615        mysql:5.7.22        "docker-entrypoint.s…"   34 seconds ago      Up 33 seconds       0.0.0.0:3306->3306/tcp   mysql-test

尝试连接mysql

caobo@caobo-server:~$ mysql -h 127.0.0.1 -P 3306 -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

看到上面的信息表示启动成功

使用mysqlslap命令

最简单的用法

参数 作用
-h host
-P 端口
-p 密码
-a 自动生成sql
-c 指定client数量
- -only-print 只打印,不执行
caobo@caobo-server:~$ mysqlslap -h 127.0.0.1 -P 3306 -u root -p123456 -a
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 0.086 seconds
        Minimum number of seconds to run all queries: 0.086 seconds
        Maximum number of seconds to run all queries: 0.086 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

使用sysbench

1. 安装

Ubuntu可以用apt安装

caobo@caobo-server:~$ sudo apt install sysbench

2. 全局参数

参数 作用
–threads=N 使用线程数
–time=N 执行时间,单位是秒

3. Mysql相关参数

参数 作用
- -mysql-host=[LIST,…] host
- -mysql-port=[LIST,…] port
- -mysql-db=STRING 使用的数据库
- -mysql-user=STRING 用户名
- -mysql-password=STRING 密码

4. oltp相关参数

需要指定lua脚本才能用help命令查看参数

caobo@caobo-server:~$ sysbench /usr/share/sysbench/oltp_read_write.lua help
参数 作用
–tables=N 表的数量
–table_size=N 每个表的记录数量

5. 执行测试

prepare 准备数据

caobo@caobo-server:~$ sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test \
--mysql-user=root --mysql-password=123456 --table_size=5000000 \
--tables=2 --threads=30 --time=20 --report-interval=10 \
--db-driver=mysql prepare

sysbench 1.0.11 (using system LuaJIT 2.1.0-beta3)

Initializing worker threads...

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

run 执行

caobo@caobo-server:~$ sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test \
--mysql-user=root --mysql-password=123456 --table_size=5000000 \
--tables=2 --threads=300 --time=20 --report-interval=10 \
--db-driver=mysql run

sysbench 1.0.11 (using system LuaJIT 2.1.0-beta3)

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


Initializing worker threads...

Threads started!

[ 10s ] thds: 30 tps: 225.58 qps: 4547.49 (r/w/o: 3189.84/903.50/454.15) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 30 tps: 166.60 qps: 3346.85 (r/w/o: 2340.07/673.59/333.20) lat (ms,95%): 467.30 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            55342
        write:                           15812
        other:                           7906
        total:                           79060
    transactions:                        3953   (193.77 per sec.)
    queries:                             79060  (3875.47 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.3947s
    total number of events:              3953

Latency (ms):
         min:                                  7.85
         avg:                                152.88
         max:                               1722.97
         95th percentile:                    397.39
         sum:                             604336.98

Threads fairness:
    events (avg/stddev):           131.7667/5.09
    execution time (avg/stddev):   20.1446/0.13

cleanup 清除数据

caobo@caobo-server:~$ sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test \
--mysql-user=root --mysql-password=123456 --table_size=5000000 \
--tables=2 --threads=300 --time=20 --report-interval=10 \
--db-driver=mysql cleanup
sysbench 1.0.11 (using system LuaJIT 2.1.0-beta3)

Dropping table 'sbtest1'...
Dropping table 'sbtest2'...

到此简单的mysql压力测试就完成!今后可以尝试更多复杂的测试,这次只是一个入门