原文链接： 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进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
