【mysql】使用tpcc-mysql进展压力测试_mysql

rpm -Uvh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm yum install bzr bzr branch lp:~percona-dev/perconatools/tpcc-mysql 

[root@localhost tpcc-mysql]# cat README  1. Build binaries    * cd scr ; make    ( you should have mysql_config available in $PATH)  2. Load data    * create database      mysqladmin create tpcc1000    * create tables      mysql tpcc1000 < create_table.sql    * create indexes and FK ( this step can be done after loading data)      mysql tpcc1000 < add_fkey_idx.sql    * populate data      - simple step        tpcc_load 127.0.0.1:33000 tpcc1000 root "" 1000                  |hostname:port| |dbname| |user| |password| |WAREHOUSES|        ref. tpcc_load --help for all options      - load data in parallel         check load.sh script  3. start benchmark    * ./tpcc_start -h127.0.0.1 -P33000 -dtpcc1000 -uroot -w1000 -c32 -r10 -l10800                   |hostname| |port| |dbname| |user| |WAREHOUSES| |CONNECTIONS| |WARMUP TIME| |BENCHMARK TIME|    * ref. tpcc_start --help for all options  

#!/bin/bash                                                                                                                              # Configration   MYSQL=mysql TPCCLOAD=./tpcc_load TABLESQL=./create_table.sql CONSTRAINTSQL=./add_fkey_idx.sql DEGREE=`getconf _NPROCESSORS_ONLN`   SERVER=192.168.1.104 DATABASE=tpcc USER=root PASS=123456 WAREHOUSE=10   # Load   set -e $MYSQL -h$SERVER -u$USER -p$PASS -e "DROP DATABASE IF EXISTS $DATABASE" $MYSQL -h$SERVER -u$USER -p$PASS -e "CREATE DATABASE $DATABASE" $MYSQL -h$SERVER -u$USER -p$PASS $DATABASE < $TABLESQL $MYSQL -h$SERVER -u$USER -p$PASS $DATABASE < $CONSTRAINTSQL /usr/local/src/tpcc-mysql/tpcc_load_parallel.sh [FORMAT=unix] [TYPE=SH] [POS=1,1][1%] 09/01/16 - 23:26                                          done           if [ $STATUS -ne 0 ]; then             exit $STATUS         fi           PIDLIST=()     fi done   for PID in ${PIDLIST[@]}; do     wait $PID       if [ $? -ne 0 ]; then         STATUS=1     fi done   if [ $STATUS -eq 0 ]; then     echo 'Completed.' fi   exit $STATUS   

./tpcc_start -h192.168.1.104 -dtpcc -uroot -p123456 -w10 -c16 -r10 -l1200 > /tmp/512m-tpcc-data.log ./tpcc_start -h192.168.1.104 -dtpcc -uroot -p123456 -w10 -c16 -r10 -l1200 > /tmp/1g-tpcc-data.log ./tpcc_start -h192.168.1.104 -dtpcc -uroot -p123456 -w10 -c16 -r10 -l1200 > /tmp/2g-tpcc-data.log  ./tpcc_start -h192.168.1.104 -dtpcc -uroot -p123456 -w10 -c8 -r10 -l1200 > /tmp/8c-tpcc-data.log ./tpcc_start -h192.168.1.104 -dtpcc -uroot -p123456 -w10 -c16 -r10 -l1200 > /tmp/16c-tpcc-data.log ./tpcc_start -h192.168.1.104 -dtpcc -uroot -p123456 -w10 -c64 -r10 -l1200 > /tmp/64c-tpcc-data.log   -d 测试库 -u 用户名 -p 密码 -w 使用多少个数据仓库 -c 测试的并发数 -r 预热几秒 -l 运行测试多长时间 

*************************************** *** ###easy### TPC-C Load Generator *** *************************************** option h with value '192.168.1.104' option d with value 'tpcc' option u with value 'root' option p with value '123456' option w with value '20'//仓库 option c with value '16'//并发线程数 option r with value '10'//预热时长 option l with value '1200' <Parameters>      [server]: 192.168.1.104      [port]: 3306      [DBname]: tpcc        [user]: root        [pass]: 123456   [warehouse]: 20  [connection]: 16      [rampup]: 10 (sec.)     [measure]: 1200 (sec.)  RAMP-UP TIME.(10 sec.)  //预热结束，开始进行压测  MEASURING START.  //每10秒钟输出一次压测数据    10, 0(0):17.297|29.366, 2(1):6.077|6.390, 0(0):0.000|0.000, 0(0):0.000|3.968, 0(0):0.000|0.000   20, 2(2):19.999|40.895, 3(2):5.218|10.209, 0(0):0.000|0.000, 1(0):0.000|53.658, 1(1):0.000|140.858   30, 0(0):0.000|0.000, 1(1):0.000|5.888, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000   40, 4(4):19.999|25.264, 4(3):9.853|13.864, 0(0):0.000|0.000, 1(0):0.000|3.219, 0(0):0.000|0.000   50, 5(5):19.999|40.184, 4(2):6.046|7.648, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000   60, 0(0):0.000|0.000, 1(0):0.000|3.568, 1(1):0.000|24.226, 1(0):0.000|1.637, 0(0):0.000|0.000 …… 1170, 4(4):19.437|25.995, 4(3):7.417|9.241, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000 1180, 6(6):19.999|26.409, 9(2):5.568|8.834, 1(0):0.000|1.762, 1(0):0.000|36.276, 1(1):0.000|86.459 1190, 2(2):11.617|12.754, 2(2):5.420|8.147, 0(0):0.000|0.000, 0(0):0.000|0.000, 0(0):0.000|0.000 1200, 2(2):16.664|22.551, 1(0):0.000|1.486, 0(0):0.000|0.000, 1(0):0.000|30.834, 1(1):0.000|256.143  //以逗号分隔，共6列 //第一列，第N次10秒 //第二列，新订单成功执行压测的次数(推迟执行压测的次数):90%事务的响应时间|本轮测试最大响应时间，新订单事务数也被认为是总有效事务数的指标 //第三列，支付业务成功执行次数(推迟执行次数):90%事务的响应时间|本轮测试最大响应时间 //第四列，订单状态业务的结果，后面几个的意义同上 //第五列，物流发货业务的结果，后面几个的意义同上 //第六列，库存仓储业务的结果，后面几个的意义同上  -- 压测结束  STOPPING THREADS................  <Raw Results>   [0] sc:4  lt:243  rt:546999  fl:275 //New-Order，新订单业务成功(success,简写sc)次数，延迟(late,简写lt)次数，重试(retry,简写rt)次数，失败(failure,简写fl)次数   [1] sc:144  lt:92  rt:565412  fl:281 //Payment，支付业务统计，其他同上   [2] sc:24  lt:3  rt:50802  fl:26 //Order-Status，订单状态业务统计，其他同上   [3] sc:52  lt:0  rt:0  fl:0 //Delivery，发货业务统计，其他同上   [4] sc:0  lt:27  rt:50788  fl:26 //Stock-Level，库存业务统计，其他同上  in 1200 sec.  <Raw Results2(sum ver.)>   [0] sc:4  lt:243  rt:547120  fl:275    [1] sc:144  lt:92  rt:565566  fl:281    [2] sc:24  lt:3  rt:50802  fl:26    [3] sc:52  lt:0  rt:0  fl:0    [4] sc:0  lt:27  rt:50788  fl:26   <Constraint Check> (all must be [OK]) //下面所有业务逻辑结果都必须为 OK 才行  [transaction percentage]         Payment: 40.07% (>=43.0%) [NG] * //支付成功次数(上述统计结果中 sc + lt)必须大于43.0%，否则结果为NG，而不是OK    Order-Status: 4.58% (>= 4.0%) [OK] //订单        Delivery: 8.83% (>= 4.0%) [OK] //发货     Stock-Level: 4.58% (>= 4.0%) [OK] //库存  [response time (at least 90% passed)] //响应耗时指标必须超过90%通过才行       New-Order: 1.62%  [NG] *         Payment: 61.02%  [NG] *    Order-Status: 88.89%  [NG] *        Delivery: 100.00%  [OK]     Stock-Level: 0.00%  [NG] *  <TpmC>                  12.350 TpmC  -- TpmC结果值（每分钟事务数） 

./tpcc_analyze.sh 512m-tpcc-data.log > 512m-tpcc-data.data ./tpcc_analyze.sh 1g-tpcc-data.log > 1g-tpcc-data.data ./tpcc_analyze.sh 2g-tpcc-data.log > 2g-tpcc-data.data  paste 512m-tpcc-data.data  1g-tpcc-data.data 2g-tpcc-data.data > tpcc-data.data ./tpcc-graph.sh tpcc-data.data  tpcc.jpg 

#!/bin/bash TIMESLOT=1          if [ -n "$2" ] then      TIMESLOT=$2     echo "Defined $2" fi             cat $1 | grep -v HY000 | grep -v payment | grep -v neword | /  awk -v timeslot=$TIMESLOT ' BEGIN { FS="[,():]"; s=0; cntr=0; aggr=0 } / /MEASURING START/ { s=1} /STOPPING THREADS/ {s=0} /0/ { if (s==1) { cntr++; aggr+=$2; } / if ( cntr==timeslot ) { printf ("%d %3f/n",$1,$5) ; cntr=0; aggr=0  }  } '  

#!/bin/bash gnuplot << EOP  set style line 1 lt 1 lw 3 set style line 2 lt 5 lw 3 set style line 3 lt 7 lw 3 set terminal png size 960,480 set grid x y set xlabel "Time(sec)" set ylabel "Transactions" set output "$2" plot "$1" using 1:2 title "PS 5.1.56 buffer pool 512MM" ls 1 with lines,/      "$1" using 3:4 title "PS 5.1.56 buffer pool 1g" ls 2 with lines,/      "$1" using 3:6 title "PS 5.1.56 buffer pool 2g" ls 3 with lines axes x1y1                                                      EOP  

export GDFONTPATH=/usr/share/fonts/liberation export GNUPLOT_DEFAULT_GDFONT=LiberationSans-Regular source ~/.bashrc