每日MySQL之012：客户端负载模拟工具mysqlslap

mysqlslap是一个客户端程序，用来模拟客户端负载，它能报告每个阶段执行的时间。

语法如下：
shell> mysqlslap [options]

有些选项，比如--create或者--query能让你指定一个SQL字符串或者指定一个文件，文件中包含了要执行的语句。

mysqlslap有三个阶段：
--1. 创建schema，表等测试用的数据。这个阶段只用一个client连接。
--2. 负载测试。这个阶段使用很多的client连接
--3. 清理阶段。这个阶段只用一个client连接。

示例1：
使用自己的创建和查询SQL语句，使用50个client，每个client做200次查询:
root@db2a:~# mysqlslap -u root -pqingsong --delimiter=";"  --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FROM a" --concurrency=50 --iterations=200
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 0.015 seconds
        Minimum number of seconds to run all queries: 0.013 seconds
        Maximum number of seconds to run all queries: 0.139 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1

db2b上client连接到db2a上：
root@db2b:~# mysqlslap -u root -h db2a -pqingsong --delimiter=";"  --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FROM a" --concurrency=50 --iterations=200
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 0.661 seconds
        Minimum number of seconds to run all queries: 0.606 seconds
        Maximum number of seconds to run all queries: 0.797 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1

可以看到，由于db2b上测试的时候，和db2a不在同一台机器上，性能很差。

示例2：
让 mysqlslap 自己创建表，表中有两个 INT 列和3个 VARCHAR 列。使用5个client并发查询，每个client查询20次。
root@db2a:~# mysqlslap -u root -p --concurrency=5 --iterations=20 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql
Enter password: 
Benchmark
        Average number of seconds to run all queries: 0.047 seconds
        Minimum number of seconds to run all queries: 0.029 seconds
        Maximum number of seconds to run all queries: 0.122 seconds
        Number of clients running queries: 5
        Average number of queries per client: 0

Tell the program to load the create, insert, and query SQL statements from the specified files, where the create.sql file has multiple table creation statements delimited by ';' and multiple insert statements delimited by ';'. The --query file will have multiple queries delimited by ';'. Run all the load statements, then run all the queries in the query file with five clients (five times each):

示例3：
从文件中加载create、insert和select语句。create.sql有多个语句，使用';'分开。query.sql有多个查询语句，用';'分开。并发5，每个client查询10次
root@db2a:~# cat create.sql 
create table t1(id int, name char(20));
insert into t1 values(100,'aaa');
insert into t1 values(200,'bbb');

root@db2a:~# cat query.sql 
select name from t1;
select id from t1;

root@db2a:~# mysqlslap -p --concurrency=5 --iterations=10 --query=query.sql --create=create.sql --delimiter=";"
Enter password: 
Benchmark
        Average number of seconds to run all queries: 0.002 seconds
        Minimum number of seconds to run all queries: 0.002 seconds
        Maximum number of seconds to run all queries: 0.002 seconds
        Number of clients running queries: 5
        Average number of queries per client: 2

示例4：
只看看测试的时候做的什么工作，不实际执行。可以看到，iterations=3，这些测试一共进行了三轮。concurrency=2,但每一轮的create和insert只执行一遍，select执行两遍，这正说明了三个阶段中，只有第二个阶段使用很多的client连接:
root@db2a:~# mysqlslap -p --concurrency=2 --iterations=3 --query=query.sql --create=create.sql --delimiter=";" --only-print
Enter password: 
DROP SCHEMA IF EXISTS `mysqlslap`;
CREATE SCHEMA `mysqlslap`;
use mysqlslap;
create table t1(id int, name char(20));
insert into t1 values(100,'aaa');
insert into t1 values(200,'bbb');
select name from t1;
select id from t1;
select name from t1;
select id from t1;
DROP SCHEMA IF EXISTS `mysqlslap`;
CREATE SCHEMA `mysqlslap`;
use mysqlslap;
create table t1(id int, name char(20));
insert into t1 values(100,'aaa');
insert into t1 values(200,'bbb');
select name from t1;
select id from t1;
select name from t1;
select id from t1;
DROP SCHEMA IF EXISTS `mysqlslap`;
CREATE SCHEMA `mysqlslap`;
use mysqlslap;
create table t1(id int, name char(20));
insert into t1 values(100,'aaa');
insert into t1 values(200,'bbb');
select name from t1;
select id from t1;
select name from t1;
select id from t1;
DROP SCHEMA IF EXISTS `mysqlslap`;