Tpcc-mysql是percona基於tpcc衍生出來專用於mysql基準測試的產品 ,可以參見 《高性能MySQL第三版》一、安裝rpm -Uvh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpmyum...
Tpcc-mysql是percona基於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
查看 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 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
二、準備工作
網上的一個腳本:tpcc_load_parallel.sh
這裡 WAREHOUSE=10
#!/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結果值(每分鐘事務數)
四、使用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.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
tpcc_analyze.sh
#!/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 } } '
tpcc_graph.sh
#!/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
可能出現的錯誤
Could not find/open font when opening font "arial", using internal non-scalable font
export GDFONTPATH=/usr/share/fonts/liberation export GNUPLOT_DEFAULT_GDFONT=LiberationSans-Regular source ~/.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