解析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 “
”
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
相關文章
- 【XMPP】Smack原始碼之訊息接收與解析Mac原始碼
- eml檔案解析
- Class檔案解析
- Qt的.pro檔案格式解析QT
- Hibernate的配置檔案解析
- maven中的profile檔案的解析Maven
- jdom解析xml檔案XML
- java class檔案解析Java
- BVH檔案格式解析
- Nginx配置檔案解析Nginx
- GData解析XML檔案XML
- redis配置檔案解析Redis
- jquery 解析xml檔案jQueryXML
- oracle trace檔案解析Oracle
- ISO檔案解析(轉)
- XML 檔案解析實踐 (DOM 解析)XML
- DICOM醫學檔案的解析
- Torrent檔案的解析與轉換
- 如何解析 redis 的 rdb 檔案Redis
- Benchmarksql的props配置檔案解析SQL
- 原始碼解析Flask的配置檔案原始碼Flask
- Java解析ELF檔案:ELF檔案格式規範Java
- Oracle引數檔案解析——引數解析Oracle
- mybatis原始碼配置檔案解析之五:解析mappers標籤(解析XML對映檔案)MyBatis原始碼APPXML
- webpack打包bundle檔案解析Web
- java class 檔案格式解析Java
- c++ 解析yaml檔案C++YAML
- java解析yaml配置檔案JavaYAML
- 使用 Java 解析XML檔案JavaXML
- 使用PdfRenderer解析PDF檔案
- DHCP常用配置檔案解析
- 解析Mach-o檔案Mac
- 解析listener跟蹤檔案
- Nginx配置檔案示例解析Nginx
- android解析plist檔案Android
- android: AAC檔案解析Android
- iOS: Crash檔案解析(一)iOS
- 利用Perl解析XML檔案XML