Home > database > 解析super-smack的smack文件

解析super-smack的smack文件

Super-smack是一款强大的数据库压测工具,现在支持mysql和PostgreSQL两种数据库,你可以通过简单的配置文件(.smack)来生成一系列测试环境:测试数据,测试表;在测试的过程中,你可以控制客户端的并发数量以及执行频率,根据业务场景配置不同sql,以及他们的执行比率来满足我们需要的业务测试场景;

在安装完super-smack工具后,我们会在smack文件目录中看到一些文件: select-key.smack , update-select.smack这些都是工具自带的配置文件,在运行Super-smack工具来压测DB之前,需要准备压测的表,压测数据,应用场景的sql语句,连接数据库的配置等信息,这些都可以在smack文件中配置完成。

我们只需要依葫芦画瓢,修改其中的配置就行了;

$./super-smack -d mysql -D /home/mysql/xuancan/super-smack/super-data /home/mysql/xuancan/super-smack-1.3/smacks/my.smack 10 15

Query Barrel Report for client smacker

connect: max=3ms  min=0ms avg= 0ms from 10 clients

Query_type      num_queries     max_time        min_time        q_per_s

select_akey   1000    16      2       497.42

select_by_id    10000   0       0       4974.16

select_userid_aid     5000    0       0       2487.08

update_by_id    1500    4       0       746.12

上面的my.smack配置文件中我配置了按照select_akey(按照akey查询) ,select_by_id (按照主键id查询),select_userid_aid(按照userid和aid查询),update_by_id(根据主键更新) 四种业务场景的sql,客户端有10个,每个客户端轮询执行15次的压测场景,其中我们比较关注的是qps,当然得到的测试数据还和主机服务器的配置有关,数据量相关,不能一概而论。

我们拷贝一份select-update.smack文件,并重命名为my.smack,开始编辑my.smack文件:

client “admin”

{

user “root”;

host “localhost”;

db “test”;

pass “”;

socket “/u01/mysql/run/mysql.sock”;

}

该选项是用于配置admin client,由于我们在本机上进行压测,所以使用root用户不用指定密码,如果你在远程客户端上进行压测,那么就需要指定pass密码了;socket用于指定连接mysql使用的sock文件,super-smack默认会到”/tmp/mysql.sock” or “/var/lib/mysql/mysql.sock”这两个地方去读smack文件,我们这里指定了/u01/mysql/run/mysql.sock;

table “auth”

{  client “admin”;

// if the table is not found or does not pass the checks, create it, with the following, dropping the old one if needed

create ” CREATE TABLE auth(

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`userid` bigint(20) unsigned NOT NULL COMMENT ‘用户id’,

`nick` varchar(32) NOT NULL COMMENT ‘nick’,

`aid` bigint(20) unsigned NOT NULL COMMENT ‘应用id’,

`akey` varchar(256) NOT NULL,

`skey` varchar(500) NOT NULL COMMENT ‘skey’,

PRIMARY KEY (`id`),

UNIQUE KEY `ind_auth_userid` (`userid`,`aid`) USING BTREE,

KEY `ind_auth_akey` (akey) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=gbk”;

min_rows “90000”; // the table must have at least that many rows

data_file “userid.dat”; // if the table is empty, load the data from

//this file

gen_data_file “gen-data -n 90000 -f %10-12s%n,%25-25s,%n,%d”;

// if the file above does not exist, generate it with the above command

}

该选项用于定义压测的表:首先这里引用了前面定义的admin client,该表将会按照admin client的定义在test库中检查,如果该表没有,那么将会创建该表,同时我们指定了该表的最小行数,如果表中的行数没有达到min_rows,那么super-smack将会删除掉该表,重建该表;如果该表为空,将会从userid.dat中load数据到该表中,如果改文件不存在,则将用gen_data产生该文件:

%n表示可以从1开始递增的数值,可用于表中的主键

%d表示随机的产生数值

%s表示随机生产一些字母

10-12表示产生字母的范围长度

//define a dictionary

dictionary “userid”

{

type “rand”; // userid are retrieved in random order

source_type “file”; // userid come from a file

source “user.dat”; // file location

delim “,”; // take the part of the line before ,

file_size_equiv “45000”; // if the file is greater than this

//divive the real file size by this value obtaining N and take every Nth

//line skipping others

}

该数据字典用于配置sql查询条件中的值,

type:rand表示随机的从userid.dat中抽取值;

seq表示值是连续的

unique表示用gen-date产生唯一的值

source_type:file表示数据来自于磁盘的文件

list表示由用户提供带有分割符的数据(”one”,”two”,”three”)

template表示当type为unique的时候使用,比如jzawodn_%07d” generates values composed of jzawodn_ and a seven-digit number.

Source:存放在/home/mysql/xuancan/super-smack/super-data中的文件userid.dat

Delim:表示如果你使用带有分隔符的文件,delim告诉super-smack什么分隔符分隔文件

File_size_equiv:如果你的数据字典文件非常大,该选项将会很有用,如果你的文件为10k,指定file_size_equiv为1024,那么super-smack将会使用1/10的数据来测试;

备注:如果我们的查询值有多个,比如根据aid+userid来查询,那么需要定义两个数据字典:aid和userid。

SELECT aid INTO OUTFILE “/home/mysql/xuancan/super-smack/super-data/aid.dat”

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”‘

LINES TERMINATED BY “\n”

FROM auth;

SELECT useid INTO OUTFILE “/home/mysql/xuancan/super-smack/super-data/userid.dat”

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”‘

LINES TERMINATED BY “\n”

FROM auth;

SELECT akey INTO OUTFILE “/home/mysql/xuancan/super-smack/super-data/akey.dat”

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”‘

LINES TERMINATED BY “\n”

FROM auth;

//define a query

query “select_by_userid”

{

query “select * from auth where userid = ‘$userid'”;

// $word will be substitute with the read from the ‘userid’ dictionary

type “select_by_userid”;

// query stats will be grouped by type

has_result_set “y”;

// the query is expected to return a result set

parsed “y”;

// the query string should be first processed by super-smack to do

// dictionary substitution

}

query “update_by_aid”

{

query “update auth set akey=’$akey’ where aid= ‘$aid'”;

// $word will be substitute with the read from the ‘word’ dictionary

// note that the first word is not the same as the second, as each is

// a separate random draw from the dictionary

type “update_index”;

// query stats will be grouped by type

has_result_set “n”;

// the query is expected to return a result set

parsed “y”;

// the query string should be first processed by super-smack to do

// dictionary substitution

}

定义查询:query定义查询的sql,其中查询的值有刚才定义的数据字典word来获得

Type:在生成结果的时候显示的名字;

Has_result_set:如果是sql为select,则该值设置为y,若为update,则为n

Parsed:表示word的值数据字典是将该值置为y

// define database client type

client “smacker”

{

user “test”; // connect as this user

pass “test”; // use this password

host “localhost”; // connect to this host

db “test”; // switch to this database

socket “/u01/mysql/run/mysql.sock”; // this only alies to MySQL and is

// ignored for PostgreSQL

query_barrel “2 select_akey  15 select_by_id   5  select_userid_aid 10 update_by_id“; // on each round

}

与前面定义的admin client不同的是在smacker client中多定义了query_barrel,query_barrel定义了查询的顺序和执行次数,也是就是我们常说的业务场景,你的select update delete的比例是多少;

main

{

smacker.init(); // initialize the clients

smacker.set_num_rounds($2); // second arg on the command line defines

// the number of rounds for each client

smacker.create_threads($1);

// first argument on the command line defines how many client instances

// to fork. Anything after this will be done once for each client until

// you collect the threads

smacker.connect();

// you must connect after you fork

smacker.unload_query_barrel(); // for each client fire the query barrel

// it will now do the number of rounds specified by set_num_rounds()

// on each round, query_barrel of the client is executed

smacker.collect_threads();

// the master thread waits for the children, each child reports the stats

// the stats are printed

smacker.disconnect();

// the children now disconnect and exit

}

最后定义的中我们需要注意$1和$2两个参数,也就是我们调用super-smack进行压测的时候的10 15,10代表了有多少客户端同时来进行测试,15则代表了每个客户端轮询执行多少次查询;

参考:http://imysql.cn/docs/High_Performance_MySQL/0596003064/hpmysql-CHP-3-SECT-3.html

Categories: database Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.