磐维BenchmarkSQL压测配置

一、配置BenchmarkSQL主机

安装依赖软件包

yum install gcc glibc-headers gcc-c++ gcc-gfortran readline-devel libXt-devel pcre-devel libcurl libcurl-devel -y

yum install ncurses ncurses-devel autoconf automake zlib zlib-devel bzip2 bzip2-devel xz-devel -y

yum install java-1.8.0-openjdk -y

安装R语言(generateReport.sh脚本需要)

yum install pango-devel pango libpng-devel cairo cairo-devel ## 使R语言支持png图片，否则报告生成有问题

tar -zxf R-3.6.3.tar.gz

cd R-3.6.3

./configure && make && make install

## 如果需要重新安装，请参考以下步骤 ##

make uninstall

./configure

make

make install

• 编译安装htop(服务器端和客户端都安装)

unzip htop-main.zip

cd htop-main

./autogen.sh && ./configure && make && make install

安装ant

tar -xf apache-ant-1.9.16-bin.tar.gz

cp apache-ant-1.9.16/bin/ant /usr/bin/

• 检查安装情况(java/ant/htop)

[root@benchmarksql ~]# ant -version

Apache Ant(TM) version 1.9.4 compiled on November 5 2018

(如果提示tools.jar不存在，则把包里的tools.jar拷贝到对应路径下）

[root@benchmarksql ~]# java -version

openjdk version "1.8.0_262"

OpenJDK Runtime Environment (build 1.8.0_262-b10)

OpenJDK 64-Bit Server VM (build 25.262-b10, mixed mode)

[root@benchmarksql ~]# htop --version

htop 3.0.5

[root@prod ~]# R --version

R version 3.6.3 (2020-02-29) -- "Holding the Windsock"

Copyright (C) 2020 The R Foundation for Statistical Computing

Platform: x86_64-pc-linux-gnu (64-bit)

2. 准备软件

• 解压软件及JDBC驱动

[root@benchmarksql ~]# cd /soft/

[root@benchmarksql soft]# ls

benchmarksql-5.0.zip panwei.tar.gz

[root@benchmarksql soft]# unzip benchmarksql-5.0.zip

[root@benchmarksql soft]# tar -xf PanWeiDB-2.0.0-openEuler-64bit-Jdbc.tar.gz

• 替换默认的postgresql驱动

[root@benchmarksql ~]# cd /soft/benchmarksql-5.0/lib/postgres/

[root@benchmarksql postgres]# mv postgresql-9.3-1102.jdbc41.jar postgresql-9.3-1102.jdbc41.jar.bak

[root@benchmarksql postgres]# mv /soft/PanWeiDB-2.0.0.jar .

[root@benchmarksql postgres]# ls

postgresql-9.3-1102.jdbc41.jar.bak postgresql.jar

• 使用ant编译

[root@benchmarksql ~]# cd /soft/benchmarksql-5.0/

[root@benchmarksql benchmarksql-5.0]# ant

Buildfile: /soft/benchmarksql-5.0/build.xml

init:

[mkdir] Created dir: /soft/benchmarksql-5.0/build

compile:

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

dist:

[mkdir] Created dir: /soft/benchmarksql-5.0/dist

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

BUILD SUCCESSFUL

Total time: 2 seconds

3. 配置软件

• 配置props文件(配置文件切忌多余空格，否则会出现各种错误)

[root@benchmarksql ~]# cd /soft/benchmarksql-5.0/run

[root@benchmarksql run]# vi props.panwei.1000w

------------------------------------------------------------

db=postgres

driver=org.panweidb.Driver

// 修改连接字符串, 包含IP、端口号、数据库

conn=jdbc:panweidb://192.168.0.11:26000/tpcc1000?prepareThreshold=1&batchMode=on&fetchsize=10

// 设置数据库登录用户和密码。

user=jack

password=panwei@123

warehouses=20 //仓库数，每个Warehouse的数据量大小约为76823.04KB

loadWorkers=8 //用于在数据库中初始化数据的加载进程数量，默认为4 (建议填写CPU核数)

terminals=6 //终端数即：并发客户端数量, 跟服务端最大work数对应，通常设置为CPU线程总数的2～6倍

runTxnsPerTerminal=0 //每个终端(terminal)运行的固定事务数量,当该参数为非0时,runMins参数必须等于零

runMins=5 //要运行指定的分钟,当该参数为非0时,runTxnsPerTerminal必须等于零

limitTxnsPerMin=0 //每分钟总事务数

terminalWarehouseFixed=false //在4.x兼容模式下运行时，设置为True。设置为false以均匀使用整个配置的数据库。

//以下五个值相加之和为100 (45、43、4、4和4的默认百分比与TPC-C规范匹配)

newOrderWeight=45

paymentWeight=43

orderStatusWeight=4

deliveryWeight=4

stockLevelWeight=4

//创建文件夹以收集详细结果数据。

//通过注释取消此内容。

resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS

osCollectorScript=./misc/os_collector_linux.py

osCollectorInterval=1

//收集OS负载信息。

osCollectorSSHAddr=omm@192.168.0.11

osCollectorDevices=net_eth0 blk_vda

------------------------------------------------------------

• 配置tableCreates.sql脚本,调整表的表空间分布(充分利用多块磁盘的IO)

[root@benchmarksql ~]# cd /soft/benchmarksql-5.0/run/sql.common

[root@benchmarksql sql.common]# vi tableCreates.sql

------------------------------------------------------------

create table bmsql_config (

cfg_name varchar(30) primary key,

cfg_value varchar(50)

) with (segment=on);

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)

)WITH (FILLFACTOR=80,segment=on);

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)

)WITH (FILLFACTOR=80,segment=on);

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)

)WITH (FILLFACTOR=80,segment=on);

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)

)WITH (FILLFACTOR=80,segment=on);

create table bmsql_new_order (

no_w_id integer not null,

no_d_id integer not null,

no_o_id integer not null

)WITH (FILLFACTOR=80,segment=on);

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

)WITH (FILLFACTOR=80,segment=on);

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)

)WITH (FILLFACTOR=80,segment=on);

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

) with (segment=on);

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)

)WITH (FILLFACTOR=80,segment=on) ;

------------------------------------------------------------

4. 配置与数据库服务器的ssh互信

[root@benchmarksql ~]# ssh-keygen -t rsa

[root@benchmarksql ~]# ssh-copy-id omm@192.168.0.11

二、配置panwei DB主机

1. 创建数据库及用户

postgres=# create user jack with sysadmin identified by 'panwei@123';

CREATE ROLE

postgres=# create database tpcc1000 encoding='UTF-8' owner=jack;

CREATE DATABASE

2. 配置pg_hba.conf

[omm@prod ~]$

gs_guc set -I all -N all -c "password_encryption_type=1"

gs_guc reload -N all -I all -h "host tpcc1000 jack 0.0.0.0/0 md5"

gs_guc reload -N all -I all -h "host tpcc1000 jack 0.0.0.0/0 sha256"

gs_om -t restart

三、BenchmarkSQL主机发起测试

1. 导入测试数据

[root@benchmarksql ~]# cd /soft/benchmarksql-5.0/run

[root@benchmarksql run]# ./runDatabaseBuild.sh props.panwei.1000w ## 执行前，请务必将props.panwei.1000w文件的所有注释和多余空格删除，否则可能报错

-- ----

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

-- indexes built and extras created.

-- PostgreSQL version.

-- ----

vacuum analyze;

## 当执行完毕vacuum后，使用ctrl+c手动终止数据载入进程。

2. 运行TPCC测试

[root@benchmarksql ~]# cd /soft/benchmarksql-5.0/run

[root@benchmarksql run]# ./runBenchmark.sh props.panwei.1000w

## 执行前，请务必将props.panwei.1000w文件的所有注释和多余空格删除，否则可能报错

## terminals参数设置有大小范围要求，太大会报错：ERROR jTPCC : Term-00, Invalid number of terminals!y

四、查看测试结果

1. runBenchmark.sh运行结果

14:57:19,609 [Thread-3] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 14193.09

14:57:19,609 [Thread-3] INFO jTPCC : Term-00, Measured tpmTOTAL = 31578.42

14:57:19,609 [Thread-3] INFO jTPCC : Term-00, Session Start = 2021-01-19 14:52:19

14:57:19,609 [Thread-3] INFO jTPCC : Term-00, Session End = 2021-01-19 14:57:19

14:57:19,609 [Thread-3] INCC : Term-00, Transaction Count = 157899

2. 运行时的htop数据

3. html报告查看

## 检查R语言是否支持png

# R

> capabilities()

jpeg png tiff tcltk X11 aqua

FALSE TRUE FALSE FALSE FALSE FALSE

http/ftp sockets libxml fifo cledit iconv

TRUE TRUE TRUE TRUE TRUE TRUE

NLS profmem cairo ICU long.double libcurl

TRUE FALSE TRUE TRUE TRUE TRUE

## 生成html报告

[root@benchmarksql run]# ./generateReport.sh my_result_2021-01-19_145218/

Generating my_result_2021-01-19_145218//tpm_nopm.png ... OK

Generating my_result_2021-01-19_145218//latency.png ... OK

Generating my_result_2021-01-19_145218//cpu_utilization.png ... OK

Generating my_result_2021-01-19_145218//dirty_buffers.png ... OK

Generating my_result_2021-01-19_145218//blk_vda_iops.png ... OK

Generating my_result_2021-01-19_145218//blk_vda_kbps.png ... OK

Generating my_result_2021-01-19_145218//net_eth0_iops.png ... OK

Generating my_result_2021-01-19_145218//net_eth0_kbps.png ... OK

Generating my_result_2021-01-19_145218//report.html ... OK ## HTML报告

[root@benchmarksql ~]# cd /soft/benchmarksql-5.0/run/my_result_2021-01-19_145218

[root@benchmarksql my_result_2021-01-19_145218]# ls

blk_vda_iops.png cpu_utilization.png dirty_buffers.png net_eth0_iops.png report.html tpm_nopm.png

blk_vda_kbps.png data latency.png net_eth0_kbps.png run.properties