percona tpcc mysql_percona tpcc-mysql 简介

os: centos 7.4

db: mysql 5.7

software: tpcc-mysql

TPC-C是专门针对联机交易处理系统(OLTP系统)的规范。

tpcc-mysql是percona基于TPC-C(下面简写成TPCC)衍生出来的产品，专用于MySQL基准测试

下载

安装

本次采用zip方式

# vi /etc/profile

export PATH=$PATH:/usr/local/mysql/bin/

# source /etc/profile

# cd /tmp/tpccmysql

# ls

tpcc-mysql-master.zip

# unzip ./tpcc-mysql-master.zip

# cd tpcc-mysql-master/

# cd src

# make

cc -w -O3 -g -I. `mysql_config --include` -c load.c

cc -w -O3 -g -I. `mysql_config --include` -c support.c

cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load

cc -w -O3 -g -I. `mysql_config --include` -c main.c

cc -w -O3 -g -I. `mysql_config --include` -c spt_proc.c

cc -w -O3 -g -I. `mysql_config --include` -c driver.c

cc -w -O3 -g -I. `mysql_config --include` -c sequence.c

cc -w -O3 -g -I. `mysql_config --include` -c rthist.c

cc -w -O3 -g -I. `mysql_config --include` -c sb_percentile.c

cc -w -O3 -g -I. `mysql_config --include` -c neword.c

cc -w -O3 -g -I. `mysql_config --include` -c payment.c

cc -w -O3 -g -I. `mysql_config --include` -c ordstat.c

cc -w -O3 -g -I. `mysql_config --include` -c delivery.c

cc -w -O3 -g -I. `mysql_config --include` -c slev.c

cc main.o spt_proc.o driver.o support.o sequence.o rthist.o sb_percentile.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start

# cd ..

# ls -l

total 308

-rw-r--r-- 1 root root 1621 Jan 21 2017 add_fkey_idx.sql-rw-r--r-- 1 root root 317 Jan 21 2017 count.sql-rw-r--r-- 1 root root 3105 Jan 21 2017 create_table.sql-rw-r--r-- 1 root root 194 Jan 21 2017 Dockerfile-rw-r--r-- 1 root root 763 Jan 21 2017 drop_cons.sql-rw-r--r-- 1 root root 1079 Jan 21 2017 load_multi_schema.sh-rw-r--r-- 1 root root 573 Jan 21 2017 load.sh-rw-r--r-- 1 root root 2302 Jan 21 2017 README.mddrwxr-xr-x 2 root root 92 Jan 21 2017 schema2

drwxr-xr-x 5 root root 4096 Jan 21 2017 scripts

drwxr-xr-x 2 root root 4096 Mar 27 09:59 src

-rwxr-xr-x 1 root root 81128 Mar 27 09:59 tpcc_load-rwxr-xr-x 1 root root 188656 Mar 27 09:59 tpcc_start

可以看到，生成了 tpcc_load、tpcc_start

tpcc_load 初始化数据

# ./tpcc_load

./tpcc_load: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

需要修改下ld.so.conf

# vi /etc/ld.so.conf

/usr/local/mysql/lib

# ldconfig

# ./tpcc_load

*************************************

*** TPCC-mysql Data Loader ***

*************************************

[server]:

[port]: 3306

[DBname]:

[user]:

[pass]:

[warehouse]: 0

2002, HY000, Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

必读README

# more ./README.md

# ./tpcc_load --help

*************************************

*** TPCC-mysql Data Loader ***

*************************************

./tpcc_load: invalid option -- '-'

Usage: tpcc_load -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -l part -m min_wh -n max_wh

*[part]: 1=ITEMS 2=WAREHOUSE 3=CUSTOMER 4=ORDERS

创建数据库，用户

# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 26

Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> create database tpcc1000;

Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> create user 'tpcc'@'localhost' identified by 'tpcc';

Query OK, 0 rows affected (0.47 sec)

MySQL [(none)]> grant all privileges on tpcc1000.* to 'tpcc'@'localhost';

Query OK, 0 rows affected (0.09 sec)

创建表

# mysql -h localhost -utpcc -ptpcc -D tpcc1000 < /tmp/tpccmysql/tpcc-mysql-master/create_table.sql

创建索引和约束

# mysql -h localhost -utpcc -ptpcc -D tpcc1000 < /tmp/tpccmysql/tpcc-mysql-master/add_fkey_idx.sql

生成数据

# ./tpcc_load -h localhost -P 3306 -d tpcc1000 -u tpcc -p tpcc -w 100

tpcc_start 基准测试

Start benchmark

* `./tpcc_start -h127.0.0.1 -P3306 -dtpcc1000 -uroot -w1000 -c32 -r10 -l10800`

* |hostname| |port| |dbname| |user| |WAREHOUSES| |CONNECTIONS| |WARMUP TIME| |BENCHMARK TIME|

* ref. tpcc_start --help for all options

# ./tpcc_start --help

***************************************

*** ###easy### TPC-C Load Generator ***

***************************************

./tpcc_start: invalid option -- '-'

Usage: tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouses -c connections -r warmup_time -l running_time -i report_interval -f report_file -t trx_file

# ./tpcc_start -h localhost -P 3306 -d tpcc1000 -u tpcc -p tpcc -w 100 -c 5 -r 10 -l 108 -i 10 -f /tmp/tpccmysql_report

***************************************

*** ###easy### TPC-C Load Generator ***

***************************************

option h with value 'locatlhost'

option P with value '3306'

option d with value 'tpcc1000'

option u with value 'tpcc'

option p with value 'tpcc'

option w with value '100'

option c with value '5'

option r with value '10'

option l with value '108'

option i with value '10'

option f with value '/tmp/tpccmysql_report'

[server]: localhost

[port]: 3306

[DBname]: tpcc1000

[user]: tpcc

[pass]: tpcc

[warehouse]: 100

[connection]: 5

[rampup]: 10 (sec.)

[measure]: 108 (sec.)

RAMP-UP TIME.(10 sec.)

MEASURING START.

10, trx: 19, 95%: 2123.212, 99%: 2235.391, max_rt: 2235.300, 15|1274.832, 2|405.809, 1|5469.900, 3|5013.233

20, trx: 12, 95%: 2268.420, 99%: 2708.222, max_rt: 2708.016, 11|1013.782, 2|801.527, 3|6213.653, 0|0.000

30, trx: 10, 95%: 2441.034, 99%: 2441.034, max_rt: 2441.223, 15|1401.447, 1|241.209, 0|0.000, 2|11813.174

40, trx: 12, 95%: 2332.457, 99%: 2584.664, max_rt: 2584.994, 10|1544.592, 1|582.419, 2|5734.152, 0|0.000

50, trx: 12, 95%: 2387.557, 99%: 3062.773, max_rt: 3063.001, 12|1370.608, 1|276.632, 0|0.000, 1|13032.978

60, trx: 10, 95%: 2519.730, 99%: 2519.730, max_rt: 2519.780, 11|1657.740, 1|321.442, 2|4129.371, 2|15166.151

70, trx: 12, 95%: 2307.457, 99%: 2743.307, max_rt: 2743.424, 12|2025.975, 1|197.238, 0|0.000, 1|8579.419

80, trx: 14, 95%: 2807.272, 99%: 2925.665, max_rt: 2925.712, 13|1846.569, 1|902.453, 2|5998.984, 1|7364.332

90, trx: 8, 95%: 2459.370, 99%: 2459.370, max_rt: 2459.063, 11|1826.119, 2|575.905, 1|5734.772, 1|11927.816

100, trx: 12, 95%: 2332.457, 99%: 2457.163, max_rt: 2457.393, 11|1271.223, 0|0.000, 1|5079.375, 1|12257.926

STOPPING THREADS.....

[0] sc:0 lt:121 rt:0 fl:0 avg_rt: 1954.6 (5)

[1] sc:0 lt:121 rt:0 fl:0 avg_rt: 961.7 (5)

[2] sc:0 lt:12 rt:0 fl:0 avg_rt: 464.6 (5)

[3] sc:0 lt:12 rt:0 fl:0 avg_rt: 5744.0 (80)

[4] sc:0 lt:12 rt:0 fl:0 avg_rt: 10996.1 (20)

in 100 sec.

[0] sc:0 lt:121 rt:0 fl:0

[1] sc:0 lt:121 rt:0 fl:0

[2] sc:0 lt:12 rt:0 fl:0

[3] sc:0 lt:12 rt:0 fl:0

[4] sc:0 lt:12 rt:0 fl:0

(all must be [OK])

[transaction percentage]

Payment: 43.53% (>=43.0%) [OK]

Order-Status: 4.32% (>= 4.0%) [OK]

Delivery: 4.32% (>= 4.0%) [OK]

Stock-Level: 4.32% (>= 4.0%) [OK]

[response time (at least 90% passed)]

New-Order: 0.00% [NG] *

Payment: 0.00% [NG] *

Order-Status: 0.00% [NG] *

Delivery: 0.00% [NG] *

Stock-Level: 0.00% [NG] *

72.600 TpmC