标签：TPC data kingbase DS 测试 sql home bmsql id

TPC-H、TPC-H、TPC-DS部署测试

概述

TPC-C

TPC-C是业界常用的一套Benchmark，用于评估在线事务处理（OLTP）系统性能的基准测试。它模拟了一个商品批发公司的销售模型，包括管理订单、管理库存、管理账号收支等操作。TPC-C测试的核心是新订单操作，用于衡量系统每分钟所能处理的交易数量（tpmC）。

TPC-H

TPC-H是业界常用的一套Benchmark，用于评测数据库的分析型查询能力，TPC-H 侧重于OLAP。TPC-H查询包含8张数据表、22条复杂的SQL查询，大多数查询包含若干表Join、子查询和Group-by聚合等。

TPC-DS

TPC-DS采用星型、雪花型等多维数据模式，TPC-DS侧重于OLAP。它包含7张事实表，17张纬度表平均每张表含有18列。其工作负载包含99个SQL查询，覆盖SQL99和2003的核心部分以及OLAP。这个测试集包含对大数据集的统计、报表生成、联机查询、数据挖掘等复杂应用，测试用的数据和值是有倾斜的，与真实数据一致。

TPC-C部署测试

一、BenchMarkSQL工具部署与配置

1.1使用rz命令上传BenchMarkSQL软件至/home/kingbase中，并解压

[root@kingbase ~]# su - kingbase

[kingbase@kingbase ~]$ ll benchmarksql-5.0.zip

-rw-r--r-- 1 kingbase kingbase 2263539 11月 4 18:15 benchmarksql-5.0.zip

[kingbase@kingbase ~]$ unzip benchmarksql-5.0.zip

1.2配置JDBC

[kingbase@kingbase ~]$ cd benchmarksql-5.0/lib

[kingbase@kingbase lib]$ mkdir -p kingbase8

[kingbase@kingbase lib]$ cp /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0020/Interface/jdbc/kingbase*.jar /home/kingbase/benchmarksql-5.0/lib/kingbase8

[kingbase@kingbase lib]$ cp /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0020/Interface/jdbc/kingbase8-8.6.0.jar /home/kingbase/benchmarksql-5.0/lib

[kingbase@kingbase lib]$ ll kingbase8

-rw-rw-r-- 1 kingbase kingbase 1189389 11月 5 14:54 kingbase8-8.6.0.jar

-rw-rw-r-- 1 kingbase kingbase 1045436 11月 5 14:54 kingbase8-8.6.0.jre6.jar

-rw-rw-r-- 1 kingbase kingbase 1185028 11月 5 14:54 kingbase8-8.6.0.jre7.jar

[kingbase@kingbase lib]$ ll kingbase8-8.6.0.jar

-rw-rw-r-- 1 kingbase kingbase 1189389 11月 5 14:54 kingbase8-8.6.0.jar

1.3使用ant重新编绎

（1）上传并解压apache-ant-1.10.15-bin.zip至/home/kingbase

[kingbase@kingbase ~]$ cd /home/kingbase

[kingbase@kingbase ~]$ ls -l apache-ant-1.10.15-bin.zip

[kingbase@kingbase ~]$ unzip apache-ant-1.10.15-bin.zip

（2）上传并解压jdk-8u341-linux-x64.tar.gz，并配置jdk环境变量

[kingbase@kingbase ~]$ cd /home/kingbase

[kingbase@kingbase ~]$ ls -l jdk-8u341-linux-x64.tar.gz

[kingbase@kingbase ~]$ tar -xvf jdk-8u341-linux-x64.tar.gz

配置jdk环境变量：

[kingbase@kingbase ~]$ vi ~/.bash_profile

添加如下内容：

export ANT_HOME=/home/kingbase/apache-ant-1.10.15

export PATH=$PATH:$ANT_HOME/bin

export JAVA_HOME=/home/kingbase/jdk1.8.0_341

export JRE_HOME=$JAVA_HOME/jre

export CLASSPATH=$JAVA_HOME/lib:$JRE_HOME/lib:$CLASSPATH

export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$PATH

生效环境变量：

[kingbase@kingbase ~]$ source ~/.bash_profile

（3）使用ant重编译benchmarksql

[kingbase@kingbase ~]$ cd /home/kingbase/benchmarksql-5.0

[kingbase@kingbase benchmarksql-5.0]$ ant clean

Buildfile: /home/kingbase/benchmarksql-5.0/build.xml

clean:

[delete] Deleting directory /home/kingbase/benchmarksql-5.0/build

BUILD SUCCESSFUL

Total time: 1 second

[kingbase@kingbase benchmarksql-5.0]$ ant

Buildfile: /home/kingbase/benchmarksql-5.0/build.xml

init:

[mkdir] Created dir: /home/kingbase/benchmarksql-5.0/build

compile:

[javac] Compiling 11 source files to /home/kingbase/benchmarksql-5.0/build

dist:

[mkdir] Created dir: /home/kingbase/benchmarksql-5.0/dist

[jar] Building jar: /home/kingbase/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar

BUILD SUCCESSFUL

Total time: 4 seconds

二、准备测试数据

2.1创建tpcc用户并授予相关权限

[kingbase@kingbase ~]$ ksql -Usystem -dtest

用户 system 的口令：

输入 "help" 来获取帮助信息.

test=# CREATE USER tpcc with superuser password 'tpcc';

CREATE ROLE

test=# GRANT ALL privileges on database test to tpcc;

GRANT

2.2修改benchmarksql配置文件

编辑./run/props.kingbase文件，并修改内容：

[kingbase@kingbase ~]$ cd /home/kingbase/benchmarksql-5.0

[kingbase@kingbase benchmarksql-5.0]$ cp ./run/props.pg ./run/props.kingbase

[kingbase@kingbase benchmarksql-5.0]$ vi ./run/props.kingbase

修改后内容参照如下

db=postgres

driver=com.kingbase8.Driver

conn=jdbc:kingbase8://192.168.40.111:54321/test

user=tpcc

password=tpcc

warehouses=10 ##仓数(10仓大概1.12G数据)

loadWorkers=4 ##加载数据的进程数

terminals=1 ##并发数

//To run specified transactions per terminal- runMins must equal zero

runTxnsPerTerminal=0

//To run for specified minutes- runTxnsPerTerminal must equal zero

runMins=5 ##压测时间(分钟)：一般20分钟左右，视情况而定

//Number of total transactions per minute

limitTxnsPerMin=0

//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

//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec

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_enp0s3 blk_sda

2.3添加测试表及测试数据

[kingbase@kingbase ~]$ cd /home/kingbase/benchmarksql-5.0/run

[kingbase@kingbase run]$ chmod +x *.sh

[kingbase@kingbase run]$ bash runDatabaseBuild.sh props.kingbase

# ------------------------------------------------------------

# Loading SQL file ./sql.common/tableCreates.sql

# ------------------------------------------------------------

create table bmsql_config (

cfg_name varchar(30) primary key,

cfg_value varchar(50)

);

create table bmsql_warehouse (

w_id integer not null,

w_ytd decimal(12,2),

w_tax decimal(4,4),

w_name varchar(10),

w_street_1 varchar(20),

w_street_2 varchar(20),

w_city varchar(20),

w_state char(2),

w_zip char(9)

);

create table bmsql_district (

d_w_id integer not null,

d_id integer not null,

d_ytd decimal(12,2),

d_tax decimal(4,4),

d_next_o_id integer,

d_name varchar(10),

d_street_1 varchar(20),

d_street_2 varchar(20),

d_city varchar(20),

d_state char(2),

d_zip char(9)

);

create table bmsql_customer (

c_w_id integer not null,

c_d_id integer not null,

c_id integer not null,

c_discount decimal(4,4),

c_credit char(2),

c_last varchar(16),

c_first varchar(16),

c_credit_lim decimal(12,2),

c_balance decimal(12,2),

c_ytd_payment decimal(12,2),

c_payment_cnt integer,

c_delivery_cnt integer,

c_street_1 varchar(20),

c_street_2 varchar(20),

c_city varchar(20),

c_state char(2),

c_zip char(9),

c_phone char(16),

c_since timestamp,

c_middle char(2),

c_data varchar(500)

);

create sequence bmsql_hist_id_seq;

create table bmsql_history (

hist_id integer,

h_c_id integer,

h_c_d_id integer,

h_c_w_id integer,

h_d_id integer,

h_w_id integer,

h_date timestamp,

h_amount decimal(6,2),

h_data varchar(24)

);

create table bmsql_new_order (

no_w_id integer not null,

no_d_id integer not null,

no_o_id integer not null

);

create table bmsql_oorder (

o_w_id integer not null,

o_d_id integer not null,

o_id integer not null,

o_c_id integer,

o_carrier_id integer,

o_ol_cnt integer,

o_all_local integer,

o_entry_d timestamp

);

create table bmsql_order_line (

ol_w_id integer not null,

ol_d_id integer not null,

ol_o_id integer not null,

ol_number integer not null,

ol_i_id integer not null,

ol_delivery_d timestamp,

ol_amount decimal(6,2),

ol_supply_w_id integer,

ol_quantity integer,

ol_dist_info char(24)

);

create table bmsql_item (

i_id integer not null,

i_name varchar(24),

i_price decimal(5,2),

i_data varchar(50),

i_im_id integer

);

create table bmsql_stock (

s_w_id integer not null,

s_i_id integer not null,

s_quantity integer,

s_ytd integer,

s_order_cnt integer,

s_remote_cnt integer,

s_data varchar(50),

s_dist_01 char(24),

s_dist_02 char(24),

s_dist_03 char(24),

s_dist_04 char(24),

s_dist_05 char(24),

s_dist_06 char(24),

s_dist_07 char(24),

s_dist_08 char(24),

s_dist_09 char(24),

s_dist_10 char(24)

);

Starting BenchmarkSQL LoadData

driver=com.kingbase8.Driver

conn=jdbc:kingbase8://192.168.56.5:54321/test

user=tpcc

password=***********

warehouses=10

loadWorkers=4

fileLocation (not defined)

csvNullValue (not defined - using default 'NULL')

Worker 000: Loading ITEM

Worker 001: Loading Warehouse 1

Worker 002: Loading Warehouse 2

Worker 003: Loading Warehouse 3

Worker 000: Loading ITEM done

Worker 000: Loading Warehouse 4

Worker 001: Loading Warehouse 1 done

Worker 001: Loading Warehouse 5

Worker 002: Loading Warehouse 2 done

Worker 002: Loading Warehouse 6

Worker 003: Loading Warehouse 3 done

Worker 003: Loading Warehouse 7

Worker 000: Loading Warehouse 4 done

Worker 000: Loading Warehouse 8

Worker 001: Loading Warehouse 5 done

Worker 000: Loading Warehouse 9

Worker 002: Loading Warehouse 6 done

Worker 002: Loading Warehouse 10

Worker 003: Loading Warehouse 7 done

Worker 000: Loading Warehouse 8 done

Worker 000: Loading Warehouse 9 done

Worker 002: Loading Warehouse 10 done

# ------------------------------------------------------------

# Loading SQL file ./sql.common/indexCreates.sql

# ------------------------------------------------------------

alter table bmsql_warehouse add constraint bmsql_warehouse_pkey

primary key (w_id);

alter table bmsql_district add constraint bmsql_district_pkey

primary key (d_w_id, d_id);

alter table bmsql_customer add constraint bmsql_customer_pkey

primary key (c_w_id, c_d_id, c_id);

create index bmsql_customer_idx1

on bmsql_customer (c_w_id, c_d_id, c_last, c_first);

alter table bmsql_oorder add constraint bmsql_oorder_pkey

primary key (o_w_id, o_d_id, o_id);

create unique index bmsql_oorder_idx1

on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);

alter table bmsql_new_order add constraint bmsql_new_order_pkey

primary key (no_w_id, no_d_id, no_o_id);

alter table bmsql_order_line add constraint bmsql_order_line_pkey

primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);

alter table bmsql_stock add constraint bmsql_stock_pkey

primary key (s_w_id, s_i_id);

alter table bmsql_item add constraint bmsql_item_pkey

primary key (i_id);

# ------------------------------------------------------------

# Loading SQL file ./sql.common/foreignKeys.sql

# ------------------------------------------------------------

alter table bmsql_district add constraint d_warehouse_fkey

foreign key (d_w_id)

references bmsql_warehouse (w_id);

alter table bmsql_customer add constraint c_district_fkey

foreign key (c_w_id, c_d_id)

references bmsql_district (d_w_id, d_id);

alter table bmsql_history add constraint h_customer_fkey

foreign key (h_c_w_id, h_c_d_id, h_c_id)

references bmsql_customer (c_w_id, c_d_id, c_id);

alter table bmsql_history add constraint h_district_fkey

foreign key (h_w_id, h_d_id)

references bmsql_district (d_w_id, d_id);

alter table bmsql_new_order add constraint no_order_fkey

foreign key (no_w_id, no_d_id, no_o_id)

references bmsql_oorder (o_w_id, o_d_id, o_id);

alter table bmsql_oorder add constraint o_customer_fkey

foreign key (o_w_id, o_d_id, o_c_id)

references bmsql_customer (c_w_id, c_d_id, c_id);

alter table bmsql_order_line add constraint ol_order_fkey

foreign key (ol_w_id, ol_d_id, ol_o_id)

references bmsql_oorder (o_w_id, o_d_id, o_id);

alter table bmsql_order_line add constraint ol_stock_fkey

foreign key (ol_supply_w_id, ol_i_id)

references bmsql_stock (s_w_id, s_i_id);

alter table bmsql_stock add constraint s_warehouse_fkey

foreign key (s_w_id)

references bmsql_warehouse (w_id);

alter table bmsql_stock add constraint s_item_fkey

foreign key (s_i_id)

references bmsql_item (i_id);

# ------------------------------------------------------------

# Loading SQL file ./sql.postgres/extraHistID.sql

# ------------------------------------------------------------

-- ----

-- Extra Schema objects/definitions for history.hist_id in PostgreSQL

-- ----

-- This is an extra column not present in the TPC-C

-- specs. It is useful for replication systems like

-- Bucardo and Slony-I, which like to have a primary

-- key on a table. It is an auto-increment or serial

-- column type. The definition below is compatible

-- with Oracle 11g, using a sequence and a trigger.

-- ----

-- Adjust the sequence above the current max(hist_id)

select setval('bmsql_hist_id_seq', (select max(hist_id) from bmsql_history));

-- Make nextval(seq) the default value of the hist_id column.

alter table bmsql_history

alter column hist_id set default nextval('bmsql_hist_id_seq');

-- Add a primary key history(hist_id)

alter table bmsql_history add primary key (hist_id);

# ------------------------------------------------------------

# Loading SQL file ./sql.postgres/buildFinish.sql

# ------------------------------------------------------------

-- ----

-- Extra commands to run after the tables are created, loaded,

-- indexes built and extra's created.

-- PostgreSQL version.

-- ----

vacuum analyze;

[kingbase@kingbase run]$

三、调整数据库配置

3.1调整对象的约束

[kingbase@kingbase ~]$ ksql -Usystem -dtest

用户 system 的口令：

输入 "help" 来获取帮助信息.

test=# CREATE INDEX idx_oorder_2 ON bmsql_oorder(o_w_id,o_d_id,o_c_id,o_id);

CREATE INDEX

test=# CHECKPOINT;

CHECKPOINT

3.2根据硬件配置调整相关参数

根据实际情况而定：

1.调整系统参数：关闭透明大页、打开网卡的large-receive-offload(lro)功能等

2.网卡中断核心数绑定

3.修改网卡的mtu

4.禁用numa_balancing

5.数据库参数调优

6.绑核启动数据库等等

四、开始测试

4.1连接测试用户

[kingbase@kingbase ~]$ ksql -Usystem -dtest

用户 system 的口令：

输入 "help" 来获取帮助信息.

test=# CREATE EXTENSION sys_prewarm; ##创建sys_prewarm扩展，进行数据预热

test=# SELECT * FROM sys_available_extensions WHERE name like '%warm%';

4.2预加热数据

SELECT sys_prewarm('bmsql_config');

SELECT sys_prewarm('bmsql_customer');

SELECT sys_prewarm('bmsql_district');

SELECT sys_prewarm('bmsql_hist_id_seq');

SELECT sys_prewarm('bmsql_history');

SELECT sys_prewarm('bmsql_item');

SELECT sys_prewarm('bmsql_new_order');

SELECT sys_prewarm('bmsql_oorder');

SELECT sys_prewarm('bmsql_order_line');

SELECT sys_prewarm('bmsql_stock');

SELECT sys_prewarm('bmsql_warehouse');

SELECT sys_prewarm('bmsql_config_pkey');

SELECT sys_prewarm('bmsql_customer_idx1');

SELECT sys_prewarm('bmsql_customer_pkey');

SELECT sys_prewarm('bmsql_district_pkey');

SELECT sys_prewarm('bmsql_item_pkey');

SELECT sys_prewarm('bmsql_new_order_pkey');

SELECT sys_prewarm('bmsql_oorder_idx1');

SELECT sys_prewarm('bmsql_oorder_pkey');

SELECT sys_prewarm('bmsql_order_line_pkey');

SELECT sys_prewarm('bmsql_warehouse_pkey');

SELECT sys_prewarm('IDX_OORDER_2');

4.3测试

[kingbase@kingbase ~]$ cd /home/kingbase/benchmarksql-5.0/run

[kingbase@kingbase run]$ bash runBenchmark.sh props.kingbase

4.4测试结果

测试结果：

附：htop工具安装

1.使用rz命令上传htop-2.2.0.tar.gz到服务器并解压

[root@kingbase ~]# gunzip htop-2.2.0.tar.gz

[root@kingbase ~]# tar -xvf htop-2.2.0.tar

2.安装依赖包

[root@kingbase ~]# yum install gcc ncurses-devel -y

3.编译安装

[root@kingbase ~]# cd htop-2.2.0/

[root@kingbase htop-2.2.0]# ./configure

[root@kingbase htop-2.2.0]# make && make install

[root@kingbase ~]# htop

TPC-H部署测试

一、TPC-H工具部署与配置

1.1使用rz命令上传TPC-H-Tool_v3.0.1.zip软件至/home/kingbase中，并解压

[root@kingbase ~]# su - kingbase

[kingbase@kingbase ~]$ unzip TPC-H-Tool_v3.0.1.zip

[kingbase@kingbase ~]$ mv TPC-H\ V3.0.1 tpc-h-tool

1.2编辑修改makefile.suite

[kingbase@kingbase ~]$ cd /home/kingbase/tpc-h-tool/dbgen/

[kingbase@kingbase dbgen]$ vi makefile.suite

--在CC后填写gcc
--在DATABASE后填写POSTGRESQL
--在MACHINE后填写LINUX
--在WORKLOAD后填写TPCH

CC = gcc

DATABASE= POSTGRESQL

MACHINE = LINUX

WORKLOAD = TPCH

1.3编辑tpcd.h，添加以下宏

[kingbase@kingbase ~]$ cd /home/kingbase/tpc-h-tool/dbgen/

[kingbase@kingbase dbgen]$ vi tpcd.h

在文件最后添加以下宏即可：

#ifdef POSTGRESQL

#define GEN_QUERY_PLAN "EXPLAIN"

#define START_TRAN "BEGIN TRANSACTION"

#define END_TRAN "COMMIT;"

#define SET_OUTPUT ""

#define SET_ROWCOUNT "LIMIT %d\n"

#define SET_DBASE ""

#endif

1.4执行编译安装

[kingbase@kingbase ~]$ cd /home/kingbase/tpc-h-tool/dbgen/

[kingbase@kingbase dbgen]$ make -f makefile.suite

二、准备测试数据

2.1创建数据库及表空间

[kingbase@kingbase ~]$ ksql -Usystem -dtest

用户 system 的口令：

输入 "help" 来获取帮助信息.

test=# create database tpch;

CREATE DATABASE

tpch=# create tablespace tpch location '/data/tpch';

CREATE TABLESPACE

test=# alter database tpch set tablespace tpch;

ALTER DATABASE

2.2创建测试表

##在dbgen目录下的dss.ddl文件，定义了表的初始化语句（8张表）

TPC-H包括8张表,如下：

PART：表示零件的信息

SUPPLIER：表示供货商的信息

PARTSUPP：表示供货商的零件的信息

CUSTOMER：表示消费者的信息

ORDERS：表示订单的信息

LINEITEM：表示在线商品的信息

NATION：表示国家的信息

REGION：表示地区的信息

[kingbase@kingbase ~]$ ksql -Usystem -dtpch

用户 system 的口令：

输入 "help" 来获取帮助信息.

tpch=# \i /home/kingbase/tpc-h-tool/dbgen/dss.ddl

tpch=# \d

2.3使用dbgen生成测试数据

[kingbase@kingbase ~]$ cd /home/kingbase/tpc-h-tool/dbgen

[kingbase@kingbase dbgen]$ time ./dbgen -s 1

## -s参数指定数据量，单位为G，生成1G数据

[kingbase@kingbase dbgen]$ time ./dbgen -s 10 ##生成10G数据

##将生成的8个tbl文件移动至data目录下

[kingbase@kingbase dbgen]$ mv *.tbl /data

2.4导入测试数据

##导入数据前需要先将dbgen生成的数据中每一行最后一个“|”分隔符去除，否则导入数据会报错

##使用如下命令去掉每行末尾的"|"：

[kingbase@kingbase dbgen]$ cd /data

[kingbase@kingbase dbgen]$ for i in `ls *.tbl` do name=$i echo $name sed -i 's/|$//' $i done

连接tpch数据库，使用COPY命令将dbgen生成的测试数据导入数据库：

[kingbase@kingbase ~]$ ksql -Usystem -dtpch

导入测试数据（根据实际情况修改路径）：

copy customer from '/data/customer.tbl' DELIMITERS '|';

copy lineitem from '/data/lineitem.tbl' DELIMITERS '|';

copy nation from '/data/nation.tbl' DELIMITERS '|';

copy orders from '/data/orders.tbl' DELIMITERS '|';

copy partsupp from '/data/partsupp.tbl' DELIMITERS '|';

copy part from '/data/part.tbl' DELIMITERS '|';

copy region from '/data/region.tbl' DELIMITERS '|';

copy supplier from '/data/supplier.tbl' DELIMITERS '|';

vacuum ANALYZE ;

2.5创建约束

连接tpch数据库，创建约束：

[kingbase@kingbase ~]$ ksql -Usystem -dtpch

主键约束：

ALTER TABLE PART ADD PRIMARY KEY (P_PARTKEY);

ALTER TABLE SUPPLIER ADD PRIMARY KEY (S_SUPPKEY);

ALTER TABLE PARTSUPP ADD PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY);

ALTER TABLE CUSTOMER ADD PRIMARY KEY (C_CUSTKEY);

ALTER TABLE ORDERS ADD PRIMARY KEY (O_ORDERKEY);

ALTER TABLE LINEITEM ADD PRIMARY KEY (L_ORDERKEY, L_LINENUMBER);

ALTER TABLE NATION ADD PRIMARY KEY (N_NATIONKEY);

ALTER TABLE REGION ADD PRIMARY KEY (R_REGIONKEY);

外键约束：

ALTER TABLE SUPPLIER ADD FOREIGN KEY (S_NATIONKEY) REFERENCES NATION(N_NATIONKEY);

ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_PARTKEY) REFERENCES PART(P_PARTKEY);

ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER(S_SUPPKEY);

ALTER TABLE CUSTOMER ADD FOREIGN KEY (C_NATIONKEY) REFERENCES NATION(N_NATIONKEY);

ALTER TABLE ORDERS ADD FOREIGN KEY (O_CUSTKEY) REFERENCES CUSTOMER(C_CUSTKEY);

ALTER TABLE LINEITEM ADD FOREIGN KEY (L_ORDERKEY) REFERENCES ORDERS(O_ORDERKEY);

ALTER TABLE LINEITEM ADD FOREIGN KEY (L_PARTKEY,L_SUPPKEY) REFERENCES PARTSUPP(PS_PARTKEY,PS_SUPPKEY);

ALTER TABLE NATION ADD FOREIGN KEY (N_REGIONKEY) REFERENCES REGION(R_REGIONKEY);

2.6使用qgen生成测试语句

使用下面的命令生成22条测试语句

[kingbase@kingbase ~]$ cd /home/kingbase/tpc-h-tool/dbgen

[kingbase@kingbase dbgen]$ export DSS_QUERY=./queries

生成测试语句：

[kingbase@kingbase dbgen]$ for i in {1..22} do name="$i.sql" echo $name ./qgen -d $i >$name done

[kingbase@kingbase dbgen]$ ls -l *.sql

现有生成的22条测试语句如下：

注：已在每个sql文件里添加\timing on，以便后续测试中获取sql的执行时间

注：如需重新生成测试语句，可以使用如下命令在每个.sql文件的第一行批量添加\timing on

[kingbase@kingbase ~]$ cd /home/kingbase/tpc-h-tool/dbgen/tpch_sql

执行命令批量添加：

for i in `ls *.sql`

echo $i;

sed -i '1i \\\timing on' "$i"

done

将22条测试语句拷贝到tpch_sql目录下：

[kingbase@kingbase ~]$ cd /home/kingbase/tpc-h-tool/dbgen

[kingbase@kingbase dbgen]$ mkdir tpch_sql

[kingbase@kingbase dbgen]$ cd tpch_sql

[kingbase@kingbase dbgen]$ ll

三、开始测试

3.1编辑测试脚本

[kingbase@kingbase ~]$ cd /home/kingbase/tpc-h-tool/dbgen/tpch_sql

[kingbase@kingbase tpch_sql]$ vi tpch.sh

#!/bin/bash

echo "*********************************"

echo "****** TPC-H test started. ******"

echo "*********************************"

rm -rf tpch_result.txt

for i in $(seq 1 22); do

ksql -U system -d tpch -f ${i}.sql > ${i}sql.log

echo "******" >> tpch_result.txt

echo "${i}.sql" >> tpch_result.txt

tail -n 1 "${i}sql.log" >> tpch_result.txt

echo "${i}.sql -- `tail -n 1 "${i}sql.log" | awk -F'(' '{print$1}'`"

done

echo "*****************************************************"

echo "*** TPC-H test completed, results in: tpch_result.txt"

echo "*****************************************************"

========================================

注：

此脚本中，因ksql命令本身不支持自动输入密码的功能，存在执行脚本的过程中需要手动输入数据库用户密码的问题。

对kingbase数据库配置本地免密登录方式，配置如下：

1.修改sys_hba.conf配置文件，将local认证方式scram-sha-256修改为trust即可

[kingbase@kingbase ~]$ cd /data

[kingbase@kingbase data]$ vi sys_hba.conf

2.重启数据库生效

[kingbase@kingbase ~]$ sys_ctl restart

========================================

3.2测试

[kingbase@kingbase ~]$ cd /home/kingbase/tpc-h-tool/dbgen/tpch_sql

[kingbase@kingbase tpch_sql]$ sh tpch.sh

3.3测试结果

测试结果：

[kingbase@kingbase tpch_sql]$ cat tpch_result.txt

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

1.sql

时间：25.939 ms

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

2.sql

时间：3785.785 ms (00:03.786)

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

3.sql

时间：180323.669 ms (03:00.324)

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

4.sql

时间：128452.777 ms (02:08.453)

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

5.sql

时间：126846.503 ms (02:06.847)

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

............

21.sql

时间：187096.451 ms (03:07.096)

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

22.sql

时间：39452.183 ms (00:39.452)

TPC-DS部署测试

一、TPC-DS工具部署

1.1使用rz命令上传TPC-DS-Tool_v3.2.0软件至/home/kingbase中，并解压

[root@kingbase ~]# su - kingbase

[kingbase@kingbase ~]$ unzip TPC-DS-Tool_v3.2.0

[kingbase@kingbase ~]$ mv DSGen-software-code-3.2.0rc1 tpc-ds-tool

1.2安装TPC-DS编译依赖包

[root@kingbase ~]# yum install gcc gcc-c++ -y

1.3编译TPC-DS

[kingbase@kingbase ~]$ cd tpc-ds-tool/tools/

[kingbase@kingbase tools]$ make