简介：


mysqlslap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server. mysqlslap is available as of MySQL 5.1.4.


官方文档语法：http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html


测试：

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqlslap --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FR

OM a" --concurrency=50 --iterations=200 -uroot -pw123123

Benchmark

    Average number of seconds to run all queries: 0.133 seconds

    Minimum number of seconds to run all queries: 0.046 seconds

    Maximum number of seconds to run all queries: 1.219 seconds

    Number of clients running queries: 50

    Average number of queries per client: 1



C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqlslap --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FR

OM a" --concurrency=50 --iterations=200 -uroot -pw123123 --debug-info

Benchmark

    Average number of seconds to run all queries: 0.116 seconds

    Minimum number of seconds to run all queries: 0.046 seconds

    Maximum number of seconds to run all queries: 1.109 seconds

    Number of clients running queries: 50

    Average number of queries per client: 1



开启另外一个客户端窗口：


mysql> show databases;

+--------------------+

| Database      |

+--------------------+

| information_schema |

| backup       |

| #mysql50#bin-log  |

| mysql        |

| performance_schema |

| phpmyadmin     |

| test        |

| test01       |

+--------------------+

8 rows in set (0.08 sec)


mysql> show databases;

+--------------------+

| Database      |

+--------------------+

| information_schema |

| backup       |

| #mysql50#bin-log  |

| mysql        |

| mysqlslap      |

| performance_schema |

| phpmyadmin     |

| test        |

| test01       |

+--------------------+

9 rows in set (0.02 sec)


mysql> use mysqlslap

Database changed

mysql> show tables;

+---------------------+

| Tables_in_mysqlslap |

+---------------------+

| t1          |

+---------------------+

1 row in set (0.00 sec)


mysql> select * from t1;

+---------+--------------------------------------------------------------------------------------------------------------------------------+

| intcol1 | charcol1                                                            |

+---------+--------------------------------------------------------------------------------------------------------------------------------+

|    41 | nagT4IIMk551R1Bv2RcrO2ZsMLwebn6BI9snJvuNr2ZZkLXtSnkMxxJNLpyGbqiomg24SQlq9Aq6LZ89NOacKmwBSOfQjHyei9hZbJIOWxQt6h5ksW9HZxCmWhcbnF |

|  15574 | BsqAL4mARB7xdnZ35Tpeho2mQAZ80BibAzOH83vL2qXwEgcLofo41utJIe087n8DojnLWHDaTwbrKTJwy3kSOTp2FPZ3jRaN80II0wehTepdCiysezAHeBFSGr2uYF |

|  20328 | QMQbtpiOchI2r1zHcsTbYKq3phoL9FjMP2PSmxxsYTuhTw9SC5vEp1YP0yZz3JG7ppd6pDbxGQP8Mbuu1wXQNtvlDC8Y5tc1y6M14c2p1FqDQkzCfZkuq59mZa28e7 |

|   9905 | IB2PHaEOuECzDqndIHDqNSY5iKt72Jnlj4jYhLjCltlBYM2QkgnKZEqAvtXKwnPAKEvOzPwaeMXQyZXkyOzsgRg6AHAbcnjIO9Mml7xEl7p8jK2M3wH1v54gMGqmXr |

|   6411 | nZgWk8jKX2T0DyyPnH0IJNgdkx8t71q0oZOP0XOvGCAjPcw6x6dxrpLw2dYxc5h39p5JscbtikausdTYNRNBvnove32uX1Ff5hq6EPjWyJLDEHEcDcP2oh0DLvF22B |

|  30877 | OeCNcDKDbYeAxsYbTB0S5Ax9APZurgOebzYl413pFSCxTc17nLTgZELHvjtsOLsfWS74v6IAnRONhj6NAX90kizZXkIsW5vf3MeROSXktb5YQITMz5Tkl0P8yW13Cs |

|  27152 | 7A3L3NnKTblS3Ro3dT391S28KhIE8mWT3A0S7cNb65ID94S3zdmOFeQXPFhpGkbO2bZ2wosQQD94lmsvZ5GchaucswyMbFrqLlXxmAWMaZZwCS2ZujENcSOHDpSgKI |

|  16858 | qbhONG3TMLpoMZE02NrpefozX3l1kYlte92yHqLuS1zDF9d9y0rkxFFFWfY82a5A4ugvX1vzAeZ8dCG9DohAj3Sky0XPErt7tF85GTWI9D6Kc9RSf4L56cG6mnLkKm |

|   6021 | ML4TFMtO1lMjr6SIMn7LsGqup4cqtwsaP88rYXuze24bY9fyNI6JemGMteM6YHPRy2RIqCKIfsoM0vF2RGdedSrwq95ypgKgRMbbChEImE689y3Fb1vF8aW5kxi1k9 |

|  17469 | W3Wt5qxGdIZGuOXTvlHkHjkAoSL5YDYac6GC90EfjXL9JlPPGAvTOmZu90FZpLYSyromAiuJAYiBF4akMvKuC1Mnd9lFuWNaLsYfoYiRobo3zakQzy5m6gRs7YGERh |

|    41 | nagT4IIMk551R1Bv2RcrO2ZsMLwebn6BI9snJvuNr2ZZkLXtSnkMxxJNLpyGbqiomg24SQlq9Aq6LZ89NOacKmwBSOfQjHyei9hZbJIOWxQt6h5ksW9HZxCmWhcbnF |

|  15574 | BsqAL4mARB7xdnZ35Tpeho2mQAZ80BibAzOH83vL2qXwEgcLofo41utJIe087n8DojnLWHDaTwbrKTJwy3kSOTp2FPZ3jRaN80II0wehTepdCiysezAHeBFSGr2uYF |

|  20328 | QMQbtpiOchI2r1zHcsTbYKq3phoL9FjMP2PSmxxsYTuhTw9SC5vEp1YP0yZz3JG7ppd6pDbxGQP8Mbuu1wXQNtvlDC8Y5tc1y6M14c2p1FqDQkzCfZkuq59mZa28e7 |

|   9905 | IB2PHaEOuECzDqndIHDqNSY5iKt72Jnlj4jYhLjCltlBYM2QkgnKZEqAvtXKwnPAKEvOzPwaeMXQyZXkyOzsgRg6AHAbcnjIO9Mml7xEl7p8jK2M3wH1v54gMGqmXr |

|   6411 | nZgWk8jKX2T0DyyPnH0IJNgdkx8t71q0oZOP0XOvGCAjPcw6x6dxrpLw2dYxc5h39p5JscbtikausdTYNRNBvnove32uX1Ff5hq6EPjWyJLDEHEcDcP2oh0DLvF22B |

|  30877 | OeCNcDKDbYeAxsYbTB0S5Ax9APZurgOebzYl413pFSCxTc17nLTgZELHvjtsOLsfWS74v6IAnRONhj6NAX90kizZXkIsW5vf3MeROSXktb5YQITMz5Tkl0P8yW13Cs |

|  27152 | 7A3L3NnKTblS3Ro3dT391S28KhIE8mWT3A0S7cNb65ID94S3zdmOFeQXPFhpGkbO2bZ2wosQQD94lmsvZ5GchaucswyMbFrqLlXxmAWMaZZwCS2ZujENcSOHDpSgKI |

|  16858 | qbhONG3TMLpoMZE02NrpefozX3l1kYlte92yHqLuS1zDF9d9y0rkxFFFWfY82a5A4ugvX1vzAeZ8dCG9DohAj3Sky0XPErt7tF85GTWI9D6Kc9RSf4L56cG6mnLkKm |

|   6021 | ML4TFMtO1lMjr6SIMn7LsGqup4cqtwsaP88rYXuze24bY9fyNI6JemGMteM6YHPRy2RIqCKIfsoM0vF2RGdedSrwq95ypgKgRMbbChEImE689y3Fb1vF8aW5kxi1k9 |

|  17469 | W3Wt5qxGdIZGuOXTvlHkHjkAoSL5YDYac6GC90EfjXL9JlPPGAvTOmZu90FZpLYSyromAiuJAYiBF4akMvKuC1Mnd9lFuWNaLsYfoYiRobo3zakQzy5m6gRs7YGERh |

|    41 | nagT4IIMk551R1Bv2RcrO2ZsMLwebn6BI9snJvuNr2ZZkLXtSnkMxxJNLpyGbqiomg24SQlq9Aq6LZ89NOacKmwBSOfQjHyei9hZbJIOWxQt6h5ksW9HZxCmWhcbnF |

|  15574 | BsqAL4mARB7xdnZ35Tpeho2mQAZ80BibAzOH83vL2qXwEgcLofo41utJIe087n8DojnLWHDaTwbrKTJwy3kSOTp2FPZ3jRaN80II0wehTepdCiysezAHeBFSGr2uYF |

|  20328 | QMQbtpiOchI2r1zHcsTbYKq3phoL9FjMP2PSmxxsYTuhTw9SC5vEp1YP0yZz3JG7ppd6pDbxGQP8Mbuu1wXQNtvlDC8Y5tc1y6M14c2p1FqDQkzCfZkuq59mZa28e7 |

|   9905 | IB2PHaEOuECzDqndIHDqNSY5iKt72Jnlj4jYhLjCltlBYM2QkgnKZEqAvtXKwnPAKEvOzPwaeMXQyZXkyOzsgRg6AHAbcnjIO9Mml7xEl7p8jK2M3wH1v54gMGqmXr |

|   6411 | nZgWk8jKX2T0DyyPnH0IJNgdkx8t71q0oZOP0XOvGCAjPcw6x6dxrpLw2dYxc5h39p5JscbtikausdTYNRNBvnove32uX1Ff5hq6EPjWyJLDEHEcDcP2oh0DLvF22B |

|  30877 | OeCNcDKDbYeAxsYbTB0S5Ax9APZurgOebzYl413pFSCxTc17nLTgZELHvjtsOLsfWS74v6IAnRONhj6NAX90kizZXkIsW5vf3MeROSXktb5YQITMz5Tkl0P8yW13Cs |

|  27152 | 7A3L3NnKTblS3Ro3dT391S28KhIE8mWT3A0S7cNb65ID94S3zdmOFeQXPFhpGkbO2bZ2wosQQD94lmsvZ5GchaucswyMbFrqLlXxmAWMaZZwCS2ZujENcSOHDpSgKI |

|  16858 | qbhONG3TMLpoMZE02NrpefozX3l1kYlte92yHqLuS1zDF9d9y0rkxFFFWfY82a5A4ugvX1vzAeZ8dCG9DohAj3Sky0XPErt7tF85GTWI9D6Kc9RSf4L56cG6mnLkKm |

|   6021 | ML4TFMtO1lMjr6SIMn7LsGqup4cqtwsaP88rYXuze24bY9fyNI6JemGMteM6YHPRy2RIqCKIfsoM0vF2RGdedSrwq95ypgKgRMbbChEImE689y3Fb1vF8aW5kxi1k9 |

|  17469 | W3Wt5qxGdIZGuOXTvlHkHjkAoSL5YDYac6GC90EfjXL9JlPPGAvTOmZu90FZpLYSyromAiuJAYiBF4akMvKuC1Mnd9lFuWNaLsYfoYiRobo3zakQzy5m6gRs7YGERh |

|    41 | nagT4IIMk551R1Bv2RcrO2ZsMLwebn6BI9snJvuNr2ZZkLXtSnkMxxJNLpyGbqiomg24SQlq9Aq6LZ89NOacKmwBSOfQjHyei9hZbJIOWxQt6h5ksW9HZxCmWhcbnF |

|  15574 | BsqAL4mARB7xdnZ35Tpeho2mQAZ80BibAzOH83vL2qXwEgcLofo41utJIe087n8DojnLWHDaTwbrKTJwy3kSOTp2FPZ3jRaN80II0wehTepdCiysezAHeBFSGr2uYF |

|  20328 | QMQbtpiOchI2r1zHcsTbYKq3phoL9FjMP2PSmxxsYTuhTw9SC5vEp1YP0yZz3JG7ppd6pDbxGQP8Mbuu1wXQNtvlDC8Y5tc1y6M14c2p1FqDQkzCfZkuq59mZa28e7 |

|   9905 | IB2PHaEOuECzDqndIHDqNSY5iKt72Jnlj4jYhLjCltlBYM2QkgnKZEqAvtXKwnPAKEvOzPwaeMXQyZXkyOzsgRg6AHAbcnjIO9Mml7xEl7p8jK2M3wH1v54gMGqmXr |

|   6411 | nZgWk8jKX2T0DyyPnH0IJNgdkx8t71q0oZOP0XOvGCAjPcw6x6dxrpLw2dYxc5h39p5JscbtikausdTYNRNBvnove32uX1Ff5hq6EPjWyJLDEHEcDcP2oh0DLvF22B |

|  30877 | OeCNcDKDbYeAxsYbTB0S5Ax9APZurgOebzYl413pFSCxTc17nLTgZELHvjtsOLsfWS74v6IAnRONhj6NAX90kizZXkIsW5vf3MeROSXktb5YQITMz5Tkl0P8yW13Cs |

|  27152 | 7A3L3NnKTblS3Ro3dT391S28KhIE8mWT3A0S7cNb65ID94S3zdmOFeQXPFhpGkbO2bZ2wosQQD94lmsvZ5GchaucswyMbFrqLlXxmAWMaZZwCS2ZujENcSOHDpSgKI |

|  16858 | qbhONG3TMLpoMZE02NrpefozX3l1kYlte92yHqLuS1zDF9d9y0rkxFFFWfY82a5A4ugvX1vzAeZ8dCG9DohAj3Sky0XPErt7tF85GTWI9D6Kc9RSf4L56cG6mnLkKm |

|   6021 | ML4TFMtO1lMjr6SIMn7LsGqup4cqtwsaP88rYXuze24bY9fyNI6JemGMteM6YHPRy2RIqCKIfsoM0vF2RGdedSrwq95ypgKgRMbbChEImE689y3Fb1vF8aW5kxi1k9 |

|  17469 | W3Wt5qxGdIZGuOXTvlHkHjkAoSL5YDYac6GC90EfjXL9JlPPGAvTOmZu90FZpLYSyromAiuJAYiBF4akMvKuC1Mnd9lFuWNaLsYfoYiRobo3zakQzy5m6gRs7YGERh |

+---------+--------------------------------------------------------------------------------------------------------------------------------+

40 rows in set (0.00 sec)


mysql> select * from t1;

ERROR 1146 (42S02): Table 'mysqlslap.t1' doesn't exist


总结：从上述的实验中可以得出一个结论，测试时会创建数据库和插入数据并查询，完成测试后就好把整个测试数据库删除！！！


下边我们在做几个测试了解测试脚本是怎么写的：

1、仅仅打印执行脚本信息：


C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqlslap.exe -a  --only-print

DROP SCHEMA IF EXISTS `mysqlslap`;

CREATE SCHEMA `mysqlslap`;

use mysqlslap;

CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));

INSERT INTO t1 VALUES (41,'nagT4IIMk551R1Bv2RcrO2ZsMLwebn6BI9snJvuNr2ZZkLXtSnkMxxJNLpyGbqiomg24SQlq9Aq6LZ89NOacKmwBSOfQjHyei9hZbJIOWxQt6h5ksW9HZxCmWhcbnF');

。。。。。。。


DROP SCHEMA IF EXISTS `mysqlslap`;

C:\Program Files\MySQL\MySQL Server 5.5\bin>


可以清楚的看到执行语句和过程！！！！ 此时插入的数据是一列数字类型和一列字符类型的数据，要更改这个要用到几个参数：


–number-char-cols=N, -x N
自动生成的测试表中包含多少个字符类型的列，默认1

–number-int-cols=N, -y N
自动生成的测试表中包含多少个数字类型的列，默认1

了解了参数的意思之后，我们可以尽心一个小小的测试：

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqlslap.exe -a -y 2 -x 2 --only-print

拿出一条记录分析一下：

INSERT INTO t1 VALUES (6879,32301,'YckdqeC41gKjG17z8qsJez7O7dFDprMJchLu9ywTd4y25eYPcLfNnXt2KcoJ9tJXr3keC4fhatJupvM1GHYPXIqRvnkizLokuo1sSucdt1lIvus6GO6hkKuww75eXL','PrXJ0xcNkO5H2mb6sTml3Chmt4TqPm5qwvDj

CwwWGsWqvsAbGLgOTEq26GOqHMmGS5LWW21ZeEZF0yRKPNlctACTJNHpZzDgYP1ryjDRacHrpk6X2IMFnHyOMqmlgP');


可以很清楚的看到插入的数据是2列数字类型和2列字符类型的数据！


执行自己的sql语句：

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqlslap.exe -c 300 --create-schema=test --query="select * from userinfo" -uroot -pw123123

Benchmark

    Average number of seconds to run all queries: 0.516 seconds

    Minimum number of seconds to run all queries: 0.516 seconds

    Maximum number of seconds to run all queries: 0.516 seconds

    Number of clients running queries: 300

 

    Average number of queries per client: 1