解析super-smack的smack文件-白红宇

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

在安装完工具后，我们会在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则代表了每个客户端轮询执行多少次查询；

参考：