转自:http://www.percona.com/blog/2013/07/01/tpcc-mysql-simple-usage-steps-and-how-to-build-graphs-with-gnuplot/
Lots of times we could see different benchmarks performed by tpcc-mysql. So today I want to tell you about how to use tpcc-mysql and how to build graphs with gnuplot in a few easy steps.
As an example I’ll compare Percona Server 5.5 (latest version: 5.5.31) performance by changing InnoDB buffer pool size: innodb_buffer_pool_size = 256M / innodb_buffer_pool_size = 768M on my old test machine
System Info
- CPU: Intel(R) Pentium(R) 4 CPU 1.80GHz
- MemTotal: 1543732 kB
- OS: LinuxMint 15 (based on Ubuntu 13.04)
Files
You can find the source code of all files at the end of this post
Installation
- Install latest Percona Server
- Install tpcc-mysql
sudo apt-get install bzr
bzr branch lp:~percona-dev/perconatools/tpcc-mysql
make all
In this case it’s installed to ~/tpcc-mysql/ directory
- Install gnuplot
sudo apt-get install gnuplot
DB Config
First test will be running with innodb_buffer_pool_size = 256M option enabled and second one withinnodb_buffer_pool_size = 768M
Test for innodb_buffer_pool_size = 256M
Create DB
Assuming that Percona Server 5.5.31 installed and configured
cd ~/tpcc-mysql
mysql -u root -p -e "CREATE DATABASE tpcc1000;"
mysql -u root -p tpcc1000 < create_table.sql
mysql -u root -p tpcc1000 < add_fkey_idx.sql
Load Data
./tpcc_load 127.0.0.1 tpcc1000 root "root-password" 20
Where:
- Host: 127.0.0.1
- DB: tpcc1000
- User: root
- Password: root-password
- Warehouse: 20
...DATA LOADING COMPLETED SUCCESSFULLY.
In this case DB size is 1.9GB
Run tpcc-mysql test
./tpcc_start -h127.0.0.1 -dtpcc1000 -uroot -p -w20 -c16 -r10 -l1200 > ~/tpcc-output-ps-55-bpool-256.log
Where:
- Host: 127.0.0.1
- DB: tpcc1000
- User: root
- Warehouse: 20
- Connection: 16
- Rampup time: 10 (sec)
- Measure: 1200 (sec)
The most interesting part in the output is:
MEASURING START.
10, 25(17):9.005|9.221, 21(0):1.866|1.869, 3(0):0.647|0.840, 1(0):0.000|10.614, 2(2):19.999|29.490
20, 22(14):9.419|9.555, 26(0):1.591|1.593, 2(0):0.593|0.788, 4(0):10.453|10.688, 3(3):19.999|22.962
30, 41(32):8.703|9.057, 32(0):1.615|1.662, 3(0):0.588|0.777, 2(0):9.530|10.495, 3(2):19.999|22.983
The first two values are “time range” and “transactions”, so you can read it as:
0-10 sec, 25 transactions
10-20 sec, 22 transactions
20-30 sec, 41 transactions
Test for innodb_buffer_pool_size = 768M
Repeat following steps for innodb_buffer_pool_size = 768M (change it in my.cnf) and get results:
- DB Config
- Create DB
- Load Data
- Run tpcc-mysql test
./tpcc_start -h127.0.0.1 -dtpcc1000 -uroot -p -w20 -c16 -r10 -l1200 > ~/tpcc-output-ps-55-bpool-768.log
There are 2 files: tpcc-output-ps-55-bpool-256.log and tpcc-output-ps-55-bpool-768.log which have benchmarking results for both tests.
Generate data file for each test
./tpcc-output-analyze.sh ~/tpcc-output-ps-55-bpool-256.log > tpcc-256-data.txt
./tpcc-output-analyze.sh ~/tpcc-output-ps-55-bpool-768.log > tpcc-768-data.txt
Merge data files
paste tpcc-256-data.txt tpcc-768-data.txt > tpcc-graph-data.txt
Build graph
./tpcc-graph-build.sh tpcc-graph-data.txt tpcc-graph.jpg
In this case tpcc-graph-data.txt is a filename of source datafile and tpcc-graph.jpg filename of graph which will be generated
Graph ready: tpcc-graph.jpg
Note: “using 3:4 … with lines axes x1y1″ in tpcc-graph-build.sh means that columns number 3 and 4 in datafile will be used for as axises x and y accordingly while building second line
File listing
tpcc-output-analyze.sh (I got it there and a bit modified)
TIMESLOT=1
if [ -n “$2″ ]
then
TIMESLOT=$2
echo “Defined $2″
ficat $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 %3dn”,$1,(aggr/’$TIMESLOT’)) ; cntr=0; aggr=0 } } ‘
tpcc-graph-build.sh
#!/bin/bash
### goto user homedir and remove previous file
rm -f ‘$2′gnuplot << EOP
### set data source file
datafile = ‘$1′### set graph type and size
set terminal jpeg size 640,480### set titles
set grid x y
set xlabel “Time (sec)”
set ylabel “Transactions”### set output filename
set output ‘$2′### build graph
# plot datafile with lines
plot datafile title “PS 5.5.1, buffer pool: 256M” with lines,
datafile using 3:4 title “PS 5.5.1, buffer pool: 768M” with lines axes x1y1EOP