溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
原文鏈接： https://www.modb.pro/db/22513?03=
TPC是一系列事務處理和數據庫基準測試的規范。其中TPC-C是針對OLTP的基準測試模型，一方面可以衡量數據庫的性能，另一方面可以衡量硬件性價比，也是廣泛應用并關注的一種測試模型。TPC-C通過TPM去衡量性價比，即每分鐘的執行事務量。
本文介紹TPC-C之BenchmarkSQL對GaussDB T單機數據庫進行性能壓測。
對于分布式集群下的BenchmarkSQL測試，GaussDB T需要做適配，因為分布式下創建表必須要指定分片鍵的，默認的腳本是不指定的。
BenchmarkSQL工具要求JDK7以上。檢查系統JDK版本：
# java -version openjdk version "1.8.0_181" OpenJDK Runtime Environment (build 1.8.0_181-b13) OpenJDK 64-Bit Server VM (build 25.181-b13, mixed mode) #
# yum -y install ant
這里使用benchmarksql-5.0.zip工具包。
# mkdir -p /software/benchmark/ # cd /software/benchmark/ # ls -l 總用量 2212 -rw-r--r-- 1 root root 2263539 3月 10 13:48 benchmarksql-5.0.zip# # unzip benchmarksql-5.0.zip Archive: benchmarksql-5.0.zip ……. ……. # ls -l 總用量 2212 drwxr-xr-x 6 root root 124 5月 26 2016 benchmarksql-5.0 -rw-r--r-- 1 root root 2263539 3月 10 13:48 benchmarksql-5.0.zip# # cd benchmarksql-5.0/ # ls -l 總用量 24 -rwxr-xr-x 1 root root 1130 5月 26 2016 build.xml drwxr-xr-x 3 root root 17 5月 26 2016 doc -rwxr-xr-x 1 root root 6376 5月 26 2016 HOW-TO-RUN.txt drwxr-xr-x 5 root root 129 5月 26 2016 lib -rwxr-xr-x 1 root root 5318 5月 26 2016 README.md drwxr-xr-x 7 root root 4096 5月 26 2016 run drwxr-xr-x 6 root root 67 5月 26 2016 src #
使用ANT編譯BenchMark的包，需要用到BenchMark的build包，進入到BenchMark的目錄，執行ant進行編譯。
# cd /software/benchmark/benchmarksql-5.0/ # ant Buildfile: /software/benchmark/benchmarksql-5.0/build.xml init: [mkdir] Created dir: /software/benchmark/benchmarksql-5.0/build compile: [javac] Compiling 11 source files to /software/benchmark/benchmarksql-5.0/build dist: [mkdir] Created dir: /software/benchmark/benchmarksql-5.0/dist [jar] Building jar: /software/benchmark/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar BUILD SUCCESSFUL Total time: 4 seconds # ls -l 總用量 28 drwxr-xr-x 2 root root 4096 3月 10 13:52 build -rwxr-xr-x 1 root root 1130 5月 26 2016 build.xml drwxr-xr-x 2 root root 34 3月 10 13:52 dist drwxr-xr-x 3 root root 17 5月 26 2016 doc -rwxr-xr-x 1 root root 6376 5月 26 2016 HOW-TO-RUN.txt drwxr-xr-x 5 root root 129 5月 26 2016 lib -rwxr-xr-x 1 root root 5318 5月 26 2016 README.md drwxr-xr-x 7 root root 4096 5月 26 2016 run drwxr-xr-x 6 root root 67 5月 26 2016 src #
拷貝GaussDB T的JDBC驅動到BenchmarkSQL的lib目錄，jdbc驅動在安裝包GaussDB_T_1.0.2-CLIENT-JDBC.tar.gz里。
# cd /software/benchmark/benchmarksql-5.0/lib/ # ls -l 總用量 1256 -rwxr-xr-x 1 root root 346729 5月 26 2016 apache-log4j-extras-1.1.jar -r-x------ 1 root root 443090 3月 10 14:34 com.huawei.gauss.jdbc.ZenithDriver-GaussDB_T_1.0.2.B307.jardrwxr-xr-x 2 root root 60 5月 26 2016 firebird -rwxr-xr-x 1 root root 489883 5月 26 2016 log4j-1.2.17.jar drwxr-xr-x 2 root root 42 5月 26 2016 oracle drwxr-xr-x 2 root root 44 5月 26 2016 postgres #
Zsql連接數據庫，創建測試用戶并授權：
$ zsql omm/yhadmin_123@192.168.179.123:1888 -q connected. SQL> create user aps2 identified by aps2_123; Succeed. SQL> grant connect,resource to aps2; Succeed. SQL> exit $
在benchmarksql的run目錄下，編輯測試的配置文件props.gaussdb。
# pwd /software/benchmark/benchmarksql-5.0/run # cp props.ora props.gaussdb # vi props.gaussdb db=oracle driver=com.huawei.gauss.jdbc.ZenithDriver conn=jdbc:zenith:@192.168.179.123:1888 user=aps2 password=aps2_123 warehouses=10 loadWorkers=4 terminals=10 //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 //Set to true to run in 4.x compatible mode. Set to false to use the //entire configured database evenly. terminalWarehouseFixed=true//The following five values must add up to 100newOrderWeight=45paymentWeight=43orderStatusWeight=4deliveryWeight=4stockLevelWeight=4// Directory name to create for collecting detailed result data. // Comment this out to suppress. resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS osCollectorScript=./misc/os_collector_linux.py osCollectorInterval=1//osCollectorSSHAddr=user@dbhost osCollectorDevices=net_eth0 blk_sda #
參數說明：
配置文件中，db直接填寫oracle即可，驅動driver為com.huawei.gauss.jdbc.ZenithDriver，連接串conn為jdbc:zenith:@IP:端口，這里配置連接數據庫的IP地址和端口，user為連接數據庫的用戶名aps2，password為用戶密碼aps2_123。
warehouse數據倉庫，表示markbench生成的數據表中數據量的大小。BenchmarkSQL數據庫每個warehouse大小大概是100MB，如果該參數設置為10，那整個數據庫的大小大概在1000MB。建議將數據庫的大小設置為服務器物理內存的2-5倍，如果服務器內存為16GB，那么warehouse設置建議在328～819之間。
terminals 是指client的并發連接數，建議設置為服務器CPU總線程數的2-6倍。如果服務器為雙核16線程（單核8線程），那么建議配置在32～96之間。
runTxnsPerTerminal是每分鐘的執行事務數，runtime限制了執行的時間。配置這兩個參數的時候，如果runTxnsPerTerminal的不為0的話，那么runtime的值必須為0。反之，如果配置runtime的話，如果runtime不為0，則runTxnsPerTerminal一定要等于0。
limitTxnsPerMin=300 是限制每分鐘總共執行的事務數，0是無限制。
進入benchmarksql的run目錄下，運行./runDatabaseBuild.sh props.gaussdb腳本，初始化測試數據。
# pwd /software/benchmark/benchmarksql-5.0/run # ./runDatabaseBuild.sh props.gaussdb # ------------------------------------------------------------# Loading SQL file ./sql.common/tableCreates.sql # ------------------------------------------------------------create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) );create table bmsql_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 bmsql_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 bmsql_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 decimal(12,2), 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 bmsql_hist_id_seq;create table bmsql_history ( hist_id integer, 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 bmsql_new_order ( no_w_id integer not null, no_d_id integer not null, no_o_id integer not null);create table bmsql_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 integer, o_all_local integer, o_entry_d timestamp);create table bmsql_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 integer, ol_dist_info char(24) );create table bmsql_item ( i_id integer not null, i_name varchar(24), i_price decimal(5,2), i_data varchar(50), i_im_id integer);create table bmsql_stock ( s_w_id integer not null, s_i_id integer not null, s_quantity integer, s_ytd integer, 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) ); Starting BenchmarkSQL LoadData driver=com.huawei.gauss.jdbc.ZenithDriver conn=jdbc:zenith:@127.0.0.1:1888 user=aps2 password=*********** warehouses=10 loadWorkers=4 fileLocation (not defined) csvNullValue (not defined - using default 'NULL') Worker 000: Loading ITEM Worker 001: Loading Warehouse 1 Worker 002: Loading Warehouse 2 Worker 003: Loading Warehouse 3 Worker 000: Loading ITEM done Worker 000: Loading Warehouse 4 Worker 002: Loading Warehouse 2 done Worker 002: Loading Warehouse 5 Worker 001: Loading Warehouse 1 done Worker 001: Loading Warehouse 6 Worker 003: Loading Warehouse 3 done Worker 003: Loading Warehouse 7 Worker 000: Loading Warehouse 4 done Worker 000: Loading Warehouse 8 Worker 001: Loading Warehouse 6 done Worker 001: Loading Warehouse 9 Worker 002: Loading Warehouse 5 done Worker 002: Loading Warehouse 10 Worker 003: Loading Warehouse 7 done Worker 000: Loading Warehouse 8 done Worker 001: Loading Warehouse 9 done Worker 002: Loading Warehouse 10 done # ------------------------------------------------------------# Loading SQL file ./sql.common/indexCreates.sql # ------------------------------------------------------------alter table bmsql_warehouse add constraint bmsql_warehouse_pkey primary key (w_id);alter table bmsql_district add constraint bmsql_district_pkey primary key (d_w_id, d_id);alter table bmsql_customer add constraint bmsql_customer_pkey primary key (c_w_id, c_d_id, c_id);create index bmsql_customer_idx1on bmsql_customer (c_w_id, c_d_id, c_last, c_first);alter table bmsql_oorder add constraint bmsql_oorder_pkey primary key (o_w_id, o_d_id, o_id);create unique index bmsql_oorder_idx1on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);alter table bmsql_new_order add constraint bmsql_new_order_pkey primary key (no_w_id, no_d_id, no_o_id);alter table bmsql_order_line add constraint bmsql_order_line_pkey primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);alter table bmsql_stock add constraint bmsql_stock_pkey primary key (s_w_id, s_i_id);alter table bmsql_item add constraint bmsql_item_pkey primary key (i_id); # ------------------------------------------------------------# Loading SQL file ./sql.common/foreignKeys.sql # ------------------------------------------------------------alter table bmsql_district add constraint d_warehouse_fkey foreign key (d_w_id)references bmsql_warehouse (w_id);alter table bmsql_customer add constraint c_district_fkey foreign key (c_w_id, c_d_id)references bmsql_district (d_w_id, d_id);alter table bmsql_history add constraint h_customer_fkey foreign key (h_c_w_id, h_c_d_id, h_c_id)references bmsql_customer (c_w_id, c_d_id, c_id);alter table bmsql_history add constraint h_district_fkey foreign key (h_w_id, h_d_id)references bmsql_district (d_w_id, d_id);alter table bmsql_new_order add constraint no_order_fkey foreign key (no_w_id, no_d_id, no_o_id)references bmsql_oorder (o_w_id, o_d_id, o_id);alter table bmsql_oorder add constraint o_customer_fkey foreign key (o_w_id, o_d_id, o_c_id)references bmsql_customer (c_w_id, c_d_id, c_id);alter table bmsql_order_line add constraint ol_order_fkey foreign key (ol_w_id, ol_d_id, ol_o_id)references bmsql_oorder (o_w_id, o_d_id, o_id);alter table bmsql_order_line add constraint ol_stock_fkey foreign key (ol_supply_w_id, ol_i_id)references bmsql_stock (s_w_id, s_i_id);alter table bmsql_stock add constraint s_warehouse_fkey foreign key (s_w_id)references bmsql_warehouse (w_id);alter table bmsql_stock add constraint s_item_fkey foreign key (s_i_id)references bmsql_item (i_id); # ------------------------------------------------------------# Loading SQL file ./sql.oracle/extraHistID.sql # -------------------------------------------------------------- ------ Extra Schema objects/definitions for history.hist_id in Oracle-- ------ ------ This is an extra column not present in the TPC-C-- specs. It is useful for replication systems like-- Bucardo and Slony-I, which like to have a primary-- key on a table. It is an auto-increment or serial-- column type. The definition below is compatible-- with Oracle 11g, using the sequence in a trigger.-- ------ Adjust the sequence above the current max(hist_id)alter sequence bmsql_hist_id_seq increment by 30000;declaren integer; i integer; dummy integer;beginselect max(hist_id) into n from bmsql_history; i := 0; while i <= n loopselect bmsql_hist_id_seq.nextval into dummy from dual; i := i + 30000;end loop;end; ;alter sequence bmsql_hist_id_seq increment by 1;-- Create a trigger that forces hist_id to be hist_id_seq.nextvalcreate trigger bmsql_history_before_insertbefore insert on bmsql_historyfor each rowbeginif :new.hist_id is null thenselect bmsql_hist_id_seq.nextval into :new.hist_id from dual;end if;end; ;-- Add a primary key history(hist_id)alter table bmsql_history add primary key (hist_id); # ------------------------------------------------------------# Loading SQL file ./sql.common/buildFinish.sql # -------------------------------------------------------------- ------ Extra commands to run after the tables are created, loaded,-- indexes built and extra's created.-- ----#
進入benchmarksql的run目錄下，運行./runBenchmark.sh props.gaussdb腳本，開始性能測試。
# pwd /software/benchmark/benchmarksql-5.0/run # ./runBenchmark.sh props.gaussdb 15:55:35,431 [main] INFO jTPCC : Term-00, 15:55:35,443 [main] INFO jTPCC : Term-00, +-------------------------------------------------+15:55:35,443 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0 15:55:35,444 [main] INFO jTPCC : Term-00, +-------------------------------------------------+15:55:35,444 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa 15:55:35,444 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier 15:55:35,448 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck 15:55:35,448 [main] INFO jTPCC : Term-00, +-------------------------------------------------+15:55:35,448 [main] INFO jTPCC : Term-00, 15:55:35,448 [main] INFO jTPCC : Term-00, db=oracle 15:55:35,449 [main] INFO jTPCC : Term-00, driver=com.huawei.gauss.jdbc.ZenithDriver 15:55:35,449 [main] INFO jTPCC : Term-00, conn=jdbc:zenith:@127.0.0.1:1888 15:55:35,449 [main] INFO jTPCC : Term-00, user=aps2 15:55:35,449 [main] INFO jTPCC : Term-00, 15:55:35,449 [main] INFO jTPCC : Term-00, warehouses=10 15:55:35,450 [main] INFO jTPCC : Term-00, terminals=10 15:55:35,452 [main] INFO jTPCC : Term-00, runMins=5 15:55:35,452 [main] INFO jTPCC : Term-00, limitTxnsPerMin=300 15:55:35,452 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true 15:55:35,452 [main] INFO jTPCC : Term-00, 15:55:35,453 [main] INFO jTPCC : Term-00, newOrderWeight=45 15:55:35,453 [main] INFO jTPCC : Term-00, paymentWeight=43 15:55:35,453 [main] INFO jTPCC : Term-00, orderStatusWeight=4 15:55:35,453 [main] INFO jTPCC : Term-00, deliveryWeight=4 15:55:35,453 [main] INFO jTPCC : Term-00, stockLevelWeight=4 15:55:35,453 [main] INFO jTPCC : Term-00, 15:55:35,453 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS 15:55:35,454 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 15:55:35,454 [main] INFO jTPCC : Term-00, 15:55:35,552 [main] INFO jTPCC : Term-00, copied props.gaussdb to my_result_2020-03-10_155535/run.properties 15:55:35,552 [main] INFO jTPCC : Term-00, created my_result_2020-03-10_155535/data/runInfo.csv for runID 2 15:55:35,552 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2020-03-10_155535/data/result.csv 15:55:35,554 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 15:55:35,554 [main] INFO jTPCC : Term-00, osCollectorInterval=1 15:55:35,554 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null 15:55:35,554 [main] INFO jTPCC : Term-00, osCollectorDevices=net_eth0 blk_sda 15:55:35,679 [main] INFO jTPCC : Term-00, 15:55:36,214 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 15615:55:36,215 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 59
運行5分鐘之后，自動顯示運行結果，如下：
16:00:38,727 [Thread-3] INFO jTPCC : Term-00, 16:00:38,727 [Thread-3] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 132.81 16:00:38,727 [Thread-3] INFO jTPCC : Term-00, Measured tpmTOTAL = 299.97 16:00:38,728 [Thread-3] INFO jTPCC : Term-00, Session Start = 2020-03-10 15:55:3616:00:38,728 [Thread-3] INFO jTPCC : Term-00, Session End = 2020-03-10 16:00:3816:00:38,728 [Thread-3] INFO jTPCC : Term-00, Transaction Count = 1510
結果也會保存到csv文件，可用來生成相關報表：
# tree my_result_2020-03-10_155535 my_result_2020-03-10_155535 ├── data │ ├── blk_sda.csv │ ├── net_eth0.csv │ ├── result.csv │ ├── runInfo.csv │ └── sys_info.csv └── run.properties 1 directory, 6 files #
# ./runDatabaseDestroy.sh props.gaussdb # ------------------------------------------------------------# Loading SQL file ./sql.common/tableDrops.sql # ------------------------------------------------------------drop table bmsql_config;drop table bmsql_new_order;drop table bmsql_order_line;drop table bmsql_oorder;drop table bmsql_history;drop table bmsql_customer;drop table bmsql_stock;drop table bmsql_item;drop table bmsql_district;drop table bmsql_warehouse;drop sequence bmsql_hist_id_seq; #
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
