benchmarksql 测试Postgres

最新推荐文章于 2024-04-16 14:30:24 发布

weixin_34238642

最新推荐文章于 2024-04-16 14:30:24 发布

阅读量2k

点赞数

文章标签： java 数据库 python

原文链接：https://my.oschina.net/yafeishi/blog/742314

版权

2019独角兽企业重金招聘Python工程师标准>>>

BenchmarkSQL是什么？
安装BenchmarkSQL
使用BenchmarkSQL测试数据库

BenchmarkSQL是什么？

BenchmarkSQL是一款经典的开源数据库测试工具，内嵌了TPCC测试脚本，可以对EnterpriseDB、PostgreSQL、MySQL、Oracle以及SQL Server等数据库直接进行测试。
BenchmarkSQL的测试场景：
TPC-C 会模拟一个批发商的货物管理环境。该批发公司有N个仓库，每个仓库供应10个地区，
其中每个地区为3000 名顾客服务。在每个仓库中有10 个终端，每个终端用于一个地区。
在运行的时候，10*N 个终端操作员会向公司的数据库发送5类请求。由于一个仓库中不可能
存储公司中所有的货物，有一些请求必须发往其他的仓库，因此数据库在逻辑上是分布的。
N 是一个可变的参数值，测试则可以将 N 作为传入参数。

安装BenchmarkSQL

到项目地址：https://sourceforge.net/projects/benchmarksql/?source=typ_redirect下载zip包解压即可。
由于其使用java语言编写，所以要求运行主机安装java。

使用BenchmarkSQL测试数据库

阅读 HOW-TO-RUN.txt

比较4.1.1 和5.0的文件内容，发现5.0的使用方式有很大的变化：

需要使用ant编译源码
数据加载的方式也发生变化，需要仔细阅读 How to run 文件。
增加了对os性能指标的采集

创建测试用户和数据库

postgres@postgres:5432 # create user benchmarksql;
CREATE ROLE
postgres@postgres:5432 # create database benchmarksql owner benchmarksql encoding='utf8' template=template0;
CREATE DATABASE
postgres@postgres:5432 # \l+ benchmarksql
                                                List of databases
     Name     |    Owner     | Encoding | Collate | Ctype | Access privileges | Size  | Tablespace | Description 
--------------+--------------+----------+---------+-------+-------------------+-------+------------+-------------
 benchmarksql | benchmarksql | UTF8     | C       | C     |                   | 20 MB | pg_default | 
(1 row)


postgres@postgres:5432 # \c benchmarksql benchmarksql
You are now connected to database "benchmarksql" as user "benchmarksql".
benchmarksql@benchmarksql:5432 > \d
No relations found.

修改benchmarksql 数据库连接配置

配置文件放在 benchmarksql_home/run 目录下

[postgres@dang-db benchmarksql-5.0]$cd run
[postgres@dang-db run]$ll
total 72
-rwxr-xr-x 1 postgres postgres 1100 May 26 01:39 funcs.sh
-rwxr-xr-x 1 postgres postgres 2123 May 26 01:39 generateGraphs.sh
-rwxr-xr-x 1 postgres postgres 7256 May 26 01:39 generateReport.sh
-rwxr-xr-x 1 postgres postgres  962 May 26 01:39 log4j.properties
drwxrwxr-x 2 postgres postgres 4096 May 26 01:39 misc
-rw-rw-r-- 1 postgres postgres 1063 May 26 01:39 props.fb
-rw-rw-r-- 1 postgres postgres  947 May 26 01:39 props.ora
-rw-rw-r-- 1 postgres postgres 1021 May 26 01:39 props.pg
-rwxr-xr-x 1 postgres postgres  385 May 26 01:39 runBenchmark.sh
-rwxr-xr-x 1 postgres postgres  528 May 26 01:39 runDatabaseBuild.sh
-rwxr-xr-x 1 postgres postgres  330 May 26 01:39 runDatabaseDestroy.sh
-rwxr-xr-x 1 postgres postgres  200 May 26 01:39 runLoader.sh
-rwxr-xr-x 1 postgres postgres 1207 May 26 01:39 runSQL.sh
drwxrwxr-x 2 postgres postgres 4096 May 26 01:39 sql.common
drwxrwxr-x 2 postgres postgres 4096 May 26 01:39 sql.firebird
drwxrwxr-x 2 postgres postgres 4096 May 26 01:39 sql.oracle
drwxrwxr-x 2 postgres postgres 4096 May 26 01:39 sql.postgres
[postgres@dang-db run]$pwd
/postgres/tools/benchmarksql-5.0/run

需要对Postgres进行测试，则修改props.pg配置文件。

[postgres@dang-db run]$cp props.pg props.pg.bak
[postgres@dang-db run]$vi props.pg
db=postgres
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5432/benchmarksql
user=benchmarksql
//password=PWbmsql

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

4.1.1 版本使用

创建测试表：

[postgres@dang-db run]$./runSQL.sh props.pg sqlTableCreates
DROP SCHEMA IF EXISTS benchmarksql CASCADE;
CREATE SCHEMA benchmarksql;
create table benchmarksql.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 benchmarksql.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 benchmarksql.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  float,
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 benchmarksql.hist_id_seq;
create table benchmarksql.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 benchmarksql.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     decimal(2,0),
o_all_local  decimal(1,0),
o_entry_d    timestamp
);
create table benchmarksql.new_order (
no_w_id  integer   not null,
no_d_id  integer   not null,
no_o_id  integer   not null
);
create table benchmarksql.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     decimal(2,0),
ol_dist_info    char(24)
);
create table benchmarksql.stock (
s_w_id       integer       not null,
s_i_id       integer       not null,
s_quantity   decimal(4,0),
s_ytd        decimal(8,2),
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)
);
create table benchmarksql.item (
i_id     integer      not null,
i_name   varchar(24),
i_price  decimal(5,2),
i_data   varchar(50),
i_im_id  integer
);

benchmarksql@benchmarksql:5432 > \d
                  List of relations
    Schema    |    Name     |   Type   |    Owner     
--------------+-------------+----------+--------------
 benchmarksql | customer    | table    | benchmarksql
 benchmarksql | district    | table    | benchmarksql
 benchmarksql | hist_id_seq | sequence | benchmarksql
 benchmarksql | history     | table    | benchmarksql
 benchmarksql | item        | table    | benchmarksql
 benchmarksql | new_order   | table    | benchmarksql
 benchmarksql | oorder      | table    | benchmarksql
 benchmarksql | order_line  | table    | benchmarksql
 benchmarksql | stock       | table    | benchmarksql
 benchmarksql | warehouse   | table    | benchmarksql
(10 rows)

导入测试数据：

[postgres@dang-db run]$./runLoader.sh props.pg numWarehouses 1
Starting BenchmarkSQL LoadData
----------------- Initialization -------------------
numWarehouses
1
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5432/benchmarksql
user=benchmarksql
password=******

------------- LoadData StartTime = Thu Sep 01 11:49:05 CST 2016-------------

Start Whse Load for 1 Whses @ Thu Sep 01 11:49:05 CST 2016 ...
Elasped Time(ms): 0.034
End Whse Load @  Thu Sep 01 11:49:05 CST 2016

Start Item Load for 100000 Items @ Thu Sep 01 11:49:05 CST 2016 ...
  Elasped Time(ms): 0.175       Writing record 10000 of 100000
  Elasped Time(ms): 5.036       Writing record 20000 of 100000
  Elasped Time(ms): 4.751       Writing record 30000 of 100000
  Elasped Time(ms): 4.693       Writing record 40000 of 100000
  Elasped Time(ms): 4.874       Writing record 50000 of 100000
  Elasped Time(ms): 5.133       Writing record 60000 of 100000
  Elasped Time(ms): 5.405       Writing record 70000 of 100000
  Elasped Time(ms): 5.769       Writing record 80000 of 100000
  Elasped Time(ms): 5.091       Writing record 90000 of 100000
  Elasped Time(ms): 5.391       Writing record 100000 of 100000
  Elasped Time(ms): 5.308       Writing final records 100000 of 100000
End Item Load @  Thu Sep 01 11:49:57 CST 2016

Start Stock Load for 100000 units @ Thu Sep 01 11:49:57 CST 2016 ...
  Elasped Time(ms): 0.256       Writing record 10000 of 100000
  Elasped Time(ms): 8.577       Writing record 20000 of 100000
  Elasped Time(ms): 8.105       Writing record 30000 of 100000
  Elasped Time(ms): 8.013       Writing record 40000 of 100000
  Elasped Time(ms): 8.103       Writing record 50000 of 100000
  Elasped Time(ms): 8.194       Writing record 60000 of 100000
  Elasped Time(ms): 8.122       Writing record 70000 of 100000
  Elasped Time(ms): 7.945       Writing record 80000 of 100000
  Elasped Time(ms): 8.577       Writing record 90000 of 100000
  Elasped Time(ms): 8.165       Writing record 100000 of 100000
  Elasped Time(ms): 8.203       Writing final records 100000 of 100000
End Stock Load @  Thu Sep 01 11:51:19 CST 2016

Start District Data for 10 Dists @ Thu Sep 01 11:51:19 CST 2016 ...
  Elasped Time(ms): 0.192       Writing record 10 of 10
End District Load @  Thu Sep 01 11:51:19 CST 2016

Start Cust-Hist Load for 60000 Cust-Hists @ Thu Sep 01 11:51:19 CST 2016 ...
  Elasped Time(ms): 0.57        Writing record 10000 of 60000
  Elasped Time(ms): 8.683       Writing record 20000 of 60000
  Elasped Time(ms): 12.004      Writing record 30000 of 60000
  Elasped Time(ms): 8.319       Writing record 40000 of 60000
  Elasped Time(ms): 7.866       Writing record 50000 of 60000
  Elasped Time(ms): 8.145       Writing record 60000 of 60000
  Elasped Time(ms): 8.729       Writing record 60000 of 60000
End Cust-Hist Data Load @  Thu Sep 01 11:52:14 CST 2016

whse=1, dist=10, cust=3000
Start Order-Line-New Load for approx 340000 rows @ Thu Sep 01 11:52:14 CST 2016 ...
  Elasped Time(ms): 0.042       Writing record 10000 of 340000
  Elasped Time(ms): 7.003       Writing record 20000 of 340000
  Elasped Time(ms): 7.775       Writing record 40000 of 340000
  Elasped Time(ms): 14.787      Writing record 50000 of 340000
  Elasped Time(ms): 7.842       Writing record 60000 of 340000
  Elasped Time(ms): 7.476       Writing record 70000 of 340000
  Elasped Time(ms): 7.277       Writing record 80000 of 340000
  Elasped Time(ms): 7.853       Writing record 90000 of 340000
  Elasped Time(ms): 7.727       Writing record 100000 of 340000
  Elasped Time(ms): 7.103       Writing record 120000 of 340000
  Elasped Time(ms): 15.013      Writing record 130000 of 340000
  Elasped Time(ms): 7.484       Writing record 140000 of 340000
  Elasped Time(ms): 7.207       Writing record 150000 of 340000
  Elasped Time(ms): 8.206       Writing record 160000 of 340000
  Elasped Time(ms): 8.106       Writing record 170000 of 340000
  Elasped Time(ms): 7.701       Writing record 180000 of 340000
  Elasped Time(ms): 8.113       Writing record 190000 of 340000
  Elasped Time(ms): 8.056       Writing record 200000 of 340000
  Elasped Time(ms): 8.08        Writing record 210000 of 340000
  Elasped Time(ms): 8.236       Writing record 220000 of 340000
  Elasped Time(ms): 7.512       Writing record 230000 of 340000
  Elasped Time(ms): 7.285       Writing record 240000 of 340000
  Elasped Time(ms): 7.892       Writing record 260000 of 340000
  Elasped Time(ms): 15.049      Writing record 270000 of 340000
  Elasped Time(ms): 7.093       Writing record 280000 of 340000
  Elasped Time(ms): 7.246       Writing record 290000 of 340000
  Elasped Time(ms): 7.32        Writing record 300000 of 340000
  Elasped Time(ms): 7.128       Writing record 310000 of 340000
  Elasped Time(ms): 7.125       Writing record 320000 of 340000
  Elasped Time(ms): 7.381       Writing record 330000 of 340000
  Writing final records 339014 of 340000
End Orders Load @  Thu Sep 01 11:56:30 CST 2016

------------- LoadJDBC Statistics --------------------
     Start Time = Thu Sep 01 11:49:05 CST 2016
       End Time = Thu Sep 01 11:56:30 CST 2016
       Run Time = 444 Seconds
    Rows Loaded = 599025 Rows
Rows Per Second = 1349 Rows/Sec

查看表大小：
benchmarksql@benchmarksql:5432 > \d+
                                List of relations
    Schema    |    Name     |   Type   |    Owner     |    Size    | Description 
--------------+-------------+----------+--------------+------------+-------------
 benchmarksql | customer    | table    | benchmarksql | 18 MB      | 
 benchmarksql | district    | table    | benchmarksql | 8192 bytes | 
 benchmarksql | hist_id_seq | sequence | benchmarksql | 8192 bytes | 
 benchmarksql | history     | table    | benchmarksql | 2608 kB    | 
 benchmarksql | item        | table    | benchmarksql | 10 MB      | 
 benchmarksql | new_order   | table    | benchmarksql | 416 kB     | 
 benchmarksql | oorder      | table    | benchmarksql | 2024 kB    | 
 benchmarksql | order_line  | table    | benchmarksql | 30 MB      | 
 benchmarksql | stock       | table    | benchmarksql | 34 MB      | 
 benchmarksql | warehouse   | table    | benchmarksql | 8192 bytes | 
(10 rows)

测试表创建索引：

[postgres@dang-db run]$./runSQL.sh props.pg sqlIndexCreates
alter table benchmarksql.warehouse add constraint pk_warehouse
primary key (w_id);
alter table benchmarksql.district add constraint pk_district
primary key (d_w_id, d_id);
alter table benchmarksql.customer add constraint pk_customer
primary key (c_w_id, c_d_id, c_id);
create index ndx_customer_name
on  benchmarksql.customer (c_w_id, c_d_id, c_last, c_first);
alter table benchmarksql.oorder add constraint pk_oorder
primary key (o_w_id, o_d_id, o_id);
create unique index ndx_oorder_carrier
on  benchmarksql.oorder (o_w_id, o_d_id, o_carrier_id, o_id);
alter table benchmarksql.new_order add constraint pk_new_order
primary key (no_w_id, no_d_id, no_o_id);
alter table benchmarksql.order_line add constraint pk_order_line
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
alter table benchmarksql.stock add constraint pk_stock
primary key (s_w_id, s_i_id);
alter table benchmarksql.item add constraint pk_item
primary key (i_id);
vacuum analyze;

查看索引：
benchmarksql@benchmarksql:5432 > \di
                           List of relations
    Schema    |        Name        | Type  |    Owner     |   Table    
--------------+--------------------+-------+--------------+------------
 benchmarksql | ndx_customer_name  | index | benchmarksql | customer
 benchmarksql | ndx_oorder_carrier | index | benchmarksql | oorder
 benchmarksql | pk_customer        | index | benchmarksql | customer
 benchmarksql | pk_district        | index | benchmarksql | district
 benchmarksql | pk_item            | index | benchmarksql | item
 benchmarksql | pk_new_order       | index | benchmarksql | new_order
 benchmarksql | pk_oorder          | index | benchmarksql | oorder
 benchmarksql | pk_order_line      | index | benchmarksql | order_line
 benchmarksql | pk_stock           | index | benchmarksql | stock
 benchmarksql | pk_warehouse       | index | benchmarksql | warehouse
(10 rows)

开始测试过程：

[postgres@dang-db run]$ ./runBenchmark.sh props.pg
2016-09-01 14:05:40,826  INFO - Term-00, 
2016-09-01 14:05:40,828  INFO - Term-00, +-------------------------------------------------------------+
2016-09-01 14:05:40,828  INFO - Term-00,      BenchmarkSQL v4.1.1
2016-09-01 14:05:40,828  INFO - Term-00, +-------------------------------------------------------------+
2016-09-01 14:05:40,828  INFO - Term-00,  (c) 2003, Raul Barbosa
2016-09-01 14:05:40,828  INFO - Term-00,  (c) 2004-2016, Denis Lussier
2016-09-01 14:05:40,828  INFO - Term-00,  (c) 2016, Jan Wieck
2016-09-01 14:05:40,828  INFO - Term-00, +-------------------------------------------------------------+
2016-09-01 14:05:40,828  INFO - Term-00, 
2016-09-01 14:05:40,828  INFO - Term-00, driver=org.postgresql.Driver
2016-09-01 14:05:40,829  INFO - Term-00, conn=jdbc:postgresql://localhost:5432/benchmarksql
2016-09-01 14:05:40,829  INFO - Term-00, user=benchmarksql
2016-09-01 14:05:40,829  INFO - Term-00, 
2016-09-01 14:05:40,829  INFO - Term-00, warehouses=1
2016-09-01 14:05:40,829  INFO - Term-00, terminals=1
2016-09-01 14:05:40,829  INFO - Term-00, runTxnsPerTerminal=10
2016-09-01 14:05:40,829  INFO - Term-00, limitTxnsPerMin=300
2016-09-01 14:05:40,829  INFO - Term-00, 
2016-09-01 14:05:40,830  INFO - Term-00, newOrderWeight=45
2016-09-01 14:05:40,830  INFO - Term-00, paymentWeight=43
2016-09-01 14:05:40,830  INFO - Term-00, orderStatusWeight=4
2016-09-01 14:05:40,830  INFO - Term-00, deliveryWeight=4
2016-09-01 14:05:40,830  INFO - Term-00, stockLevelWeight=4
2016-09-01 14:05:40,830  INFO - Term-00, 
Term-00, Running Average tpmTOTAL: 281.69    Current tpmTOTAL: 84    Memory Usage: 10MB / 57MB          
2016-09-01 14:05:43,367  INFO - Term-00, 
2016-09-01 14:05:43,367  INFO - Term-00, 
2016-09-01 14:05:43,367  INFO - Term-00, Measured tpmC (NewOrders) = 146.16
2016-09-01 14:05:43,367  INFO - Term-00, Measured tpmTOTAL = 267.96
2016-09-01 14:05:43,367  INFO - Term-00, Session Start     = 2016-09-01 14:05:40
2016-09-01 14:05:43,367  INFO - Term-00, Session End       = 2016-09-01 14:05:43
2016-09-01 14:05:43,367  INFO - Term-00, Transaction Count = 10

清理测试数据，以便重新导入：

[postgres@dang-db run]$./runSQL.sh props.pg sqlTableTruncates
truncate table benchmarksql.warehouse;
truncate table benchmarksql.item;
truncate table benchmarksql.stock;
truncate table benchmarksql.district;
truncate table benchmarksql.customer;
truncate table benchmarksql.history;
truncate table benchmarksql.oorder;
truncate table benchmarksql.order_line;
truncate table benchmarksql.new_order;

单独删除测试表上的索引：

[postgres@dang-db run]$./runSQL.sh props.pg sqlIndexDrops
alter table benchmarksql.warehouse drop constraint pk_warehouse;
alter table benchmarksql.district drop constraint pk_district;
alter table benchmarksql.customer drop constraint pk_customer;
drop index ndx_customer_name;
-- history table has no primary key
-- commit;
alter table benchmarksql.oorder drop constraint pk_oorder;
drop index ndx_oorder_carrier;
alter table benchmarksql.new_order drop constraint pk_new_order;
alter table benchmarksql.order_line drop constraint pk_order_line;
alter table benchmarksql.stock drop constraint pk_stock;
alter table benchmarksql.item drop constraint pk_item;

删除测试表：

[postgres@dang-db run]$./runSQL.sh props.pg sqlTableDrops
drop table benchmarksql.warehouse;
drop table benchmarksql.item;
drop table benchmarksql.stock;
drop table benchmarksql.district;
drop table benchmarksql.customer;
drop table benchmarksql.oorder;
drop table benchmarksql.order_line;
drop table benchmarksql.history;
drop sequence benchmarksql.hist_id_seq;
drop table benchmarksql.new_order;

runSQL.sh props.pg SQLCOMMAND 中 sql对应的文件均在run目录下：

[postgres@dang-db run]$ll | grep sql
-rw-rw-r-- 1 postgres postgres 1219 Feb  1  2016 sqlExtraCreates.ora
-rw-rw-r-- 1 postgres postgres  860 Feb  1  2016 sqlExtraCreates.pg
-rwxr-xr-x 1 postgres postgres  981 Feb  1  2016 sqlIndexCreates
-rwxr-xr-x 1 postgres postgres  610 Feb  1  2016 sqlIndexDrops
-rwxr-xr-x 1 postgres postgres 1564 Feb  1  2016 sqlTableCopies
-rwxr-xr-x 1 postgres postgres 3136 Feb  1  2016 sqlTableCreates
-rwxr-xr-x 1 postgres postgres  350 Feb  1  2016 sqlTableDrops
-rwxr-xr-x 1 postgres postgres  344 Feb  1  2016 sqlTableTruncates

5.0 版本操作

使用ant编译BenchmarkSQL源码.
本机上并没有安装ant，需要去官网上下载：http://ant.apache.org/bindownload.cgi。解压zip包，配置path环境变量即可。

[postgres@dang-db benchmarksql-5.0]$ant
Buildfile: /postgres/tools/benchmarksql-5.0/build.xml

init:

compile:
    [javac] Compiling 11 source files to /postgres/tools/benchmarksql-5.0/build

dist:
    [mkdir] Created dir: /postgres/tools/benchmarksql-5.0/dist
      [jar] Building jar: /postgres/tools/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar

BUILD SUCCESSFUL
Total time: 1 second

构造测试数据，包括建表、loaddata、建索引、建外键、等。
在5.0版本中，连接用户必须制定密码，否则会报错，导致loaddata步骤失败：

Starting BenchmarkSQL LoadData

driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5432/benchmarksql
user=benchmarksql
password (not defined)
Exception in thread "main" java.lang.NullPointerException
        at java.util.Hashtable.put(Hashtable.java:514)
        at java.util.Properties.setProperty(Properties.java:161)
        at LoadData.main(LoadData.java:87)
        
 表中没有数据：
 benchmarksql@benchmarksql:5432 > \d+
                                   List of relations
    Schema    |       Name        |   Type   |    Owner     |    Size    | Description 
--------------+-------------------+----------+--------------+------------+-------------
 benchmarksql | bmsql_config      | table    | benchmarksql | 0 bytes    | 
 benchmarksql | bmsql_customer    | table    | benchmarksql | 24 kB      | 
 benchmarksql | bmsql_district    | table    | benchmarksql | 0 bytes    | 
 benchmarksql | bmsql_hist_id_seq | sequence | benchmarksql | 8192 bytes | 
 benchmarksql | bmsql_history     | table    | benchmarksql | 0 bytes    | 
 benchmarksql | bmsql_item        | table    | benchmarksql | 0 bytes    | 
 benchmarksql | bmsql_new_order   | table    | benchmarksql | 0 bytes    | 
 benchmarksql | bmsql_oorder      | table    | benchmarksql | 0 bytes    | 
 benchmarksql | bmsql_order_line  | table    | benchmarksql | 0 bytes    | 
 benchmarksql | bmsql_stock       | table    | benchmarksql | 0 bytes    | 
 benchmarksql | bmsql_warehouse   | table    | benchmarksql | 0 bytes    | 
(11 rows)

修改用户设置密码：

postgres@benchmarksql:5432 # alter user benchmarksql with password 'benchsqla123';
ALTER ROLE
postgres@benchmarksql:5432 # \c benchmarksql benchmarksql
You are now connected to database "benchmarksql" as user "benchmarksql".

重新构造测试数据之前先删除测试环境：

[postgres@dang-db run]$./runDatabaseDestroy.sh props.pg
# ------------------------------------------------------------
# Loading SQL file ./sql.common/tableDrops.sql
# ------------------------------------------------------------
drop table bmsql_config;
drop table bmsql_new_order;
drop table bmsql_order_line;
drop table bmsql_oorder;
drop table bmsql_history;
drop table bmsql_customer;
drop table bmsql_stock;
drop table bmsql_item;
drop table bmsql_district;
drop table bmsql_warehouse;
drop sequence bmsql_hist_id_seq;

再次初始化测试环境：

[postgres@dang-db run]$./runDatabaseBuild.sh props.pg
# ------------------------------------------------------------
# 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=benchmarksql
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);
ERROR: Cannot create foreign key whose evaluation cannot be enforced to remote nodes
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);
ERROR: Cannot create foreign key whose evaluation cannot be enforced to remote nodes
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);
ERROR: Cannot create foreign key whose evaluation cannot be enforced to remote nodes
alter table bmsql_stock add constraint s_warehouse_fkey
foreign key (s_w_id)
references bmsql_warehouse (w_id);
ERROR: Abort transaction for gxid (1630619),Because could not serialize access due to concurrent update.
alter table bmsql_stock add constraint s_item_fkey
foreign key (s_i_id)
references bmsql_item (i_id);
ERROR: Cannot create foreign key whose evaluation cannot be enforced to remote nodes

# ------------------------------------------------------------
# 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;

由于pgxc架构的限制，外键在分片表上无法创建。

测试环境初始化完成之后，开始运行测试程序：

[postgres@dang-db run]$./runBenchmark.sh props.pg
15:16:42,969 [main] INFO   jTPCC : Term-00, 
15:16:42,972 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
15:16:42,972 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0
15:16:42,972 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
15:16:42,973 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa
15:16:42,973 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier
15:16:42,975 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck
15:16:42,975 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+
15:16:42,975 [main] INFO   jTPCC : Term-00, 
15:16:42,975 [main] INFO   jTPCC : Term-00, db=postgres
15:16:42,975 [main] INFO   jTPCC : Term-00, driver=org.postgresql.Driver
15:16:42,975 [main] INFO   jTPCC : Term-00, conn=jdbc:postgresql://localhost:5432/benchmarksql
15:16:42,975 [main] INFO   jTPCC : Term-00, user=benchmarksql
15:16:42,975 [main] INFO   jTPCC : Term-00, 
15:16:42,976 [main] INFO   jTPCC : Term-00, warehouses=1
15:16:42,976 [main] INFO   jTPCC : Term-00, terminals=1
15:16:42,978 [main] INFO   jTPCC : Term-00, runTxnsPerTerminal=8
15:16:42,978 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=300
15:16:42,978 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true
15:16:42,978 [main] INFO   jTPCC : Term-00, 
15:16:42,978 [main] INFO   jTPCC : Term-00, newOrderWeight=45
15:16:42,978 [main] INFO   jTPCC : Term-00, paymentWeight=43
15:16:42,978 [main] INFO   jTPCC : Term-00, orderStatusWeight=4
15:16:42,979 [main] INFO   jTPCC : Term-00, deliveryWeight=4
15:16:42,979 [main] INFO   jTPCC : Term-00, stockLevelWeight=4
15:16:42,979 [main] INFO   jTPCC : Term-00, 
15:16:42,979 [main] INFO   jTPCC : Term-00, resultDirectory=null
15:16:42,979 [main] INFO   jTPCC : Term-00, osCollectorScript=null
15:16:42,980 [main] INFO   jTPCC : Term-00, 
15:16:44,096 [main] INFO   jTPCC : Term-00, C value for C_LAST during load: 69
15:16:44,096 [main] INFO   jTPCC : Term-00, C value for C_LAST this run:    159
15:16:44,096 [main] INFO   jTPCC : Term-00, 
15:16:45,209 [Thread-0] FATAL  jTPCCTerminal : 2nt tpmTOTAL: 0    Memory Usage: 6MB / 57MB          
java.lang.ArrayIndexOutOfBoundsException: 2
        at org.postgresql.util.ByteConverter.int4(ByteConverter.java:48)
        at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2125)
        at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2589)
        at jTPCCTData.executeNewOrder(jTPCCTData.java:488)
        at jTPCCTData.execute(jTPCCTData.java:95)
        at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:261)
        at jTPCCTerminal.run(jTPCCTerminal.java:88)
        at java.lang.Thread.run(Thread.java:745)

由于代码本身的原因，我这边一执行就报错。
由于没有测试成功的原因，后续的生成报告和生成图形化的操作没法操作，引用how to run中的内容：

Result report
BenchmarkSQL collects detailed performance statistics and (if
configured) OS performance data. The example configuration file
defaults to a directory starting with my_result_.

Use the generateReport.sh DIRECTORY script to create an HTML file
with graphs. This requires R to be installed, which is beyond the
scope of this HOW-TO.

生成报告还需运行环境安装R语言。
查看5.0版本中的 runDatabaseBuild.sh 中的内容，发现是将之前版本runSQL.sh的几个步骤进行了封装：

[postgres@dang-db run]$more runDatabaseBuild.sh
#!/bin/sh

if [ $# -lt 1 ] ; then
    echo "usage: $(basename $0) PROPS [OPT VAL [...]]" >&2
    exit 2
fi

PROPS="$1"
shift
if [ ! -f "${PROPS}" ] ; then
    echo "${PROPS}: no such file or directory" >&2
    exit 1
fi
DB="$(grep '^db=' $PROPS | sed -e 's/^db=//')"

BEFORE_LOAD="tableCreates"
AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"

for step in ${BEFORE_LOAD} ; do
    ./runSQL.sh "${PROPS}" $step
done

./runLoader.sh "${PROPS}" $*

for step in ${AFTER_LOAD} ; do
    ./runSQL.sh "${PROPS}" $step
done

外键无法创建的问题，可以通过修改tableCreate脚本，将默认的hash分片表，修改为复制表即可。
分析runSQL脚本的内容，了解runSQL如何去寻找sql脚本：

# Determine which SQL file to use.
#
# 1) If $2 specifies a file that ends in .sql, we use that.
# 2) If a file ./sql.<dbtype>/$2.sql exists, we use that.
# 3) If none of the above, use ./sql.common/$2.sql.
# ----
if echo "$2" | grep -q -e '\.sql$' ; then
    ENDS_WITH_SQL=1
else
    ENDS_WITH_SQL=0
fi

if [ -f "${2}" -a $ENDS_WITH_SQL -eq 1 ] ; then
    SQL_FILE="$2"
else
    if [ -f "./sql.$(getProp db)/${2}.sql" ] ; then
        SQL_FILE="./sql.$(getProp db)/${2}.sql"
    else
        SQL_FILE="./sql.common/${2}.sql"
        if [ ! -f "${SQL_FILE}" ] ; then
           echo "ERROR: Cannot locate SQL file for ${2}" >&2
           exit 1
        fi
    fi
fi

先判断第二个参数是否以.sql 结尾，如果是，直接使用该文件。
如果不是.sql文件，根据数据库类型去相应的sql.db 目录去查找是否存在执行操作对应的sql文件，如果找到了，则使用找到的文件。
如果没有找到，则取sql.common目录中的文件。
按照现在的逻辑，只需要将sql.common中的tableCreates.sql复制到sql.postgres目录，然后修改为复制表的创建方式即可：

create table bmsql_config (
  cfg_name    varchar(30) primary key,
  cfg_value   varchar(50)
)
distribute by replication;

.....

再次执行 ./runDatabaseBuild.sh propts.pg，查看外键部分log：

# ------------------------------------------------------------
# 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);

创建成功。

转载于:https://my.oschina.net/yafeishi/blog/742314