Mysql 压力测试工具super-smack的安装

Mysql 压力测试工具super-smack的安装

本文主要介绍super-smack的安装过程以及详细配置

参考文章：http://www.ltesting.net/ceshi/ceshijishu/xncs/2008/0904/158289.html

                http://lgpzll.iteye.com/blog/616014

安装环境ubuntu10.10

Super-smack1.3下载地址：链接: http://pan.baidu.com/s/1o6C9miA  密码:  qjrv

在root下建立一个文件夹weic(名字随便)把super-smack-1.3.tar放入该文件夹

1.   进入/root/weic/解压super-smack-1.3.tar

#tar –xzvf super-smack-1.3.tar

2.   进入src目录

3.   执行命令：

#./configure--prefix=/usr/local/supersmack --with-mysql--with-mysql-lib=/usr/local/mysql/lib--with-mysql-include=/usr/local/mysql/include 

 

#make

若出错

 

A.   进入super-smack.h和dictionary.h 加入#include<string.h>

B.    或者nano dictionary.h 添加 #include <cstring> 

C.    若缺少flex  安装flex 

 # apt-get install flex 

 

       D．若缺少yacc

4.安装Yacc   注：最好不要在当前的shell下安装
wgethttp://invisible-island.net/datafiles/release/byacc.tar.gz 
tar zxvf byacc.tar.gz 
cd byacc 
./configure 
make 
make install 

5.再次进入src目录

#make

#makeinstall

若无错误

6.配置执行文件到/usr/local/目录方便执行（也可以不这么做，直接到程序目录下执行）

cp /usr/share/smacks/select-key.smack/usr/local/supersmack/bin/select-key.smack 

cp /usr/share/smacks/update-select.smack /usr/local/supersmack/bin/update-select.smack

7.修改两个重要文件select-key.smack和update-select.smack 

 

修改数据库用户名和密码 
nano /usr/local/supersmack/bin/select-key.smack 
nano/usr/local/supersmack/bin/update-select.smack 

 

1)word.data修改为http_auth.data 
2)gen_data_file"/usr/local/supersmack/bin/gen-data -n 90000 -f%12-12s%n       %25-25s%n      %d"; 
中","用TAB 
3)delim用TAB替换"," 

4）修改”/tmp/mysql.sock”为你自己的mysqld.sock的目录

如我的mysqld.sock路径是

/var/run/mysqld/my

 

 

 

具体可以参考以下文章：http://www.ltesting.net/ceshi/ceshijishu/xncs/2008/0904/158289.html

[Copy to clipboard] [ - ]CODE: // this is will be used in thetable section  这一段做了修改，主要是连mysql server：
client "admin"
{
user "smack";
host "192.168.20.7";
db "smack";
pass "smack";
socket "/var/lib/mysql/mysql.sock"; //this only applies to MySQL and is
// ignored for PostgreSQL
}

// ensure the table exists and meets theconditions
table "http_auth"
{
  client "admin"; // connectwith this client
// if the table is not found or does not passthe checks, create it
// with the following, dropping the old one ifneeded
  create "create table http_auth
    (username char(25) not nullprimary key,
     pass char(25),
     uid integer not null,
     gid integer not null
    )";
  min_rows "90000"; // thetable must have at least that many rows
  data_file "http_auth.dat";// if the table is empty, load the data from this file
// 原本data_file的值是words.dat，后来改成smack库里与http_auth同名的http_auth.dat文件，程序才能正常运行。（详细说明在文档尾部）
  gen_data_file "gen-data -n90000 -f %12-12s%n,%25-25s,%n,%d";
// gen-data命令后面的字段是以逗号分隔，但是mysqlimport不支持以逗号分隔的文件，必须以Tab分隔，所以生成这个文件以后把所有逗号替换成TAB。（注：直接在上面的命令中把逗号替换成TAB是无效的）。
// if the file above does not exist, generate itwith the above shell command
// you can replace this command with anythingthat prints comma-delimited
// data to stdout, just make sure you have the right number of columns
}

//define a dictionary
dictionary "word"
{
  type "rand"; // words areretrieved in random order
  source_type "file"; //words come from a file
  source "http_auth.dat"; //file location  这里的文件名也相应做改动
  delim "    "; //take the part of the line before , //把逗号改为一个TAB
  file_size_equiv "45000";// if the file is greater than this
//divive the real file size by this valueobtaining N and take every Nth
//line skipping others. This is needed to beable to target a wide key
// range without using up too much memory withtest keys
}

//define a query
query "select_by_username"
{
  query "select * from http_authwhere username = '$word'";
// $word will be substitute with the read fromthe 'word' dictionary
  type "select_index";
// 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 bysuper-smack to do
// dictionary substitution
}

// define database client type 不知道这一段是做什么的，把mysql server的连接信息填进去
client "smacker1"
{
user "smack"; // connect as this user
pass "smack"; // use this password
host "192.168.20.7"; // connect tothis host
db "smack"; // switch to this database
socket "/var/lib/mysql/mysql.sock"; //this only applies to MySQL and is
// ignored for PostgreSQL
query_barrel "2 select_by_username";// on each round,
// run select_by_username query 2 times
}

main
{
  smacker1.init(); // initialize theclient
  smacker1.set_num_rounds($2); //second arg on the command line defines
// the number of rounds for each client
  smacker1.create_threads($1);
// first argument on the command line defineshow many client instances
// to fork. Anything after this will be doneonce for each client until
// you collect the threads
  smacker1.connect();
// you must connect after you fork
  smacker1.unload_query_barrel(); //for each client fire the query barrel
// it will now do the number of rounds specifiedby set_num_rounds()
// on each round, query_barrel of the client isexecuted

  smacker1.collect_threads();
// the master thread waits for the children,each child reports the stats
// the stats are printed
  smacker1.disconnect();
// the children now disconnect and exit
}
执行测试命令：
super-smack -d mysql select-key.smack 10 1000

系统输出：

[Copy to clipboard] [ - ]CODE: super-smack -dmysql select-key.smack 10 1000
Table 'http_auth' does not meet condtions, willbe dropped
Creating table 'http_auth'
Loading data from file'/var/smack-data/http_auth.dat' into table 'http_auth'
Error running query load data infile'/var/smack-data/http_auth.dat' into table http_auth fields terminated by',':Can't get stat of '/var/smack-data/http_auth.dat' (Errcode: 2)
super-smack: aborting on failed query
http_auth.dat文件为空，数据没有导入成功，手工导入数据：
gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d> /var/smack-data/http_auth.dat

把开文本编辑器，把这个文件中的所有逗号替换为TAB，在vi里使用命令：
:%s/,/\t/g

再次运行测试命令，虽然我已经把配置文件中的delim变量的值改为TAB，但是下面的出错信息中仍然说字段分隔符是','，并且http_auth表没有数据：

[Copy to clipboard] [ - ]CODE: Table 'http_auth'does not meet condtions, will be dropped
Creating table 'http_auth'
Loading data from file '/var/smack-data/http_auth.dat'into table 'http_auth'
Error running query load data infile'/var/smack-data/http_auth.dat' into table http_auth fields terminated by',':Can't get stat of '/var/smack-data/http_auth.dat' (Errcode: 2)
super-smack: aborting on failed query
[code]
通过手工导入：
mysqlimport -L -usmack -psmack -h192.168.20.7smack /var/smack-data/http_auth.dat

系统输出说明数据已经导入：
smack.http_auth: Records:90000  Deleted: 0  Skipped: 0  Warnings: 0

再次运行select测试命令：
super-smack -d mysql select-key.smack 10 1000

系统输出：
[code]
Query Barrel Report for client smacker1
connect: max=112ms  min=14ms avg= 59msfrom 10 clients
Query_type     num_queries     max_time       min_time        q_per_s
select_index    20000  5       0       1967.19
运行update测试命令：
super-smack -d mysql update-select.smack 10 1000

系统输出：

[Copy to clipboard] [ - ]CODE: Query BarrelReport for client smacker
connect: max=137ms  min=17ms avg= 76msfrom 10 clients
Query_type     num_queries     max_time       min_time        q_per_s
select_index    10000  6       1       932.57
update_index    10000  7       0       932.57
注：多次测试后发现每次输出的值都会不一样。


在mysql 集群上测试select的性能：
在配置文件中的建表语句中加入“engine ndb“，然后运行select测试语句，系统输出：

[Copy to clipboard] [ - ]CODE: Query BarrelReport for client smacker1
connect: max=600ms  min=20ms avg=262ms from 10 clients
Query_type     num_queries     max_time       min_time        q_per_s
select_index    20000  12      2       650.54
运行update测试语句，系统输出：

[Copy to clipboard] [ - ]CODE: Query BarrelReport for client smacker
connect: max=117ms  min=15ms avg= 75msfrom 10 clients
Query_type     num_queries     max_time       min_time        q_per_s
select_index    10000  17      3       291.17
update_index    10000  42      3       291.17
====================================================================================================
1. 关于data_file变量的设置：
经过研究发现super-smack先生成一个文件文件，文件名是由data_file变量指定，再把这个文件内容导入smack库的http_auth表，这个变量原来的值是words.dat，我试着手工导入文件：
mysqlimport -L -usmack -psmack -h192.168.20.7smack /var/smack-data/words.dat

然后系统会提示找不到smack.words表，也就是smack库下的words表。mysqlimport导入文件的时候把文件扩展名去掉，把文件名剩下的部分做为表名导入数据，实际上smack库里没有words这个表，所以我把这个变量的值改为http_auth.dat，然后再试着手工导入就一切 ok了：
mysqlimport -L -usmack -psmack -h192.168.20.7smack /var/smack-data/http_auth.dat

2. 关于http_auth.dat文件：
mysqlimport导入的文件，字段间必须以tab分隔，逗号或空格都不可以。