解析super-smack的smack檔案

玄慚發表於2016-04-01

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 “

FROM auth;

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

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”‘

LINES TERMINATED BY “

FROM auth;

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

FIELDS TERMINATED BY ‘,’

OPTIONALLY ENCLOSED BY ‘”‘

LINES TERMINATED BY “

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


相關文章