emysql 压力测试

本站关于mysql的测试在 ../mysql/benchmark_demo
建表语句
mysqlslap 测试
emysql的压力测试，开一条mysql连接，用存储过程插入10000条数据
测试用 press.erl
建表语句

CREATE TABLE `tbl_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`onOffType` int(11) DEFAULT NULL,
`accountId` int(11) DEFAULT NULL,
`accountType` int(11) DEFAULT NULL,
`playerId` int(11) DEFAULT NULL,
`headHeroId` int(11) DEFAULT NULL,
`playerLevel` int(11) DEFAULT NULL,
`playerName` varchar(255) DEFAULT NULL,
`logTime` datetime DEFAULT NULL,
`clientVersion` varchar(255) DEFAULT NULL,
`clientType` varchar(255) DEFAULT NULL,
`issuers` varchar(255) DEFAULT NULL,
`flashPlayerVersion` int(11) DEFAULT NULL,
`connectType` int(11) DEFAULT NULL,
`gameServerName` varchar(255) DEFAULT NULL,
`ipAddr` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);

DELIMITER $$
drop procedure if exists `insert_tbl_test`$$
create procedure `insert_tbl_test`(
IN inaccountId int(11),/**/
IN inaccountType int(11),/**/
IN inplayerId int(11),/**/
IN inheadHeroId int(11),/**/
IN inplayerLevel int(11),/**/
IN inplayerName varchar(255),/**/
IN inlogTime datetime,/**/
IN inclientVersion varchar(255),/**/
IN inclientType varchar(255),/**/
IN inissuers varchar(255),/**/
IN inflashPlayerVersion int(11),/**/
IN inconnectType int(11),/**/
IN ingameServerName varchar(255),/**/
IN inipAddr varchar(255),/**/
IN inonOffType int(11) /**/
)
BEGIN
insert into `tbl_on_off`(
`accountId`, /**/
`accountType`, /**/
`playerId`, /**/
`headHeroId`, /**/
`playerLevel`, /**/
`playerName`, /**/
`logTime`, /**/
`clientVersion`, /**/
`clientType`, /**/
`issuers`, /**/
`flashPlayerVersion`, /**/
`connectType`, /**/
`gameServerName`, /**/
`ipAddr`, /**/
`onOffType` /**/
) values(
inaccountId,/**/
inaccountType,/**/
inplayerId,/**/
inheadHeroId,/**/
inplayerLevel,/**/
inplayerName,/**/
inlogTime,/**/
inclientVersion,/**/
inclientType,/**/
inissuers,/**/
inflashPlayerVersion,/**/
inconnectType,/**/
ingameServerName,/**/
inipAddr,/**/
inonOffType /**/
);
END$$
DELIMITER ;
mysqlslap 测试

其中关于开一个mysql连接执行10000次存储过程插入10000条数据的测试
mysqlslap --query="call insert_tbl_test(1,1,1,1,1,'name','1989-01-26','ver','t','dd',1,1,'name','localhost',1)" --concurrency=1 --number-of-queries 10000 --iterations=3 --engine=myisam -uroot -proot
结果显示不用3s就插入了10000条数据
jixiuf@jf /tmp/d $ mysqlslap –query="call insert_tbl_test(1,1,1,1,1,'name','1989-01-26','ver','t','dd',1,1,'name','localhost',1)" –concurrency=1 –number-of-queries 10000 –iterations=3 –engine=myisam -uroot -proot
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 2.496 seconds
Minimum number of seconds to run all queries: 2.469 seconds
Maximum number of seconds to run all queries: 2.551 seconds
Number of clients running queries: 1
Average number of queries per client: 10000

使用普通的insert 语句 (对于非常简单的insert语句 没有必要包装成存储过程，以下
证明，存储过程不比简单insert快)
mysqlslap --query="insert into tbl_test( accountId, accountType, playerId, headHeroId, playerLevel, playerName, logTime, clientVersion, clientType, issuers, flashPlayerVersion, connectType, gameServerName, ipAddr, onOffType ) values(1,1,1,1,1,'name','1989-01-26','ver','t','dd',1,1,'name','localhost',1)" --concurrency=1 --number-of-queries 10000 --iterations=3 --engine=myisam -uroot -proot
jixiuf@jf /tmp/d $ mysqlslap –query="insert into tbl_test( accountId, accountType, playerId, headHeroId, playerLevel, playerName, logTime, clientVersion, clientType, issuers, flashPlayerVersion, connectType, gameServerName, ipAddr, onOffType ) values(1,1,1,1,1,'name','1989-01-26','ver','t','dd',1,1,'name','localhost',1)" –concurrency=1 –number-of-queries 10000 –iterations=3 –engine=myisam -uroot -proot
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 1.642 seconds
Minimum number of seconds to run all queries: 1.620 seconds
Maximum number of seconds to run all queries: 1.676 seconds
Number of clients running queries: 1
Average number of queries per client: 10000

emysql的压力测试，开一条mysql连接，用存储过程插入10000条数据
首先修改emysql的 emysql.app.src,添加env的值{lock_timeout,infinity}
,以避免因超时 emysql不工作 。别忘了重新编译emysql

%% Emysql .app file template
%% This template is filled out by rebar,
%% or make (Makefile made to fill modules in)
%% and then cp src/emysql.app.src ebin/emysql.app

%% Settings (defaults in include/emysql.hrl):
%% default_timeout (TIMEOUT = 8000)
%% lock_timeout (LOCK_TIMEOUT = 5000)

{application, emysql, [
{description, "Emysql - Erlang MySQL driver"},
{vsn, "0.2"},
{modules, []},
{mod, {emysql_app, ["%MAKETIME%"]}},
{registered, []},
{applications, [kernel, stdlib, crypto]},
{env, [{default_timeout, 5000},
{lock_timeout,infinity}
]}
]}.

测试用 press.erl

-module(press).
-export([get_timestamp/0,start/3,run/3]).
-include("emysql.hrl").

-record(state,{running=0,start_time,process_count_all,sql_count_each_process}).
%% 启动ProcessCount个进程，每个进程执行SqlCountEachProcess次sql 操作
start(ProcessCount,SqlCountEachProcess,MysqlConnectionCount)->
crypto:start(),
application:start(emysql),
emysql:add_pool(hello_pool, MysqlConnectionCount, "root", "root", "localhost", 3306, "mysqlslap", utf8),

CurrentTime=get_timestamp(),
spawn_link(?MODULE,run,[ProcessCount,SqlCountEachProcess,#state{start_time=CurrentTime,
process_count_all=ProcessCount,
sql_count_each_process=SqlCountEachProcess}])
.


recv(#state{running=0,start_time=StartTime,
process_count_all=ProcessCount,
sql_count_each_process=SqlCountEachProcess
})->
Usedtime =get_timestamp()-StartTime,
io:format("process_count:~p sql count each process:~p used time:~p~n",[ProcessCount,SqlCountEachProcess,Usedtime]),
emysql:remove_pool(hello_pool);
recv(#state{running=Running}=State)->
receive
done->
recv(State#state{running=Running-1})
end
.

run(0,_SqlCountEachProcess,#state{}=State)->
recv(State);
run(ProcessCount,SqlCountEachProcess,#state{running=Running}=State) ->
Parent =self(),
spawn(fun()-> run_sql(SqlCountEachProcess,Parent)end),
run(ProcessCount-1,SqlCountEachProcess,State#state{running=Running+1})
.

run_sql(0,Parent)->
Parent!done;
run_sql(SqlCountEachProcess,Parent) ->
test2(),
run_sql(SqlCountEachProcess-1 ,Parent)
.


test2()->
%%"insert into tbl_test( accountId, accountType, playerId, headHeroId, playerLevel, playerName, logTime, clientVersion, clientType, issuers, flashPlayerVersion, connectType, gameServerName, ipAddr, onOffType ) values(1,1,1,1,1,'name','1989-01-26','ver','t','dd',1,1,'name','localhost',1)"
%% "call insert_tbl_test(1,1,1,1,1,'name','1989-01-26','ver','t','dd',1,1,'name','localhost',1);"
Result=emysql:execute(hello_pool,<< "call insert_tbl_test(1,1,1,1,1,'name','1989-01-26','ver','t','dd',1,1,'name','localhost',1);">>),
case Result of
Rec when is_record(Rec ,ok_packet) ->
ok;
Rec when is_record(Rec ,error_packet) ->
io:format("~p~n",[Result])
end

%% %% Pid=global:whereis_name(emysql_center)
.
get_timestamp() ->
{Mega,Sec,Micro} = erlang:now(),
((Mega*1000000+Sec)*1000000+Micro)/1000.


启用10000个erlang进程，每个进程向emysql发一条sql(存储过程),最后一个参数是emysql连接池
里只开一个mysql连接
press:start(10000,1,1).
测试结果显示大概用了8秒，大概是比单纯的mysql测试慢3~4倍 (7583/2496=3.45)
(emacsdd@jf.org)4> press:start(10000,1,1).
process_count:10000 sql count each process:1 used time:7583.25390625
有些网友怀疑创建10000个进程也会消耗一部分时间 ，所以增加下面几个测试 ，结果显
示所用时间仍然处于7~9s这个范围内，影响不大

起用一个进程，此进程向emysql发送10000条sql语句
1> press:start(1,10000,1).
process_count:1 sql count each process:10000 used time:8632.112060546875
1> press:start(10,1000,1).
process_count:10 sql count each process:1000 used time:7520.348876953125
1> press:start(100,100,1).
process_count:100 sql count each process:100 used time:7613.68896484375
import java.sql.Date;
import java.sql.Types;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class TestMysqlConn {
public static void main(String[] args) throws Exception {
// Class.forName("com.mysql.jdbc.Driver");
Class.forName("org.gjt.mm.mysql.Driver");
long start= System.currentTimeMillis();
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysqlslap?user=root&password=root");
// call insert_tbl_test(1,1,1,1,1,'name','1989-01-26','ver','t','dd',1,1,'name','localhost',1)
CallableStatement cs = conn.prepareCall("{call insert_tbl_test(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
cs.setInt(1 , 1);
cs.setInt(2 , 1);
cs.setInt(3 , 1);
cs.setInt(4 , 1);
cs.setInt(5 , 1);
cs.setString(6 , "name");
cs.setDate(7 , new Date(1989 , 1 , 26));
cs.setString(8 , "ver");
cs.setString(9 , "t");
cs.setString(10 , "dd");
cs.setInt(11 ,1);
cs.setInt(12 ,1);
cs.setString(13 , "name");
cs.setString(14 , "localhost");
cs.setInt(15 , 1);
for (int i = 0; i < 10000; i++) {
cs.executeUpdate();
}
long end= System.currentTimeMillis();
System.out.println(end-start);

}
}
java TestMysqlConn
7198
java TestMysqlConn
7453
java TestMysqlConn
7031

测测结果显示，jdbc也用了大概7s左右的时间执行10000次存储过程的插入,
可以证明emysql跟java jdbc连mysql基本不相上下