BenchmarkSQL TPC-C OLTP 資料庫效能測試工具 For Greenplum

2019-03-20

432
0
Greenplum

BenchmarkSQL TPC-C OLTP 資料庫效能測試工具

BenchmarkSQL 是個開源測試資料庫 OLTP 效能工具，採用 TPC-C 的測試標準，

透過 JDBC 的方式連到資料庫，支援 PostgreSQL、EnterpriseDB、Oracle、DB2、SQL Server、MySQL

標案下載位置： https://sourceforge.net/projects/benchmarksql/

完整安裝介紹：https://www.jianshu.com/p/5a056102c8b8

安裝過程就不介紹了，筆者是用來測試 Greemplum 的 OLTP 能力 (os: 想也知道效能很差，但是上面給的研究，不得不做這麼奇怪的測試…唉…)

以下只記錄需要修改注意的地方

1. PostgreSQL JDBC Driver

因安裝包裡面已含有 Postgres JDBC Driver 版本為 postgresql-9.3-1102.jdbc41.jar，不過這裡我是去 PostgreSQL Driver 官網下載比較新的點的 Driver ( 筆者當時下載是 postgresql-42.2.5.jre7.jar )

2. tableCreate.sql

/benchmarksql-5.0/run/sql.common/tableCreates.sql 本身沒有問題，不過內容比較適用於 PostgreSQL、Oracle 等非分割資料使用的資料庫，而 Greenplum 因為在建立 Table 時，若沒有定義資料切割的欄位 ( DISTRIBUTED BY )，預設會拿第一個欄位來分割資料，那模擬時的資料「可能」分佈就會不均，因此就稍微修改成以下SQL內容，而原本 tableCreates.sql 需要修改檔名

create table bmsql_config (
  cfg_name    varchar(30) primary key,
  cfg_value   varchar(50)
)
DISTRIBUTED BY (cfg_name);

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)
)
DISTRIBUTED BY (w_id);

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)
)
DISTRIBUTED BY (d_w_id, d_id);

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)
)
DISTRIBUTED BY (c_w_id, c_d_id, c_id);

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)
)
DISTRIBUTED BY (h_c_w_id, h_c_d_id, h_c_id, h_w_id, h_d_id);

create table bmsql_new_order (
  no_w_id  integer   not null,
  no_d_id  integer   not null,
  no_o_id  integer   not null
)
DISTRIBUTED BY (no_w_id, no_d_id, no_o_id);

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
)
DISTRIBUTED BY (o_w_id, o_d_id, o_id);

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)
)
DISTRIBUTED BY (ol_w_id, ol_d_id, ol_o_id, ol_number);

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
)
DISTRIBUTED BY (i_id);

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)
)
DISTRIBUTED BY (s_w_id, s_i_id);

3. 修改 set myCP

在 runSQL.sh、runLoader.sh、runBenchmark.sh 裡面，把有出現 setCP || exit 1 都註解掉，並在下面加入以下的內容

#setCP || exit 1

runBenchmark.sh

myCP=".:../lib/postgres/postgresql-42.2.5.jre7.jar:../lib/apache-log4j-extras-1.1.jar:../lib/log4j-1.2.17.jar:../dist/BenchmarkSQL-5.0.jar"

runLoader.sh

myCP=".:../lib/postgres/postgresql-42.2.5.jre7.jar:../lib/apache-log4j-extras-1.1.jar:../dist/BenchmarkSQL-5.0.jar"

runSQL.sh

myCP="../lib/postgres/postgresql-42.2.5.jre7.jar:../dist/BenchmarkSQL-5.0.jar"

4. 安裝 R ( redhat 6 )

特別寫這個，如果是 redhat 6 要安裝 R，需要先安裝 epel-release，將 texinfo-tex、libicu-devel 都更新後，再來安裝 R，至於 redhat 7是不是能直接安裝 R，這點就沒研究測試，安裝方法大同小異

rpm -ivh https://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
yum install texinfo-tex
yum install libicu-devel
yum install R

相關參考連接：

TPC 組織官網 http://www.tpc.org/default.asp

Benchmarksql基于PostgreSQL安装与使用 https://www.jianshu.com/p/5a056102c8b8

PostgreSQL JDBC Driver 官網 https://jdbc.postgresql.org

BenchmarkSQL的安裝和使用指導書 https://www.itread01.com/content/1541074627.html

已轉向 blogger 記錄

https://slowlife-notes.blogspot.com

回首頁