数据库压测模型TPC-C/TPC-H
在数据库选型、新系统上线时,常常需要对数据库性能进行测试。数据库性能测试的工具有很多,比如常见的sysbench,但sysbench只能进行单表的压测,不能模拟接近生产系统的复杂的业务模型。
TPC , Transaction Processing Performance Council,是一个非盈利组织,成立于1988年,这个组织主要的功能是定义事务处理、数据库的基准,这个基准用于评估服务器的性能,并且把服务器评估的结果发布在TPC的官方网站上。简单的讲TPC就是系统评测皇家科学院。
绝大部分数据库产品在发布时都会进行TPC压力测试,阿里OceanBase在2019年10月的TPC-C测试中,跑出6000万+的测试成绩,成为TPC-C榜单第一名。
针对不同的业务场景,TPC有对应的不同的测试模型,比较常用的有针对OLTP的TPC-C及针对OLAP的TPC-H,本文详细介绍TPC-C及TPC-H测试模型及测试过程。
一、TPC-C
TPC-C is an On-Line Transaction Processing Benchmark.
TPC-C is measured in transactions per minute (tpmC).
TPC-C是业界常用的一套benchmark,由TPC委员会制定发布,用于评测数据库的联机交易处理(OLTP)能力。主要涉及10张表,包含五类业务事务模型(NewOrder–新订单的生成、Payment–订单付款、OrderStatus–最近订单查询、Delivery–配送、StockLevel–库存缺货状态分析)。
TPC-C事务模型
TPC-C需要处理的交易事务主要为以下几种:
1、新订单(New-Order) :客户输入一笔新的订货交易;
2、支付操作(Payment) :更新客户帐户余额以反映其支付状况;
3、发货(Delivery) :发货(模拟批处理交易);
4、订单状态查询(Order-Status) :查询客户最近交易的状态;
5、库存状态查询(Stock-Level) :查询仓库库存状况,以便能够及时补货。
TPC-C通过tpmC值(Transactions per Minute)来衡量系统最大有效吞吐量(MQTh,Max Qualified Throughput),其中Transactions以NewOrder Transaction为准,即最终衡量单位为每分钟处理的新订单数
TPC-C性能衡量指标tpmC
流量指标(Throughput,简称tpmC):按照TPC组织的定义,流量指标描述了系统在执行支付操作、订单状态查询、发货和库存状态查询这4种交易的同时,每分钟可以处理多少个新订单交易。所有交易的响应时间必须满足TPC-C测试规范的要求,且各种交易数量所占的比例也应该满足TPC-C测试规范的要求。在这种情况下,流量指标值越大说明系统的联机事务处理能力越高。
依据TPC-C模型,有很多不同的测试工具,如Percorna公司的TPCC-MYSQL, BenchmarkSQL等。因为BenchmarkSQL支持多种TPC模型,并且支持不同的数据库类型(如Oracle, PG, MySQL), 本篇文章介绍BenchmarkSQL.
BenchmarkSQL TPC-C测试mysql演示:
1.登录MySQL数据库服务器,创建数据库tpcc。
mysql> create database tpcc;
show databases;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tpcc |
+--------------------+
5 rows in set (0.00 sec)
mysql> use tpcc;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql>
2.BenchmarkSQL可以在官网下载5.0(测试mysql需要做修改),或者下载pingcap修改过的测试mysql的版本 https://github.com/pingcap/benchmarksql
unzip BenchMarkSQL.zip
cd /home/BenchMarkSQL/run
vi props.mysql
[root@mysql5702 run]# cat props.mysql
db=mysql
driver=com.mysql.cj.jdbc.Driver
conn=jdbc:mysql://192.168.56.58:3306/tpcc
user=root
password=oracle
warehouses=5
loadWorkers=20
terminals=10
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=5
//Number of total transactions per minute
limitTxnsPerMin=1000000000
//Set to true to run in 4.x compatible mode. Set to false to use the
//entire configured database evenly.
terminalWarehouseFixed=true
//The following five values must add up to 100
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
// Directory name to create for collecting detailed result data.
// Comment this out to suppress.
//resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
//osCollectorScript=./misc/os_collector_linux.py
//osCollectorInterval=1
//osCollectorSSHAddr=user@dbhost
//osCollectorDevices=net_eth0 blk_sda
[root@mysql5702 run]#
warehouses=5 --warehouses数量,决定load多大的数据量,1个warehouse对应30000customer,300000数据量
terminals=10 --并发数
runMins=10 --测试多场时间(分钟)
- 登录Benchmarksql客户端,向MySQL数据库服务器导入数据(创建表并初始化)。
#./runDatabaseBuild.sh props.mysql
该脚本将调用对应的sql文件,创建表、索引并load数据,有兴趣可以查看run目录下的各个sh文件内容
完成后,可以看到9张业务表1张配置表已经创建:
mysql> show tables;
+------------------+
| Tables_in_tpcc |
+------------------+
| bmsql_config |
| bmsql_customer |
| bmsql_district |
| bmsql_history |
| bmsql_item |
| bmsql_new_order |
| bmsql_oorder |
| bmsql_order_line |
| bmsql_stock |
| bmsql_warehouse |
+------------------+
10 rows in set (0.01 sec)
mysql>
4.登录Benchmarksql客户端,执行Benchmarksql程序对MySQL数据库服务器进行压力测试。
./runBenchmark.sh props.mysql
按照配置的runMins=10 执行10分钟的TPC-C压测,并得出最终的tpmC值。
tpmC (NewOrders) = 30872.68
[root@10-90-28-162.test-test.bjht run]# ./runBenchmark.sh props.mysql
18:50:31,673 [main] INFO jTPCC : Term-00,
18:50:31,675 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
18:50:31,675 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
18:50:31,675 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
18:50:31,675 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
18:50:31,675 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
18:50:31,677 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
18:50:31,677 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
18:50:31,677 [main] INFO jTPCC : Term-00,
18:50:31,677 [main] INFO jTPCC : Term-00, db=mysql
18:50:31,677 [main] INFO jTPCC : Term-00, driver=com.mysql.cj.jdbc.Driver
18:50:31,677 [main] INFO jTPCC : Term-00, conn=jdbc:mysql://10.90.155.30:33066/tpcc
18:50:31,677 [main] INFO jTPCC : Term-00, user=root
18:50:31,678 [main] INFO jTPCC : Term-00,
18:50:31,678 [main] INFO jTPCC : Term-00, warehouses=10
18:50:31,678 [main] INFO jTPCC : Term-00, terminals=5
18:50:31,679 [main] INFO jTPCC : Term-00, runMins=10
18:50:31,679 [main] INFO jTPCC : Term-00, limitTxnsPerMin=1000000000
18:50:31,679 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
18:50:31,679 [main] INFO jTPCC : Term-00,
18:50:31,679 [main] INFO jTPCC : Term-00, newOrderWeight=45
18:50:31,679 [main] INFO jTPCC : Term-00, paymentWeight=43
18:50:31,679 [main] INFO jTPCC : Term-00, orderStatusWeight=4
18:50:31,680 [main] INFO jTPCC : Term-00, deliveryWeight=4
18:50:31,680 [main] INFO jTPCC : Term-00, stockLevelWeight=4
18:50:31,680 [main] INFO jTPCC : Term-00,
18:50:31,680 [main] INFO jTPCC : Term-00, resultDirectory=null
18:50:31,680 [main] INFO jTPCC : Term-00, osCollectorScript=null
18:50:31,680 [main] INFO jTPCC : Term-00,
18:50:31,893 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 229
18:50:31,894 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 114
18:50:31,894 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 68679.74 Current tpmTOTAL: 4542300 Memory Usage: 157MB / 1442MB
19:00:31,958 [Thread-1] INFO jTPCC : Term-00,
19:00:31,958 [Thread-1] INFO jTPCC : Term-00,
19:00:31,959 [Thread-1] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 30872.68
19:00:31,959 [Thread-1] INFO jTPCC : Term-00, Measured tpmTOTAL = 68679.22
19:00:31,959 [Thread-1] INFO jTPCC : Term-00, Session Start = 2020-12-28 18:50:31
19:00:31,959 [Thread-1] INFO jTPCC : Term-00, Session End = 2020-12-28 19:00:31
19:00:31,959 [Thread-1] INFO jTPCC : Term-00, Transaction Count = 686805
[root@10-90-28-162.test-test.bjht run]#
5.清理测试数据表
./runDatabaseDestroy.sh props.mysql
32C180G内存,10 warehouse 下MySQL和PG TPC-C测试结果:
二、TPC-H
TPC-H 是业界常用的一套 Benchmark,由 TPC 委员会制定发布,用于评测数据库的分析型查询能力。TPC-H 查询包含 8 张数据表、22 条复杂的 SQL 查询,大多数查询包含若干表 Join、子查询和 Group-by 聚合等。
简单来说,TPC-H就是通过22个复杂SQL查询来评估数据库OLAP的性能。
22条SQL业务逻辑分析可参考TPC-H官方文档或 https://yq.aliyun.com/articles/149715
The TPC Benchmark™H (TPC-H) is a decision support benchmark. It consists of a suite of business oriented ad-hoc queries and concurrent data modifications. The queries and the data populating the database have been chosen to have broad industry-wide relevance while maintaining a sufficient degree of ease of implementation. This benchmark illustrates decision support systems that
• Examine large volumes of data;
• Execute queries with a high degree of complexity;
• Give answers to critical business questions.
TPC-H测试mysql演示:
- 官方TPC-H工具包可在官网 http://www.tpc.org/下载(官网的不支持MySQL,需要做一些修改)
- unzip TPC-H_Tools_v2.18.0.zip, 解压后进入/usr/local/2.18.0_rc2/dbgen 修改 makefile.suite文件,修改103~112行。
################
CC = gcc
Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
SQLSERVER, SYBASE, ORACLE, VECTORWISE
Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS,
SGI, SUN, U2200, VMS, LINUX, WIN32
Current values for WORKLOAD are: TPCH
DATABASE= MYSQL
MACHINE = LINUX
WORKLOAD = TPCH
改好之后,保存成makefile文件
cp makefile.suite makefile
3. 修改tpcd.h文件。
打开tpcd.h,在文件末尾加入下面的宏定义。
#ifdef MYSQL
#define GEN_QUERY_PLAN ""
#define START_TRAN "START TRANSACTION"
#define END_TRAN "COMMIT"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "limit %d;\n"
#define SET_DBASE "use %s;\n"
#endif
- 编译
在dbgen目录下编译: make
编译之后会生成很多的.o目标文件 - 生成.tbl数据文件。
接下来要用dbgen生成数据,一共会生成8个表(.tbl)。生成1G数据。其中1表示生成1G数据。如果你想生成10G,将1改为10。可以通过./dbgen -h查看dbgen的参数选项。
./dbgen -s 1
[root@mysql5702 dbgen]# ./dbgen -s 1
TPC-H Population Generator (Version 2.18.0)
Copyright Transaction Processing Performance Council 1994 - 2010
[root@mysql5702 dbgen]# ls *tbl
customer.tbl lineitem.tbl nation.tbl orders.tbl partsupp.tbl part.tbl region.tbl supplier.tbl
[root@mysql5702 dbgen]#
6.修改初始化脚本使MySQL可用。
dss.ddl:建表脚本
dss.ri:创建primary key和foreign key脚本
不过这些脚本不能直接在MySQL里用,需要修改。
6.1 修改dss.ddl
dss.ddl的开头需要加上一些给MySQL建立数据库连接用的指令。在最前面加上如下代码:
DROP DATABASE tpch;
CREATE DATABASE tpch;
USE tpch;
并且将表名全部改为小写字母。
[root@mysql5701 dbgen]# cat dss.ddl
-- Sccsid: @(#)dss.ddl 2.1.8.1
DROP DATABASE tpch;
CREATE DATABASE tpch;
USE tpch;
create table nation ( n_nationkey integer not null,
n_name char(25) not null,
n_regionkey integer not null,
n_comment varchar(152));
create table region ( r_regionkey integer not null,
r_name char(25) not null,
r_comment varchar(152));
create table part ( p_partkey integer not null,
p_name varchar(55) not null,
p_mfgr char(25) not null,
p_brand char(10) not null,
p_type varchar(25) not null,
p_size integer not null,
p_container char(10) not null,
p_retailprice decimal(15,2) not null,
p_comment varchar(23) not null );
create table supplier ( s_suppkey integer not null,
s_name char(25) not null,
s_address varchar(40) not null,
s_nationkey integer not null,
s_phone char(15) not null,
s_acctbal decimal(15,2) not null,
s_comment varchar(101) not null);
create table partsupp ( ps_partkey integer not null,
ps_suppkey integer not null,
ps_availqty integer not null,
ps_supplycost decimal(15,2) not null,
ps_comment varchar(199) not null );
create table customer ( c_custkey integer not null,
c_name varchar(25) not null,
c_address varchar(40) not null,
c_nationkey integer not null,
c_phone char(15) not null,
c_acctbal decimal(15,2) not null,
c_mktsegment char(10) not null,
c_comment varchar(117) not null);
create table orders ( o_orderkey integer not null,
o_custkey integer not null,
o_orderstatus char(1) not null,
o_totalprice decimal(15,2) not null,
o_orderdate date not null,
o_orderpriority char(15) not null,
o_clerk char(15) not null,
o_shippriority integer not null,
o_comment varchar(79) not null);
create table lineitem ( l_orderkey integer not null,
l_partkey integer not null,
l_suppkey integer not null,
l_linenumber integer not null,
l_quantity decimal(15,2) not null,
l_extendedprice decimal(15,2) not null,
l_discount decimal(15,2) not null,
l_tax decimal(15,2) not null,
l_returnflag char(1) not null,
l_linestatus char(1) not null,
l_shipdate date not null,
l_commitdate date not null,
l_receiptdate date not null,
l_shipinstruct char(25) not null,
l_shipmode char(10) not null,
l_comment varchar(44) not null);
[root@mysql5701 dbgen]#
6.2 修改dss.ri
USE tpch;
alter table tpch.region add primary key (r_regionkey);
alter table tpch.nation add primary key (n_nationkey);
create index idx1_nation on tpch.nation(n_regionkey);
commit work;
alter table tpch.part add primary key (p_partkey);
commit work;
alter table tpch.supplier add primary key (s_suppkey);
create index idx1_supplier on tpch.supplier(s_nationkey);
commit work;
alter table tpch.partsupp add primary key (ps_partkey,ps_suppkey);
commit work;
alter table tpch.customer add primary key (c_custkey);
create index idx1_customer on tpch.customer(c_nationkey);
commit work;
alter table tpch.lineitem add primary key (l_orderkey,l_linenumber);
commit work;
alter table tpch.orders add primary key (o_orderkey);
commit work;
create index idx1_partsupp on tpch.partsupp(ps_suppkey);
commit work;
create index idx2_partsupp on tpch.partsupp(ps_partkey);
commit work;
create index idx1_orders on tpch.orders(o_custkey);
commit work;
create index idx1_lineitem on tpch.lineitem(l_orderkey);
commit work;
create index idx2_lineitem on tpch.lineitem(l_partkey,l_suppkey);
commit work;
7.建tpch数据库及表。
调用dss.ddl创建库及表
mysql> source /usr/local/2.18.0_rc2/dbgen/dss.ddl
调用dss.ri创建主键及索引
mysql> source /usr/local/2.18.0_rc2/dbgen/dss.ri
8.导入数据。
创建以下load.sh文件并执行,生成loaddata.sql:
[root@mysql5702 dbgen]# cat load.sh
#!/bin/bash
write_to_file()
{
file="loaddata.sql"
if [ ! -f "$file" ] ; then
touch "$file"
fi
echo 'USE tpch;' >> $file
echo 'SET FOREIGN_KEY_CHECKS=0;' >> $file
DIR=pwd
for tbl in ls *.tbl
; do
table=$(echo "${tbl%.*}")
echo "LOAD DATA LOCAL INFILE '$DIR/$tbl' INTO TABLE $table" >> $file
echo "FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';" >> $file
done
echo 'SET FOREIGN_KEY_CHECKS=1;' >> $file
}
write_to_file
[root@mysql5702 dbgen]#
[root@mysql5702 dbgen]# cat loaddata.sql
USE tpch;
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/usr/local/2.18.0_rc2/dbgen/customer.tbl' INTO TABLE customer
FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA LOCAL INFILE '/usr/local/2.18.0_rc2/dbgen/lineitem.tbl' INTO TABLE lineitem
FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA LOCAL INFILE '/usr/local/2.18.0_rc2/dbgen/nation.tbl' INTO TABLE nation
FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA LOCAL INFILE '/usr/local/2.18.0_rc2/dbgen/orders.tbl' INTO TABLE orders
FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA LOCAL INFILE '/usr/local/2.18.0_rc2/dbgen/partsupp.tbl' INTO TABLE partsupp
FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA LOCAL INFILE '/usr/local/2.18.0_rc2/dbgen/part.tbl' INTO TABLE part
FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA LOCAL INFILE '/usr/local/2.18.0_rc2/dbgen/region.tbl' INTO TABLE region
FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
LOAD DATA LOCAL INFILE '/usr/local/2.18.0_rc2/dbgen/supplier.tbl' INTO TABLE supplier
FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';
SET FOREIGN_KEY_CHECKS=1;
[root@mysql5702 dbgen]#
执行loaddata.sql导入数据:
mysql> source /usr/local/2.18.0_rc2/dbgen/loaddata.sql
9.生成查询sql语句
将qgen执行文件和dists.dss文件拷贝到queries模板目录中。
cp qgen dists.dss queries/
mkdir tpch_sql
export DSS_QUERY=queries
for((i=1;i<=22;i++));
do
queries/qgen -d $i > tpch_sql/$i.sql
done
10.执行sql 1-22,记录执行时间。ke可以参考以下polarDB整理表格和图表。