BenchmarkSQL安装使用介绍

一、简介
An easy to use JDBC benchmark that closely resembles the TPC-C standard for OLTP. RDBMS's presently supported include PostgreSQL, EnterpriseDB and Oracle.
二、安装配置
下载地址：http://sourceforge.net/projects/benchmarksql/?source=navbar
Required：JDK7
PostgreSQL Version：9.3.4


解压：
[postgres@localhost ~]$ unzip benchmarksql-4.1.0.zip


保留配置备份：
[postgres@localhost ~]$ cd benchmarksql-4.1.0/run/
[postgres@localhost run]$ cp props.pg props.pg.bak


修改数据库连接配置：
[postgres@localhost run]$ vi props.pg
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5432/benchmarksql
user=benchmarksql
password=password


warehouses=1
terminals=1
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=10
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=0
//Number of total transactions per minute
limitTxnsPerMin=300




//The following five values must add up to 100
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4




创建测试库：
postgres=# create user benchmarksql with superuser password 'password';
CREATE ROLE
postgres=# create database benchmarksql owner benchmarksql;
CREATE DATABASE


创建测试表结构：
[postgres@localhost run]$ ./runSQL.sh props.pg sqlTableCreates


查看测试表：
[postgres@localhost run]$ psql benchmarksql benchmarksql
psql (9.3.4)
Type "help" for help.


benchmarksql=# \d
                  List of relations
    Schema    |    Name     |   Type   |    Owner     
--------------+-------------+----------+--------------
 benchmarksql | customer    | table    | benchmarksql
 benchmarksql | district    | table    | benchmarksql
 benchmarksql | hist_id_seq | sequence | benchmarksql
 benchmarksql | history     | table    | benchmarksql
 benchmarksql | item        | table    | benchmarksql
 benchmarksql | new_order   | table    | benchmarksql
 benchmarksql | oorder      | table    | benchmarksql
 benchmarksql | order_line  | table    | benchmarksql
 benchmarksql | stock       | table    | benchmarksql
 benchmarksql | warehouse   | table    | benchmarksql
(10 rows)




导入测试数据：
[postgres@localhost run]$ ./runLoader.sh props.pg numWarehouses 1


查看表：
benchmarksql=# \d+
                                List of relations
    Schema    |    Name     |   Type   |    Owner     |    Size    | Description 
--------------+-------------+----------+--------------+------------+-------------
 benchmarksql | customer    | table    | benchmarksql | 18 MB      | 
 benchmarksql | district    | table    | benchmarksql | 8192 bytes | 
 benchmarksql | hist_id_seq | sequence | benchmarksql | 8192 bytes | 
 benchmarksql | history     | table    | benchmarksql | 2552 kB    | 
 benchmarksql | item        | table    | benchmarksql | 10192 kB   | 
 benchmarksql | new_order   | table    | benchmarksql | 416 kB     | 
 benchmarksql | oorder      | table    | benchmarksql | 2024 kB    | 
 benchmarksql | order_line  | table    | benchmarksql | 30 MB      | 
 benchmarksql | stock       | table    | benchmarksql | 34 MB      | 
 benchmarksql | warehouse   | table    | benchmarksql | 8192 bytes | 
(10 rows)


benchmarksql=# select count(*) from customer ;
 count 
-------
 30000
(1 row)


benchmarksql=# select * from warehouse;
 w_id |   w_ytd   | w_tax  | w_name |     w_street_1      |  w_street_2  |    w_city    | w_state |   w_zip   
------+-----------+--------+--------+---------------------+--------------+--------------+---------+-----------
    1 | 300000.00 | 0.0445 | RfHUW  | HldcTUzsMXqRYwvseXe | TgpQzWZsRMCc | WgCgqczSIpzF | DT      | 123456789
(1 row)






为测试表创建索引：
[postgres@localhost run]$ ./runSQL.sh props.pg sqlIndexCreates


查看索引：
benchmarksql=# \di
                           List of relations
    Schema    |        Name        | Type  |    Owner     |   Table    
--------------+--------------------+-------+--------------+------------
 benchmarksql | history_pkey       | index | benchmarksql | history
 benchmarksql | ndx_customer_name  | index | benchmarksql | customer
 benchmarksql | ndx_oorder_carrier | index | benchmarksql | oorder
 benchmarksql | pk_customer        | index | benchmarksql | customer
 benchmarksql | pk_district        | index | benchmarksql | district
 benchmarksql | pk_item            | index | benchmarksql | item
 benchmarksql | pk_new_order       | index | benchmarksql | new_order
 benchmarksql | pk_oorder          | index | benchmarksql | oorder
 benchmarksql | pk_order_line      | index | benchmarksql | order_line
 benchmarksql | pk_stock           | index | benchmarksql | stock
 benchmarksql | pk_warehouse       | index | benchmarksql | warehouse
(11 rows)


至此配置完成。




若需要重新导入数据，则无需删除表结构，只需在导入前执行：
./runSQL.sh props.pg sqlTableTruncates


三、测试
[postgres@localhost run]$ ./runBenchmark.sh props.pg
2014-05-13 06:58:56,125  INFO - Term-00, 
2014-05-13 06:58:56,129  INFO - Term-00, +-------------------------------------------------------------+
2014-05-13 06:58:56,129  INFO - Term-00,      BenchmarkSQL v4.1
2014-05-13 06:58:56,129  INFO - Term-00, +-------------------------------------------------------------+
2014-05-13 06:58:56,129  INFO - Term-00,  (c) 2003, Raul Barbosa
2014-05-13 06:58:56,130  INFO - Term-00,  (c) 2004-2014, Denis Lussier
2014-05-13 06:58:56,132  INFO - Term-00, +-------------------------------------------------------------+
2014-05-13 06:58:56,133  INFO - Term-00, 
2014-05-13 06:58:56,134  INFO - Term-00, driver=org.postgresql.Driver
2014-05-13 06:58:56,135  INFO - Term-00, conn=jdbc:postgresql://localhost:5432/benchmarksql
2014-05-13 06:58:56,136  INFO - Term-00, user=benchmarksql
2014-05-13 06:58:56,137  INFO - Term-00, 
2014-05-13 06:58:56,138  INFO - Term-00, warehouses=1
2014-05-13 06:58:56,138  INFO - Term-00, terminals=1
2014-05-13 06:58:56,140  INFO - Term-00, runTxnsPerTerminal=10
2014-05-13 06:58:56,141  INFO - Term-00, limitTxnsPerMin=300
2014-05-13 06:58:56,141  INFO - Term-00, 
2014-05-13 06:58:56,142  INFO - Term-00, newOrderWeight=45
2014-05-13 06:58:56,143  INFO - Term-00, paymentWeight=43
2014-05-13 06:58:56,144  INFO - Term-00, orderStatusWeight=4
2014-05-13 06:58:56,145  INFO - Term-00, deliveryWeight=4
2014-05-13 06:58:56,146  INFO - Term-00, stockLevelWeight=4
2014-05-13 06:58:56,146  INFO - Term-00, 
2014-05-13 06:58:58,480  INFO - Term-00,     Current tpmTOTAL: 12    Memory Usage: 6MB / 15MB
2014-05-13 06:58:58,481  INFO - Term-00, 
2014-05-13 06:58:58,481  INFO - Term-00, Measured tpmC (NewOrders) = 86.16
2014-05-13 06:58:58,482  INFO - Term-00, Measured tpmTOTAL = 315.94
2014-05-13 06:58:58,485  INFO - Term-00, Session Start     = 2014-05-13 06:58:56
2014-05-13 06:58:58,485  INFO - Term-00, Session End       = 2014-05-13 06:58:58
2014-05-13 06:58:58,487  INFO - Term-00, Transaction Count = 10


改变测试方案只需在执行测试前修改props.pg即可。