• 软件测试技术
  • 软件测试博客
  • 软件测试视频
  • 开源软件测试技术
  • 软件测试论坛
  • 软件测试沙龙
  • 软件测试资料下载
  • 软件测试杂志
  • 软件测试人才招聘
    暂时没有公告

字号: | 推荐给好友 上一篇 | 下一篇

使用super-smack测试mysql的性能

发布: 2008-9-04 18:13 | 作者: 网络转载 | 来源: 网络转载 | 查看: 284次 | 进入软件测试论坛讨论

领测软件测试网 我的环境是rhel5.1 server i386,mysql使用从www.mysql.com上下载的5.1.22的rpm包。super-smack使用的是1.2和1.3,两个版本差不多,编译的时候要把/usr/lib64目录下的libmysqlclient.so.16复制到/usr/lib目录下,而不是建立链接过去。super-smack 只能成功安装到32系统上,64位系统装不上。

使用下来感觉super-smack在我的rhel5u1 server i386+ mysql 5.1.22上运行会有很多错误,我不得不手工修改配置文件select-key.smack,不停的修改,不停的测试,终于成功运行。

修改后的select-key.smack,这个文件默认测试MyISAM引擎的性能

[Copy to clipboard] [ - ]CODE: // this is will be used in the table 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 the conditions
table "http_auth"
{
  client "admin"; // connect with this client
// 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 http_auth
    (username char(25) not null primary key,
     pass char(25),
     uid integer not null,
     gid integer not null
    )";
  min_rows "90000"; // the table 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 -n 90000 -f %12-12s%n,%25-25s,%n,%d";
// gen-data命令后面的字段是以逗号分隔,但是mysqlimport不支持以逗号分隔的文件,必须以Tab分隔,所以生成这个文件以后把所有逗号替换成TAB。(注:直接在上面的命令中把逗号替换成TAB是无效的)。
// if the file above does not exist, generate it with the above shell command
// you can replace this command with anything that prints comma-delimited
// data to stdout, just make sure you have the right number of columns
}

//define a dictionary
dictionary "word"
{
  type "rand"; // words are retrieved 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 value obtaining N and take every Nth
//line skipping others. This is needed to be able to target a wide key
// range without using up too much memory with test keys
}

//define a query
query "select_by_username"
{
  query "select * from http_auth where username = '$word'";
// $word will be substitute with the read from the '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 by super-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 to this 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 the client
  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 defines how many client instances
// to fork. Anything after this will be done once 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 specified by set_num_rounds()
// on each round, query_barrel of the client is executed

  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 -d mysql select-key.smack 10 1000
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
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.7 smack /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= 59ms from 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 Barrel Report for client smacker
connect: max=137ms  min=17ms avg= 76ms from 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 Barrel Report 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 Barrel Report for client smacker
connect: max=117ms  min=15ms avg= 75ms from 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.7 smack /var/smack-data/words.dat

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

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

延伸阅读

文章来源于领测软件测试网 https://www.ltesting.net/

TAG: Mysql MySQL mysql mySQL snooker 性能


关于领测软件测试网 | 领测软件测试网合作伙伴 | 广告服务 | 投稿指南 | 联系我们 | 网站地图 | 友情链接
版权所有(C) 2003-2010 TestAge(领测软件测试网)|领测国际科技(北京)有限公司|软件测试工程师培训网 All Rights Reserved
北京市海淀区中关村南大街9号北京理工科技大厦1402室 京ICP备10010545号-5
技术支持和业务联系:info@testage.com.cn 电话:010-51297073

软件测试 | 领测国际ISTQBISTQB官网TMMiTMMi认证国际软件测试工程师认证领测软件测试网