一、简介
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即可。
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即可。