PostgreSQL数据库TPCC测试，Banchmarksql 5.0部署详解
PostgreSQL数据库TPCC测试，Banchmarksql 5.0部署详解
·
1 BenchmarkSQL安装部署
1.1 部署Java环境
首先使用java - version查看是否已有 Java环境（下图是有的情况）
需要注意的是，虚拟机中默认的JDK貌似是不行的哦，请参考下面链接中的博文，教你怎么卸载重装
1.1.1 若没有Java环境
参考该博文：在虚拟机安装JDK
1.1.2 若有Java环境
- 使用which java查看java安装在了哪里
[root@localhost software]# which java
/opt/module/jdk1.8.0_212/bin/java
可知安装在了/opt/module/jdk1.8.0_212
若一次没找出来，可以用
ls -lrt命令顺藤摸瓜
1.2 安装ant工具。
yum -y install ant
没有yum源的话也可到https://ant.apache.org/bindownload.cgi自行下载apache-ant-1.9.14-bin.zip
1.3 编辑jdk和ant环境变量
[postgres@localhost /]$ vim ~/.bash_profile
其中加入以下内容
export PATH=$PATH:$HOME/.ft
export JAVA_HOME=/opt/module/jdk1.8.0_212
export ANT_HOME=/opt/module/tpcc/apache-ant-1.9.14
export JDK_PATH=${JAVA_HOME}/bin:${JAVA_HOME}/jre/bin
export JDK_CLASSPATH=${JAVA_HOME}/lib:${JAVA_HOME}/jre/lib
export PATH=${JDK_PATH}:${ANT_HOME}/bin:${PATH}
保存退出后，要记得source
source ~/.bash_profile
1.4 下载benchmarksql 5.0 源码包，解压编译。
- 下载地址
https://sourceforge.net/projects/benchmarksql/
- 上传解压
[postgres@localhost benchmarksql-5.0]$ su - postgres --我这里使用的是postgres用户，切换到对应的测试用户即可
[postgres@localhost benchmarksql-5.0]$ cd /opt/module/tpcc
[postgres@localhost benchmarksql-5.0]$ unzip benchmarksql-5.0.zip
[postgres@localhost benchmarksql-5.0]$ cd benchmarksql-5.0
[postgres@localhost benchmarksql-5.0]$ ant
Buildfile: /opt/module/tpcc/benchmarksql-5.0/build.xml
init:
[mkdir] Created dir: /opt/module/tpcc/benchmarksql-5.0/build
compile:
[javac] Compiling 11 source files to /opt/module/tpcc/benchmarksql-5.0/build
dist:
[mkdir] Created dir: /opt/module/tpcc/benchmarksql-5.0/dist
[jar] Building jar: /opt/module/tpcc/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar
BUILD SUCCESSFUL
Total time: 0 seconds
此时编译后的benchmarkSQL默认支持PostgresqlSQL：
[postgres@localhost tpcc]$ cat benchmarksql-5.0/src/client/jTPCC.java | grep postgres
else if (iDB.equals("postgres"))
2 测试数据准备及执行
2.1 编辑props_file文件
vim /opt/module/tpcc/benchmarksql-5.0/run/props.pg_cn01
db=postgres
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5432/benchmarksql <<<填写需要测试的目标环境 ip、port和database name
user=postgres <<< DB用户名
password=postgres <<< DB用户密码
warehouses=1
loadWorkers=4
terminals=1
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=10
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=0
//Number of total transactions per minute
limitTxnsPerMin=300
//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_eth0 blk_sda
2.2 修改表结构和索引
如果是分布式系统，则需要考虑分布式环境下是否需要显式主键、分布键、外键、sequence等，可根据具体的SQL兼容性问题修改具体的DDL语句：
[root@localhost sql.common]$ pwd
/opt/module/tpcc/benchmarksql-5.0/run/sql.common
[root@localhost sql.common]# ll
total 28
-rw-r--r--. 1 postgres postgres 117 May 25 2016 buildFinish.sql
-rw-r--r--. 1 postgres postgres 1392 May 25 2016 foreignKeys.sql
-rw-r--r--. 1 postgres postgres 960 May 25 2016 indexCreates.sql
-rw-r--r--. 1 postgres postgres 620 May 25 2016 indexDrops.sql
-rw-r--r--. 1 postgres postgres 3068 May 25 2016 tableCreates.sql
-rw-r--r--. 1 postgres postgres 306 May 25 2016 tableDrops.sql
-rw-r--r--. 1 postgres postgres 282 May 25 2016 tableTruncates.sql
2.3 初始化测试数据：
- 创建测试库
[root@localhost ~]# su - postgres
Last login: Sat Dec 10 17:51:14 PST 2022 on pts/0
[postgres@localhost ~]$ psql
psql (14.4)
Type "help" for help.
postgres=# create database benchmarksql;
CREATE DATABASE
- 根据上述props.pg_cn01的配置，建立仓库
[postgres@localhost run]$ ./runDatabaseBuild.sh props.pg_cn01
# ------------------------------------------------------------
# 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=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5432/benchmarksql
user=postgres
password=***********
warehouses=1
loadWorkers=4
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')
Worker 000: Loading ITEM
Worker 001: Loading Warehouse 1
Worker 000: Loading ITEM done
Worker 001: Loading Warehouse 1 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;
- 执行benchmark测试
[postgres@localhost run]$ pwd
/opt/module/tpcc/benchmarksql-5.0/run
[postgres@localhost run]$ ./runBenchmark.sh props.pg_cn01
18:11:00,274 [main] INFO jTPCC : Term-00,
18:11:00,276 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
18:11:00,276 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
18:11:00,276 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
18:11:00,276 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
18:11:00,276 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
18:11:00,278 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
18:11:00,278 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
18:11:00,278 [main] INFO jTPCC : Term-00,
18:11:00,278 [main] INFO jTPCC : Term-00, db=postgres
18:11:00,278 [main] INFO jTPCC : Term-00, driver=org.postgresql.Driver
18:11:00,278 [main] INFO jTPCC : Term-00, conn=jdbc:postgresql://localhost:5432/benchmarksql
18:11:00,279 [main] INFO jTPCC : Term-00, user=postgres
18:11:00,279 [main] INFO jTPCC : Term-00,
18:11:00,279 [main] INFO jTPCC : Term-00, warehouses=1
18:11:00,279 [main] INFO jTPCC : Term-00, terminals=1
18:11:00,279 [main] INFO jTPCC : Term-00, runTxnsPerTerminal=10
18:11:00,279 [main] INFO jTPCC : Term-00, limitTxnsPerMin=300
18:11:00,279 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
18:11:00,279 [main] INFO jTPCC : Term-00,
18:11:00,279 [main] INFO jTPCC : Term-00, newOrderWeight=45
18:11:00,280 [main] INFO jTPCC : Term-00, paymentWeight=43
18:11:00,280 [main] INFO jTPCC : Term-00, orderStatusWeight=4
18:11:00,280 [main] INFO jTPCC : Term-00, deliveryWeight=4
18:11:00,280 [main] INFO jTPCC : Term-00, stockLevelWeight=4
18:11:00,280 [main] INFO jTPCC : Term-00,
18:11:00,280 [main] INFO jTPCC : Term-00, resultDirectory=null
18:11:00,280 [main] INFO jTPCC : Term-00, osCollectorScript=null
18:11:00,280 [main] INFO jTPCC : Term-00,
18:11:00,354 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 156
18:11:00,354 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 226
18:11:00,354 [main] INFO jTPCC : Term-00, Te18:11:02,377 [Thread-0] INFO jTPCC : Term-00, nt tpmTOTAL: 84 Memory Usage: 7MB / 2918:11:02,377 [Thread-0] INFO jTPCC : Term-00,
18:11:02,377 [Thread-0] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 89.68
18:11:02,378 [Thread-0] INFO jTPCC : Term-00, Measured tpmTOTAL = 328.84
18:11:02,378 [Thread-0] INFO jTPCC : Term-00, Session Start = 2022-12-10 18:11:00
18:11:02,378 [Thread-0] INFO jTPCC : Term-00, Session End = 2022-12-10 18:11:02
18:11:02,378 [Thread-0] INFO jTPCC : Term-00, Transaction Count = 10
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
0
0- 0
已为社区贡献4条内容
所有评论(0)