Tpcc-MySQL是percona基于tpcc衍生出来专用于mysql基准测试的产品 ，可以参见 《高性能MySQL第三版》
一、安装
rpm -Uvh 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秒