1 Benchmark SQL 数据库测试工具使用——安装使用

1.Benchmark SQL 数据库测试工具使用——安装使用

欢迎转载,转载请标明出处:http://blog.csdn.net/notbaron/article/details/50302105

基于JDBC所以需要先安装JDK包。

当然测试数据库,数据库本身需要安装完毕准备好,当前支持的数据库有如下2种:

Postgres 和 Oracle

最新的版本对外宣传是已经停止支持DB2数据库了。

如果需要测试应该还是可以的,应为曾经是支持过的,这个需要DBA自己去验证。

1. 下载地址

http://sourceforge.net/projects/benchmarksql/

2. 安装解压

解压命令比较简单

#unzip benchmarksql-4.1.0.zip

3. 配置文件

# cd benchmarksql-4.1.0/

#cd run/

里面两个文件:

props.ora和props.pg

分别对应oracle和pg数据库的。

蛤蟆这里以ORACLE为例

[oracle@OEL run]$ cat props.ora

driver=oracle.jdbc.driver.OracleDriver

conn=jdbc:oracle:thin:@localhost:1521:XE

user=system

password=system

 

warehouses=1

terminals=1

//To run specified transactions per terminal- runMinsmust equal zero

runTxnsPerTerminal=10

//To run for specified minutes- runTxnsPerTerminalmust equal zero

runMins=0

//Number of total transactions per minute

limitTxnsPerMin=300

 

//The following five values must add up to 100

newOrderWeight=45

paymentWeight=43

orderStatusWeight=4

deliveryWeight=4

stockLevelWeight=4

文件中主要3部分:

JDBC连接信息、每分钟执行的事务数量,每分钟执行事务总数等待、TPCC中5个事务比例

主要修改的是JDBC的连接信息,后面两个部分可以先不修改。

4. 创建测试表

使用runSQL.sh脚本

会创建一个benchmarksql的schema。

配置完配置文件后,

修改runSQL.sh如下:

myCP="../lib/ojdbc7.jar"

myCP="$myCP:../dist/BenchmarkSQL-4.1.jar"

 

myOPTS="-Dprop=$1"

myOPTS="$myOPTS -DcommandFile=$2"

 

java -cp .:$myCP $myOPTS ExecJDBC

其中ojdbc7.jar从oracle的客户端中获取。(蛤蟆电脑上的路径如下:

/u01/app/oracle/product/12c/db_1/jdbc/lib/ojdbc7.jar)

执行如下:

#./runSQL.sh  props.ora sqlTableCreates

完成表的创建。

PS:

对于ORACLE 直接创建用户就可以了。

最后如下(12C):

CREATE user benchmarksql identified by benchmarksqldefault tablespace tpcc;

grant resource,connect to benchmarksql;

alter user benchmarksql quota unlimited on tpcc;

 

 

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

 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 default hist_id_seq.nextval primarykey,

  h_c_id   integer,

  h_c_d_idinteger,

  h_c_w_idinteger,

  h_d_id   integer,

  h_w_id   integer,

  h_date   timestamp,

  h_amountdecimal(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_idinteger,

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

  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

);

5. 加载数据

使用脚本./runLoader.sh

./runLoader.sh props.ora  numwarehouses 1

Starting BenchmarkSQL LoadData

----------------- Initialization-------------------

numwarehouses

1

driver=oracle.jdbc.driver.OracleDriver

conn=jdbc:oracle:thin:@127.0.0.1:1521/pdb1

user=system

password=******

 

------------- LoadData StartTime = Mon Dec14 17:38:12 EST 2015-------------

 

Start Whse Load for 1 Whses @ Mon Dec 1417:38:12 EST 2015 ...

Elasped Time(ms): 0.025

End Whse Load @  Mon Dec 14 17:38:12 EST 2015

 

Start Item Load for 100000 Items @ Mon Dec14 17:38:12 EST 2015 ...

 Elasped Time(ms): 0.266      Writing record 10000 of 100000

 Elasped Time(ms): 0.798      Writing record 20000 of 100000

 Elasped Time(ms): 0.269      Writing record 30000 of 100000

 Elasped Time(ms): 0.179      Writing record 40000 of 100000

 Elasped Time(ms): 0.103      Writing record 50000 of 100000

 Elasped Time(ms): 0.084      Writing record 60000 of 100000

 Elasped Time(ms): 0.084      Writing record 70000 of 100000

 Elasped Time(ms): 0.087      Writing record 80000 of 100000

 Elasped Time(ms): 0.084      Writing record 90000 of 100000

 Elasped Time(ms): 0.082      Writing record 100000 of 100000

 Elasped Time(ms): 0.035      Writing final records 100000 of 100000

End Item Load @  Mon Dec 14 17:38:14 EST 2015

 

Start Stock Load for 100000 units @ Mon Dec14 17:38:14 EST 2015 ...

 Elasped Time(ms): 0.357      Writing record 10000 of 100000

 Elasped Time(ms): 0.559      Writing record 20000 of 100000

 Elasped Time(ms): 0.452      Writing record 30000 of 100000

 Elasped Time(ms): 0.53       Writing record 40000 of 100000

 Elasped Time(ms): 0.394      Writing record 50000 of 100000

 Elasped Time(ms): 0.364      Writing record 60000 of 100000

  ElaspedTime(ms): 0.335       Writing record70000 of 100000

 Elasped Time(ms): 0.358      Writing record 80000 of 100000

 Elasped Time(ms): 0.383      Writing record 90000 of 100000

 Elasped Time(ms): 0.358      Writing record 100000 of 100000

 Elasped Time(ms): 0.106      Writing final records 100000 of 100000

End Stock Load @  Mon Dec 14 17:38:18 EST 2015

 

Start District Data for 10 Dists @ Mon Dec14 17:38:18 EST 2015 ...

 Elasped Time(ms): 0.013      Writing record 10 of 10

End District Load @  Mon Dec 14 17:38:18 EST 2015

 

Start Cust-Hist Load for 60000 Cust-Hists @Mon Dec 14 17:38:18 EST 2015 ...

 Elasped Time(ms): 0.476      Writing record 10000 of 60000

 Elasped Time(ms): 1.062      Writing record 20000 of 60000

 Elasped Time(ms): 0.535      Writing record 30000 of 60000

 Elasped Time(ms): 0.751      Writing record 40000 of 60000

 Elasped Time(ms): 0.74       Writing record 50000 of 60000

 Elasped Time(ms): 0.445      Writing record 60000 of 60000

 Elasped Time(ms): 0.097       Writingrecord 60000 of 60000

End Cust-Hist Data Load @  Mon Dec 14 17:38:22 EST 2015

 

whse=1, dist=10, cust=3000

Start Order-Line-New Load for approx 340000rows @ Mon Dec 14 17:38:22 EST 2015 ...

 Elasped Time(ms): 0.16       Writing record 20000 of 340000

 Elasped Time(ms): 0.331      Writing record 30000 of 340000

 Elasped Time(ms): 0.116      Writing record 40000 of 340000

 Elasped Time(ms): 0.151      Writing record 50000 of 340000

 Elasped Time(ms): 0.108      Writing record 60000 of 340000

 Elasped Time(ms): 0.064      Writing record 70000 of 340000

 Elasped Time(ms): 0.059      Writing record 80000 of 340000

 Elasped Time(ms): 0.06       Writing record 90000 of 340000

  ElaspedTime(ms): 0.058       Writing record100000 of 340000

 Elasped Time(ms): 0.065      Writing record 110000 of 340000

 Elasped Time(ms): 0.057      Writing record 120000 of 340000

 Elasped Time(ms): 0.064      Writing record 130000 of 340000

  ElaspedTime(ms): 0.057       Writing record140000 of 340000

 Elasped Time(ms): 0.082      Writing record 150000 of 340000

 Elasped Time(ms): 0.063      Writing record 160000 of 340000

 Elasped Time(ms): 0.059      Writing record 170000 of 340000

  ElaspedTime(ms): 0.062       Writing record180000 of 340000

 Elasped Time(ms): 0.059      Writing record 190000 of 340000

 Elasped Time(ms): 0.079      Writing record 210000 of 340000

 Elasped Time(ms): 0.093      Writing record 220000 of 340000

  ElaspedTime(ms): 0.078       Writing record240000 of 340000

 Elasped Time(ms): 0.103      Writing record 250000 of 340000

 Elasped Time(ms): 0.063      Writing record 260000 of 340000

 Elasped Time(ms): 0.057      Writing record 270000 of 340000

  ElaspedTime(ms): 0.083       Writing record280000 of 340000

 Elasped Time(ms): 0.06       Writing record 290000 of 340000

 Elasped Time(ms): 0.058      Writing record 300000 of 340000

 Elasped Time(ms): 0.063      Writing record 310000 of 340000

  ElaspedTime(ms): 0.06        Writing record320000 of 340000

 Elasped Time(ms): 0.061      Writing record 330000 of 340000

 Writing final records 338831 of 340000

End Orders Load @  Mon Dec 14 17:38:25 EST 2015

 

------------- LoadJDBC Statistics--------------------

    Start Time = Mon Dec 14 17:38:12 EST 2015

      End Time = Mon Dec 14 17:38:25 EST 2015

      Run Time = 13 Seconds

   Rows Loaded = 598842 Rows

Rows Per Second = 46064 Rows/Sec

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

6. 创建索引

还是使用脚本:#./runSQL.sh

[oracle@OEL run]$ ./runSQL.sh props.orasqlIndexCreates

 

收集数据统计信息使用如下:

exec dbms_stats.gather_schema_stats('benchmarksql');

7. 执行测试

使用脚本runBenchmark.sql

#./runBenchmark.sql props.ora

 

 

 

 

 

 

 

 

 

 

 

 

 

 

再分享一下我老师大神的人工智能教程吧。零基础!通俗易懂!风趣幽默!还带黄段子!希望你也加入到我们人工智能的队伍中来!http://www.captainbed.net

上一篇:maskrcnn_benchmark理解记录——由demo\predictor.py引入推断过程


下一篇:【原创】大数据基础之Benchmark(1)HiBench