长沙网站大全,wordpress yusi主题,电子商务网站建设管理论文,网站开发公司小程序开发Super-smack是一款强大的数据库压测工具#xff0c;现在支持mysql和PostgreSQL两种数据库#xff0c;你可以通过简单的配置文件(.smack)来生成一系列测试环境#xff1a;测试数据#xff0c;测试表#xff1b;在测试的过程中#xff0c;你可以控制客户端的并发数量以及执… 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: max3ms min0ms 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 ) ENGINEInnoDB DEFAULT CHARSETgbk”; 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查询条件中的值 typerand表示随机的从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的数据来测试 备注如果我们的查询值有多个比如根据aiduserid来查询那么需要定义两个数据字典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_barrelquery_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 1510代表了有多少客户端同时来进行测试15则代表了每个客户端轮询执行多少次查询 参考http://imysql.cn/docs/High_Performance_MySQL/0596003064/hpmysql-CHP-3-SECT-3.html
