使用mysql 自带的mysqlslap （mysql仿真客户端）做“简单”的mysql性能测试、压力测试

原创 于 2019-09-15 03:39:40 发布 · 511 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql优化 #mysql压力测试 #mysql性能 #mysqlslap使用方法

本文介绍了如何使用mysqlslap工具进行MySQL的性能和压力测试。通过模拟多个客户端连接，测试新服务器、硬件升级后的数据库性能，并探讨了参数调优的效果。文章详细阐述了测试的思路，包括利用Excel生成测试脚本，创建测试表，插入大量数据，以及执行自定义SQL语句。最后，作者分享了一个测试用的Excel模板链接，供读者下载参考。

为什么要测试：

  1. 新装的服务器，为了模拟将来业务达到一定程度时数据库大致的一个性能状况。
  2. 服务器进行了硬件升级，性能比原来快了么。
  3. mysql进行参数调优，到底什么才是比较优的。

mysqlslap是一个诊断程序，旨在模拟MySQL服务器的客户端负载并报告每个阶段的时间。它就像多个客户端正在访问服务器一样。

mysqlslap分三个阶段运行：

  1. 创建架构，表以及可选的任何存储程序或数据以用于测试。此阶段使用单个客户端连接。
  2. 运行负载测试。此阶段可以使用许多客户端连接。
  3. 清理（​​如果指定，则断开连接，删除表）。此阶段使用单个客户端连接。

思路：使用excel快速生成可测试的sql语句去测试。

使用excel配置参数过程中，应充分考虑生产中的实际并发与语句执行情况后进行配置。我在测试过程中，模拟500个客户端，请求五千次左右。

↑上图excel很简单，通过公式，生成可执行脚本，然后复制到SSH命令行↓

执行结果查看↓

-------------↑以上是针对数据库自动测试的一个思路，还可以自己建表，用自己的sql语句进行测试------------------------------------

以下是自己建表手动测试，mysqlslap也能自己建表，但他每次都删，不便于我们观察，我们自己创建一些表，然后使用自己的sql来进行测试。

创建2个表，class表插入2000条数据，person表插入10000000（一千万）条数据。插入数据用mysql的函数。person表中通过classid进行关联。然后写出带左连接的语句进行测试。

创建class表：

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `C1` varchar(255) DEFAULT NULL,
  `C2` varchar(255) DEFAULT NULL,
  `C3` varchar(255) DEFAULT NULL,
  `C4` varchar(255) DEFAULT NULL,
  `C5` varchar(255) DEFAULT NULL,
  `C6` varchar(255) DEFAULT NULL,
  `C7` varchar(255) DEFAULT NULL,
  `C8` varchar(255) DEFAULT NULL,
  `C9` varchar(255) DEFAULT NULL,
  `C10` text,
  `C11` text,
  `C12` text,
  `C13` text,
  `C14` text,
  `C15` text,
  `C16` text,
  `C17` text,
  `C18` text,
  `C19` text,
  `C20` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=1;

创建person表：

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `classid` bigint(20) NOT NULL,
  `fname` varchar(200) NOT NULL,
  `lname` varchar(1000) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `sex` tinyint(1) unsigned NOT NULL,
  `i1` float(255,2) DEFAULT NULL,
  `i2` float(255,2) DEFAULT NULL,
  `i3` float(255,2) DEFAULT NULL,
  `i4` float(255,2) DEFAULT NULL,
  `i5` float(255,2) DEFAULT NULL,
  `i6` float(255,2) DEFAULT NULL,
  `i7` float(255,2) DEFAULT NULL,
  `i8` float(255,2) DEFAULT NULL,
  `i9` float(255,2) DEFAULT NULL,
  `i10` float(255,2) DEFAULT NULL,
  `t1` text,
  `t2` text,
  `t3` text,
  `t4` text,
  `t5` text,
  `v1` varchar(255) DEFAULT NULL,
  `v2` varchar(255) DEFAULT NULL,
  `v3` varchar(255) DEFAULT NULL,
  `v4` varchar(255) DEFAULT NULL,
  `v5` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=1;

插入class表2000条：

通过函数

BEGIN  
    DECLARE i INT DEFAULT 1;  
    WHILE i<=2000 DO  
        INSERT INTO class(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20) 
				VALUES(CONCAT('测试数据',FLOOR(RAND()*100)),CONCAT('测试数据',FLOOR(RAND()*100)),CONCAT('测试数据',FLOOR(RAND()*100)),CONCAT('测试数据',FLOOR(RAND()*100)),CONCAT('测试数据',FLOOR(RAND()*100)),CONCAT('测试数据',FLOOR(RAND()*100)),CONCAT('测试数据',FLOOR(RAND()*100)),CONCAT('测试数据',FLOOR(RAND()*100)),CONCAT('测试数据',FLOOR(RAND()*100)),
				CONCAT('测试数据',FLOOR(RAND()*99999999)),CONCAT('测试数据',FLOOR(RAND()*99999999)),CONCAT('测试数据',FLOOR(RAND()*99999999)),CONCAT('测试数据',FLOOR(RAND()*99999999)),CONCAT('测试数据',FLOOR(RAND()*99999999)),CONCAT('测试数据',FLOOR(RAND()*99999999)),CONCAT('测试数据',FLOOR(RAND()*99999999)),CONCAT('测试数据',FLOOR(RAND()*99999999)),CONCAT('测试数据',FLOOR(RAND()*99999999)),CONCAT('测试数据',FLOOR(RAND()*99999999)),CONCAT('测试数据',FLOOR(RAND()*99999999)));
        SET i = i+1;  
    END WHILE;  
END

插入person表10000000（一千万）条：

注意，是有参数输入的↑

插入脚本：

BEGIN   
	DECLARE chars varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	DECLARE fname VARCHAR(25) DEFAULT '';
	DECLARE lname VARCHAR(25) DEFAULT '';
	DECLARE id int UNSIGNED;
	DECLARE len int;
	set id=1;
	WHILE id <= num DO
		set len = FLOOR(1 + RAND()*25);
		set fname = '';
		WHILE len > 0 DO
			SET fname = CONCAT(fname,substring(chars,FLOOR(1 + RAND()*62),1));
			SET len = len - 1;
		END WHILE;
		set len = FLOOR(1+RAND()*25);
		set lname = '';
		WHILE len > 0 DO
			SET lname = CONCAT(lname,substring(chars,FLOOR(1 + RAND()*62),1));
			SET len = len - 1;
		END WHILE;
		INSERT INTO person (`classid`, `fname`, `lname`, `age`, `sex`, `i1`, `i2`, `i3`, `i4`, `i5`, `i6`, `i7`, `i8`, `i9`, `i10`, `t1`, `t2`, `t3`, `t4`, `t5`, `v1`, `v2`, `v3`, `v4`, `v5`) 
				VALUES (FLOOR(1+ RAND()*2000),fname,lname, FLOOR(RAND()*100), FLOOR(RAND()*2),
					FLOOR(RAND()*99999999),FLOOR(RAND()*99999999),FLOOR(RAND()*99999999),FLOOR(RAND()*99999999),FLOOR(RAND()*99999999),
					FLOOR(RAND()*99999999),FLOOR(RAND()*99999999),FLOOR(RAND()*99999999),FLOOR(RAND()*99999999),FLOOR(RAND()*99999999),
					lname,lname,lname,lname,lname,fname,fname,fname,fname,fname);

		set id = id + 1;
	END WHILE;
END

 

运行函数↓

配置是2核4G，插入一千万条从18:06运行到22:34，需要几个小时吧，具体count（*）结果↓ 大概一千一百万条左右。

以上准备测试数据和表已经结束。

还是万能的excel ，助我生成sql。

执行的sql是：

select c.*,p.* from class c,person p where p.id>3000000 and p.id<3000100 and p.classid=c.id;
select c.*,p.* from class c,person p where p.id>8000000 and p.id<8000100 and p.classid=c.id;

生成的脚本，性能测试语句是：

mysqlslap  -uroot -p1qaz2wsxadmin --create-schema=test-abc --debug-info --concurrency=500 --number-of-queries=5000 --commit=5 --detach=10 --iterations=5 --engine=innodb --delimiter=';' --query='select c.*,p.* from class c,person p where p.id>3000000 and p.id<3000100 and p.classid=c.id;select c.*,p.* from class c,person p where p.id>8000000 and p.id<8000100 and p.classid=c.id;'

测试结果：

[root@iZuhlfyunylhh1Z ~]# mysqlslap  -uroot -p1qaz2wsxadmin --create-schema=test-abc --debug-info --concurrency=500 --number-of-queries=5000 --commit=5 --detach=10 --iterations=5 --engine=innodb --delimiter=';' --query='select c.*,p.* from class c,person p where p.id>3000000 and p.id<3000100 and p.classid=c.id;select c.*,p.* from class c,person p where p.id>8000000 and p.id<8000100 and p.classid=c.id;'
Warning: Using a password on the command line interface can be insecure.
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.765 seconds
        Minimum number of seconds to run all queries: 0.665 seconds
        Maximum number of seconds to run all queries: 0.893 seconds
        Number of clients running queries: 500
        Average number of queries per client: 10


User time 2.97, System time 1.36
Maximum resident set size 20300, Integral resident set size 0
Non-physical pagefaults 36735, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 105281, Involuntary context switches 3

测试结果，在一千万条数据的表中，500个用户，对数据库查询5次，每次找出100条数据，每条耗时约0.7秒。

可以通过在my.cnf中不断优化mysql配置后，来检验此测试结果的变化。

本文的测试excel，可以在优快云下载，搜索：mysqlslap 性能测试脚本自动生成测试性能.xlsx

链接地址：https://download.youkuaiyun.com/download/colcool/11745630

没积分的就自己手写或者自己做一个吧。

奉上 mysqlslap选项

Option Name描述介绍
--auto-generate-sql在文件中未提供或使用命令选项时自动生成SQL语句 
--auto-generate-sql-add-autoincrement将AUTO_INCREMENT列添加到自动生成的表中 
--auto-generate-sql-execute-number指定自动生成的查询数 
--auto-generate-sql-guid-primary将基于GUID的主键添加到自动生成的表 
--auto-generate-sql-load-type指定测试负载类型 
--auto-generate-sql-secondary-indexes指定要添加到自动生成的表的二级索引数 
--auto-generate-sql-unique-query-number为自动测试生成多少个不同的查询。 
--auto-generate-sql-unique-write-number为--auto-generate-sql-write-number生成多少个不同的查询 
--auto-generate-sql-write-number每个线程要执行多少行插入 
--commit提交前要执行的语句数。 
--compress压缩客户端和服务器之间发送的所有信息 
--concurrency发出SELECT语句时要模拟的客户端数 
--create包含用于创建表的语句的文件或字符串 
--create-schema用于运行测试的模式 
--csv以逗号分隔值格式生成输出 
--debug写调试日志 
--debug-check程序退出时打印调试信息 
--debug-info程序退出时打印调试信息，内存和CPU统计信息 
--default-auth要使用的身份验证插件5.6.2
--defaults-extra-file除常用选项文件外，还可以读取命名选项文件 
--defaults-file只读命名选项文件 
--defaults-group-suffix选项组后缀值 
--delimiter在SQL语句中使用的分隔符 
--detach在每个N语句之后分离（关闭并重新打开）每个连接 
--enable-cleartext-plugin启用明文身份验证插件5.6.7
--engine用于创建表的存储引擎 
--help显示帮助消息并退出 
--hostMySQL服务器所在的主机 
--iterations运行测试的次数 
--login-path从.mylogin.cnf中读取登录路径选项5.6.6
--no-defaults不读选项文件 
--no-drop不要删除在测试运行期间创建的任何模式5.6.3
--number-char-cols如果指定了--auto-generate-sql，则使用的VARCHAR列数 
--number-int-cols如果指定了--auto-generate-sql，则使用的INT列数 
--number-of-queries将每个客户端限制为大约此数量的查询 
--only-print不要连接到数据库。mysqlslap只打印它本来会做的事情 
--password连接到服务器时使用的密码 
--pipe使用命名管道连接到服务器（仅限Windows） 
--plugin-dir安装插件的目录5.6.2
--port用于连接的TCP / IP端口号 
--post-query包含在测试完成后要执行的语句的文件或字符串 
--post-system测试完成后使用system（）执行的字符串 
--pre-query包含在运行测试之前要执行的语句的文件或字符串 
--pre-system在运行测试之前使用system（）执行的字符串 
--print-defaults打印默认选项 
--protocol要使用的连接协议 
--query包含用于检索数据的SELECT语句的文件或字符串 
--secure-auth不要以旧的（4.1之前的）格式向服务器发送密码5.6.17
--shared-memory-base-name用于共享内存连接的共享内存的名称 
--silent静音模式 
--socketUnix套接字文件或Windows命名管道使用 
--ssl启用连接加密 
--ssl-ca包含受信任的SSL证书颁发机构列表的文件 
--ssl-capath包含受信任的SSL证书颁发机构证书文件的目录 
--ssl-cert包含X.509证书的文件 
--ssl-cipher用于连接加密的允许密码 
--ssl-crl包含证书吊销列表的文件5.6.3
--ssl-crlpath包含证书吊销列表文件的目录5.6.3
--ssl-key包含X.509密钥的文件 
--ssl-mode期望的与服务器连接的安全状态30年6月5日
--ssl-verify-server-cert根据服务器证书Common Name身份验证主机名 
--user连接到服务器时使用的MySQL用户名 
--verbose详细模式 
--version显示版本信息并退出 

 

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值