內嵌TPCC測試腳本,支持PG、oracle、mysql等數據庫的壓力測試,使用前須要安裝jdk,原來支持linux和windows上測試,自2.3.5版本以來再也不支持windows上的壓力測試,因此若想在Windows上測試,須要用2.3.5版本。mysql
每一個倉庫負責十個區域的供貨,每一個區域 3000 個客戶服務,每一個倉庫維護 100000 種商品的庫存紀錄,每一個倉庫至多有10個終端linux
BenchmarkSQL數據量:
每一個倉庫數據量大約爲76823.04KB即約爲76M
因此 Warehouse=14時,數據量大約爲1G
Warehouse=140時,數據量大約爲10Gweb
想要加載500G左右的數據,warehouse大約爲5000便可,除了表數據還有索引
記一次加載時間:
128G內存、機械硬盤、7000warehouse
19:20開始加載數據,大約在第三天17:00左右加載完數據(近46h),而後開始建索引,直到第四天sql
測試模擬5中事務處理,新訂單、支付操做、訂單狀態查詢、發貨、庫存狀態查詢;TPC-C基準測試針對一種模擬訂單錄入與銷售環境測量每分鐘商業事務(tpmC)吞吐量。數據庫
(1)修改prop.pgwindows
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5866/benchdb
user=bench
password=bench
warehouses=10
terminals=1
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=5
//Number of total transactions per minute
limitTxnsPerMin=300
(2)建立測試表oracle
[highgo@localhost run]$ ./runSQL.sh props.pg sqlTableCreates ------步驟一
DROP SCHEMA IF EXISTS benchmarksql CASCADE;
CREATE SCHEMA benchmarksql;
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_cnt integer, 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 hist_id_seq;
create table benchmarksql.history ( hist_id integer not null default nextval('hist_id_seq') primary key, h_c_id integer, h_c_d_id integer, h_c_w_id integer, h_d_id integer, h_w_id integer, h_date timestamp, h_amount decimal(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_id integer, 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_cnt integer, 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 );
(3)導入數據svg
①直接導入post
[highgo@localhost run]$ ./runLoader.sh props.pg numWarehouses 10 ------步驟二
Starting BenchmarkSQL LoadData
----------------- Initialization -------------------
numWarehouses
10
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5866/benchdb
user=bench
password=******
------------- LoadData StartTime = Wed Aug 10 18:58:49 PDT 2016-------------
Start Whse Load for 10 Whses @ Wed Aug 10 18:58:49 PDT 2016 ...
Elasped Time(ms): 0.029
End Whse Load @ Wed Aug 10 18:58:49 PDT 2016
Start Item Load for 100000 Items @ Wed Aug 10 18:58:49 PDT 2016 ...
Elasped Time(ms): 0.379 Writing record 10000 of 100000
Elasped Time(ms): 0.856 Writing record 20000 of 100000
Elasped Time(ms): 0.729 Writing record 30000 of 100000
Elasped Time(ms): 0.52 Writing record 40000 of 100000
Elasped Time(ms): 0.576 Writing record 50000 of 100000
Elasped Time(ms): 0.281 Writing record 60000 of 100000
Elasped Time(ms): 0.36 Writing record 70000 of 100000
Elasped Time(ms): 0.269 Writing record 80000 of 100000
Elasped Time(ms): 0.251 Writing record 90000 of 100000
……
……
②能夠將數據導出到csv文件中,之後可從這些文件將數據更快的屢次導入數據庫測試
[highgo@localhost run]$ ./runLoader.sh props.pg numWarehouses 10 fileLocation /home/highgo/Desktop/BMcsv/ (注意,目錄最後BMcsv後/必定存在,否則會在Desktop目錄下以BMcsv爲文件名生成文件)
Starting BenchmarkSQL LoadData
----------------- Initialization -------------------
numWarehouses
10
/home/highgo/Desktop/BMcsv
driver=org.postgresql.Driver
conn=jdbc:postgresql://localhost:5866/benchdb
user=bench
password=******
------------- LoadData StartTime = Wed Aug 10 19:16:22 PDT 2016-------------
Start Whse Load for 10 Whses @ Wed Aug 10 19:16:22 PDT 2016 ...
Elasped Time(ms): 0.179
End Whse Load @ Wed Aug 10 19:16:22 PDT 2016
Start Item Load for 100000 Items @ Wed Aug 10 19:16:22 PDT 2016 ...
Elasped Time(ms): 0.2 Writing record 10000 of 100000
……
……
10倉庫的數據量,導出到文件大小
[highgo@localhost BMcsv]$ ll -h
total 696M
-rw-rw-r–. 1 highgo highgo 19M Aug 10 22:55 cust-hist.csv
-rw-rw-r–. 1 highgo highgo 162M Aug 10 22:55 customer.csv
-rw-rw-r–. 1 highgo highgo 8.8K Aug 10 22:54 district.csv
-rw-rw-r–. 1 highgo highgo 7.0M Aug 10 22:54 item.csv
-rw-rw-r–. 1 highgo highgo 809K Aug 10 22:55 new-order.csv
-rw-rw-r–. 1 highgo highgo 13M Aug 10 22:55 order.csv
-rw-rw-r–. 1 highgo highgo 212M Aug 10 22:55 order-line.csv
-rw-rw-r–. 1 highgo highgo 283M Aug 10 22:54 stock.csv
-rw-rw-r–. 1 highgo highgo 829 Aug 10 22:54 warehouse.csv
將數據加載到數據庫,首先修改sqlTableCopies中文件位置爲導出目錄,如:
copy benchmarksql.warehouse
(w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city, w_state, w_zip)
from '/home/highgo/Desktop/BMcsv/warehouse.csv' WITH CSV;
[highgo@localhost run]$ ./runSQL.sh props.pg sqlTableCopies
copy benchmarksql.warehouse
(w_id, w_ytd, w_tax, w_name, w_street_1, w_street_2, w_city, w_state, w_zip)
from '/home/highgo/Desktop/BMcsv/warehouse.csv' WITH CSV;
copy benchmarksql.item
(i_id, i_name, i_price, i_data, i_im_id)
from '/home/highgo/Desktop/BMcsv/item.csv' WITH CSV;
copy benchmarksql.stock
……
……
(4)建立索引
[highgo@localhost run]$ ./runSQL.sh props.pg sqlIndexCreates ------步驟三
alter table benchmarksql.warehouse add constraint pk_warehouse primary key (w_id);
alter table benchmarksql.district add constraint pk_district primary key (d_w_id, d_id);
alter table benchmarksql.customer add constraint pk_customer primary key (c_w_id, c_d_id, c_id);
……
……
(5)執行壓測(倉庫數爲10,數據量大約爲696M)
[highgo@localhost run]$ ./runBenchmark.sh props.pg ------步驟四
2016-08-10 23:16:43,365 INFO - Term-00,
2016-08-10 23:16:43,383 INFO - Term-00, +-------------------------------------------------------------+
2016-08-10 23:16:43,383 INFO - Term-00, BenchmarkSQL v4.1
2016-08-10 23:16:43,383 INFO - Term-00, +-------------------------------------------------------------+
2016-08-10 23:16:43,383 INFO - Term-00, (c) 2003, Raul Barbosa
2016-08-10 23:16:43,383 INFO - Term-00, (c) 2004-2014, Denis Lussier
2016-08-10 23:16:43,384 INFO - Term-00, +-------------------------------------------------------------+
2016-08-10 23:16:43,384 INFO - Term-00,
2016-08-10 23:16:43,384 INFO - Term-00, driver=org.postgresql.Driver
2016-08-10 23:16:43,384 INFO - Term-00, conn=jdbc:postgresql://localhost:5866/benchdb
2016-08-10 23:16:43,384 INFO - Term-00, user=bench
2016-08-10 23:16:43,385 INFO - Term-00,
2016-08-10 23:16:43,385 INFO - Term-00, warehouses=10
2016-08-10 23:16:43,385 INFO - Term-00, terminals=1
2016-08-10 23:16:43,385 INFO - Term-00, runMins=5
2016-08-10 23:16:43,386 INFO - Term-00, limitTxnsPerMin=300
2016-08-10 23:16:43,386 INFO - Term-00,
2016-08-10 23:16:43,386 INFO - Term-00, newOrderWeight=45
2016-08-10 23:16:43,386 INFO - Term-00, paymentWeight=43
2016-08-10 23:16:43,387 INFO - Term-00, orderStatusWeight=4
2016-08-10 23:16:43,387 INFO - Term-00, deliveryWeight=4
2016-08-10 23:16:43,387 INFO - Term-00, stockLevelWeight=4
2016-08-10 23:16:43,387 INFO - Term-00,
Term-00, Running Average tpmTOTAL: 339.46 Current tpmTOTAL: 12 Memory Usag
……
……
Term-00, Running Average tpmTOTAL: 281.66 Current tpmTOTAL: 7740 Memory UsTerm-00, Running Average tpmTOTAL: 281.43 Current tpmTOTAL: 7740 Memory UsTerm-00, Running Average tpmTOTAL: 281.67 Current tpmTOTAL: 7740 Memory UsTerm-00, Running Average tpmTOTAL: 281.67 Current tpmTOTAL: 7740 Memory UsTerm-00, Running Average tpmTOTAL: 281.71 Current tpmTOTAL: 7740 Memory Usage2016-08-10 23:21:44,425 INFO - Term-00, Current tpmTOTAL: 9672 Memory Usage: 3MB / 15MB
2016-08-10 23:21:44,426 INFO - Term-00,
2016-08-10 23:21:44,426 INFO - Term-00, Measured tpmC (NewOrders) = 124.11
2016-08-10 23:21:44,426 INFO - Term-00, Measured tpmTOTAL = 284.0
2016-08-10 23:21:44,426 INFO - Term-00, Session Start = 2016-08-10 23:16:44 2016-08-10 23:21:44,426 INFO - Term-00, Session End = 2016-08-10 23:21:44 2016-08-10 23:21:44,426 INFO - Term-00, Transaction Count = 1420