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