达梦数据库tpcc测试

目录

1、将BENCHMARKSQL工具拷贝到机器上

2、在数据库中创建BENCHMARKSQL表空间以及BENCHMARKSQL用户（只能是BENCHMARKSQL用户）BENCHMARKSQL用户的默认表空间为BENCHMARKSQL表空间

3、按照数据库测试需要更改配置信息

4、创建测试要用的初始表

5）装载数据

6）开始测试

1、将BENCHMARKSQL工具拷贝到机器上

2、在数据库中创建BENCHMARKSQL表空间以及BENCHMARKSQL用户（只能是BENCHMARKSQL用户）BENCHMARKSQL用户的默认表空间为BENCHMARKSQL表空间

SQL> create tablespace benchmarksql datafile 'bench.dbf' size 2048 ;

executed successfully

used time: 00:04:34.520. Execute id is 401.

SQL> create user benchmarksql identified by 123456789 default tablespace benchmarksql ;

executed successfully

used time: 100.536(ms). Execute id is 402.

SQL> grant dba to benchmarksql ;

executed successfully

3、按照数据库测试需要更改配置信息

[root@node1 run]# more props.dm

db=dm

driver=dm.jdbc.driver.DmDriver

conn=jdbc:dm://192.168.1.11:5236

user=benchmarksql

password=123456789

warehouses=10

loadWorkers=48

4、创建测试要用的初始表

SQL> start /benchmarksql-5.0rc2-westone-v1.2/run/sqlTableCreates

SQL> create table BENCHMARKSQL.bmsql_config (

cfg_name    varchar(30) cluster primary key,

cfg_value   varchar(50)

);

executed successfully

used time: 137.262(ms). Execute id is 400.

SQL> create table BENCHMARKSQL.bmsql_warehouse (

w_id        integer   not null,

w_ytd       decimal(22,2),

w_tax       float,

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),

cluster primary key(w_id)

)STORAGE(FILLFACTOR 1);

executed successfully

used time: 8.074(ms). Execute id is 405.

SQL> create table BENCHMARKSQL.bmsql_district (

d_w_id       integer       not null,

d_id         integer       not null,

d_ytd        decimal(22,2),

d_tax        float,

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),

cluster primary key(d_w_id, d_id)

)STORAGE(FILLFACTOR 1);

executed successfully

used time: 7.801(ms). Execute id is 406.

SQL> create table BENCHMARKSQL.bmsql_customer (

c_w_id         integer        not null,

c_d_id         integer        not null,

c_id           integer        not null,

c_discount     float,

c_credit       char(2),

c_last         varchar(16),

c_first        varchar(16),

c_credit_lim   float,

c_balance      float,

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),

cluster primary key(c_w_id, c_d_id, c_id)

);

executed successfully

used time: 9.061(ms). Execute id is 407.

SQL> create table BENCHMARKSQL.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 float,

h_data   varchar(24)

)storage(branch(32,32),without counter);

executed successfully

used time: 8.284(ms). Execute id is 408.

SQL> create table BENCHMARKSQL.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     float,

o_all_local  float,

o_entry_d    timestamp,

cluster primary key(o_w_id, o_d_id, o_id)

)storage(without counter);

executed successfully

used time: 5.905(ms). Execute id is 409.

SQL> create table BENCHMARKSQL.bmsql_new_order (

no_w_id  integer   not null,

no_d_id  integer   not null,

no_o_id  integer   not null,

cluster primary key(no_w_id, no_d_id, no_o_id)

)storage(without counter);

executed successfully

used time: 11.642(ms). Execute id is 410.

SQL> create table BENCHMARKSQL.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       float,

ol_supply_w_id  integer,

ol_quantity     float,

ol_dist_info    char(24),

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

)storage(without counter);

executed successfully

used time: 11.011(ms). Execute id is 411.

SQL> create table BENCHMARKSQL.bmsql_stock (

s_w_id       integer       not null,

s_i_id       integer       not null,

s_quantity   float,

s_ytd        float,

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),

cluster primary key(s_w_id, s_i_id)

);

executed successfully

used time: 12.031(ms). Execute id is 412.

SQL> create table BENCHMARKSQL.bmsql_item (

i_id     integer      not null,

i_name   varchar(24),

i_price  float,

i_data   varchar(50),

i_im_id  integer,

cluster primary key(i_id)

);

executed successfully

used time: 12.057(ms). Execute id is 413.

5）装载数据

[root@node1 run]# ./runLoader.sh props.dm  numwarehouses 10

Starting BenchmarkSQL LoadData

driver=dm.jdbc.driver.DmDriver

conn=jdbc:dm://192.168.1.11:5236

user=benchmarksql

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

warehouses=10

loadWorkers=48

fileLocation (not defined)

csvNullValue (not defined - using default 'NULL')

Worker 000: Loading ITEM

Worker 001: Loading Warehouse      1

……

6）开始测试

[root@node1 run]# ./runBenchmark.sh props.dm

21:25:44,541 [main] INFO   jTPCC : Term-00,

21:25:45,060 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+

21:25:45,061 [main] INFO   jTPCC : Term-00,      BenchmarkSQL v5.0rc2

21:25:45,061 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+

21:25:45,094 [main] INFO   jTPCC : Term-00,  (c) 2003, Raul Barbosa

21:25:45,096 [main] INFO   jTPCC : Term-00,  (c) 2004-2016, Denis Lussier

21:25:45,535 [main] INFO   jTPCC : Term-00,  (c) 2016, Jan Wieck

21:25:45,544 [main] INFO   jTPCC : Term-00, +-------------------------------------------------------------+

21:25:45,545 [main] INFO   jTPCC : Term-00,

21:25:45,554 [main] INFO   jTPCC : Term-00, db=dm

21:25:45,591 [main] INFO   jTPCC : Term-00, driver=dm.jdbc.driver.DmDriver

21:25:45,591 [main] INFO   jTPCC : Term-00, conn=jdbc:dm://192.168.1.11:5236

21:25:45,592 [main] INFO   jTPCC : Term-00, user=benchmarksql

21:25:45,600 [main] INFO   jTPCC : Term-00,

21:25:45,604 [main] INFO   jTPCC : Term-00, warehouses=10

21:25:45,604 [main] INFO   jTPCC : Term-00, terminals=10

21:25:45,780 [main] INFO   jTPCC : Term-00, runMins=5

21:25:45,781 [main] INFO   jTPCC : Term-00, limitTxnsPerMin=0

21:25:45,781 [main] INFO   jTPCC : Term-00, terminalWarehouseFixed=true

21:25:45,793 [main] INFO   jTPCC : Term-00,

21:25:45,

达梦社区地址：https://eco.dameng.com