你的位置:首页 > 数据库

[数据库]【mysql】使用tpcc


Tpcc-mysql是percona基于tpcc衍生出来专用于mysql基准测试的产品 ,可以参见 《高性能MySQL第三版》

一、安装

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

查看 README

[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 script3. 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 

二、准备工作

网上的一个脚本:tpcc_load_parallel.sh

这里 WAREHOUSE=10

#!/bin/bash                                                              # Configration MYSQL=mysqlTPCCLOAD=./tpcc_loadTABLESQL=./create_table.sqlCONSTRAINTSQL=./add_fkey_idx.sqlDEGREE=`getconf _NPROCESSORS_ONLN` SERVER=192.168.1.104DATABASE=tpccUSER=rootPASS=123456WAREHOUSE=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=()  fidone for PID in ${PIDLIST[@]}; do  wait $PID   if [ $? -ne 0 ]; then    STATUS=1  fidone 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.0001180, 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.4591190, 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.0001200, 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结果值(每分钟事务数)

四、使用gnuplot 绘图

绘图脚本

./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.datapaste 512m-tpcc-data.data 1g-tpcc-data.data 2g-tpcc-data.data > tpcc-data.data./tpcc-graph.sh tpcc-data.data tpcc.jpg

tpcc_analyze.sh 

#!/bin/bashTIMESLOT=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 } } ' 

tpcc_graph.sh

#!/bin/bashgnuplot << EOP set style line 1 lt 1 lw 3set style line 2 lt 5 lw 3set style line 3 lt 7 lw 3set terminal png size 960,480set grid x yset 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 

 

可能出现的错误

Could not find/open font when opening font "arial", using internal non-scalable font

export GDFONTPATH=/usr/share/fonts/liberationexport GNUPLOT_DEFAULT_GDFONT=LiberationSans-Regularsource ~/.bashrc

  

参考文章

《高性能MySQL第三版》

http://blog.itpub.net/26250550/viewspace-1102011/

http://imysql.com/2014/10/10/tpcc-mysql-full-user-manual.shtml

https://securfox.wordpress.com/2009/09/02/plotting-cpu-temperature-with-gnuplot/

https://niftybits.wordpress.com/2008/03/28/gnuplot-dstat-easy-graphing-on-linux/

http://blog.chinaunix.net/uid-12087380-id-4152297.html

https://github.com/yejr/MyDBAtools/tree/master/bench-tools