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

来源：互联网发布：供应链反应矩阵编辑：程序博客网时间：2024/04/28 02:14

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

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

0 0