原文链接：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-versionopenjdkversion"1.8.0_181"OpenJDKRuntimeEnvironment(build1.8.0_181-b13)OpenJDK64-BitServerVM(build25.181-b13,mixedmode)#2.2 安装依赖包

#yum-yinstallant2.3 上传BenchmarkSQL工具包并解压

这里使用benchmarksql-5.0.zip工具包。

#mkdir-p/software/benchmark/#cd/software/benchmark/#ls-l总用量2212-rw-r--r--1rootroot22635393月1013:48benchmarksql-5.0.zip##unzipbenchmarksql-5.0.zipArchive:benchmarksql-5.0.zip…….…….#ls-l总用量2212drwxr-xr-x6rootroot1245月262016benchmarksql-5.0-rw-r--r--1rootroot22635393月1013:48benchmarksql-5.0.zip##cdbenchmarksql-5.0/#ls-l总用量24-rwxr-xr-x1rootroot11305月262016build.xmldrwxr-xr-x3rootroot175月262016doc-rwxr-xr-x1rootroot63765月262016HOW-TO-RUN.txtdrwxr-xr-x5rootroot1295月262016lib-rwxr-xr-x1rootroot53185月262016README.mddrwxr-xr-x7rootroot40965月262016rundrwxr-xr-x6rootroot675月262016src#2.4 编译BenchmarkSQL

使用ANT编译BenchMark的包，需要用到BenchMark的build包，进入到BenchMark的目录，执行ant进行编译。

#cd/software/benchmark/benchmarksql-5.0/#antBuildfile:/software/benchmark/benchmarksql-5.0/build.xmlinit:[mkdir]Createddir:/software/benchmark/benchmarksql-5.0/buildcompile:[javac]Compiling11sourcefilesto/software/benchmark/benchmarksql-5.0/builddist:[mkdir]Createddir:/software/benchmark/benchmarksql-5.0/dist[jar]Buildingjar:/software/benchmark/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jarBUILDSUCCESSFULTotaltime:4seconds#ls-l总用量28drwxr-xr-x2rootroot40963月1013:52build-rwxr-xr-x1rootroot11305月262016build.xmldrwxr-xr-x2rootroot343月1013:52distdrwxr-xr-x3rootroot175月262016doc-rwxr-xr-x1rootroot63765月262016HOW-TO-RUN.txtdrwxr-xr-x5rootroot1295月262016lib-rwxr-xr-x1rootroot53185月262016README.mddrwxr-xr-x7rootroot40965月262016rundrwxr-xr-x6rootroot675月262016src#2.5 拷贝GaussDB T的JDBC驱动到lib目录

拷贝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-x1rootroot3467295月262016apache-log4j-extras-1.1.jar-r-x------1rootroot4430903月1014:34com.huawei.gauss.jdbc.ZenithDriver-GaussDB_T_1.0.2.B307.jardrwxr-xr-x2rootroot605月262016firebird-rwxr-xr-x1rootroot4898835月262016log4j-1.2.17.jardrwxr-xr-x2rootroot425月262016oracledrwxr-xr-x2rootroot445月262016postgres#2.6 创建测试数据库用户并授权

Zsql连接数据库，创建测试用户并授权：

$zsqlomm/yhadmin_123@192.168.179.123:1888-qconnected.SQL>createuseraps2identifiedbyaps2_123;Succeed.SQL>grantconnect,resourcetoaps2;Succeed.SQL>exit$2.7 创建benchmark配置文件

在benchmarksql的run目录下，编辑测试的配置文件props.gaussdb。

#pwd/software/benchmark/benchmarksql-5.0/run#cpprops.oraprops.gaussdb#viprops.gaussdbdb=oracledriver=com.huawei.gauss.jdbc.ZenithDriverconn=jdbc:zenith:@192.168.179.123:1888user=aps2password=aps2_123warehouses=10loadWorkers=4terminals=10//Torunspecifiedtransactionsperterminal-runMinsmustequalzerorunTxnsPerTerminal=0//Torunforspecifiedminutes-runTxnsPerTerminalmustequalzerorunMins=5//NumberoftotaltransactionsperminutelimitTxnsPerMin=300//Settotruetorunin4.xcompatiblemode.Settofalsetousethe//entireconfigureddatabaseevenly.terminalWarehouseFixed=true//Thefollowingfivevaluesmustaddupto100newOrderWeight=45paymentWeight=43orderStatusWeight=4deliveryWeight=4stockLevelWeight=4//Directorynametocreateforcollectingdetailedresultdata.//Commentthisouttosuppress.resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tSosCollectorScript=./misc/os_collector_linux.pyosCollectorInterval=1//osCollectorSSHAddr=user@dbhostosCollectorDevices=net_eth0blk_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.shprops.gaussdb#------------------------------------------------------------#LoadingSQLfile./sql.common/tableCreates.sql#------------------------------------------------------------createtablebmsql_config(cfg_namevarchar(30)primarykey,cfg_valuevarchar(50));createtablebmsql_warehouse(w_idintegernotnull,w_ytddecimal(12,2),w_taxdecimal(4,4),w_namevarchar(10),w_street_1varchar(20),w_street_2varchar(20),w_cityvarchar(20),w_statechar(2),w_zipchar(9));createtablebmsql_district(d_w_idintegernotnull,d_idintegernotnull,d_ytddecimal(12,2),d_taxdecimal(4,4),d_next_o_idinteger,d_namevarchar(10),d_street_1varchar(20),d_street_2varchar(20),d_cityvarchar(20),d_statechar(2),d_zipchar(9));createtablebmsql_customer(c_w_idintegernotnull,c_d_idintegernotnull,c_idintegernotnull,c_discountdecimal(4,4),c_creditchar(2),c_lastvarchar(16),c_firstvarchar(16),c_credit_limdecimal(12,2),c_balancedecimal(12,2),c_ytd_paymentdecimal(12,2),c_payment_cntinteger,c_delivery_cntinteger,c_street_1varchar(20),c_street_2varchar(20),c_cityvarchar(20),c_statechar(2),c_zipchar(9),c_phonechar(16),c_sincetimestamp,c_middlechar(2),c_datavarchar(500));createsequencebmsql_hist_id_seq;createtablebmsql_history(hist_idinteger,h_c_idinteger,h_c_d_idinteger,h_c_w_idinteger,h_d_idinteger,h_w_idinteger,h_datetimestamp,h_amountdecimal(6,2),h_datavarchar(24));createtablebmsql_new_order(no_w_idintegernotnull,no_d_idintegernotnull,no_o_idintegernotnull);createtablebmsql_oorder(o_w_idintegernotnull,o_d_idintegernotnull,o_idintegernotnull,o_c_idinteger,o_carrier_idinteger,o_ol_cntinteger,o_all_localinteger,o_entry_dtimestamp);createtablebmsql_order_line(ol_w_idintegernotnull,ol_d_idintegernotnull,ol_o_idintegernotnull,ol_numberintegernotnull,ol_i_idintegernotnull,ol_delivery_dtimestamp,ol_amountdecimal(6,2),ol_supply_w_idinteger,ol_quantityinteger,ol_dist_infochar(24));createtablebmsql_item(i_idintegernotnull,i_namevarchar(24),i_pricedecimal(5,2),i_datavarchar(50),i_im_idinteger);createtablebmsql_stock(s_w_idintegernotnull,s_i_idintegernotnull,s_quantityinteger,s_ytdinteger,s_order_cntinteger,s_remote_cntinteger,s_datavarchar(50),s_dist_01char(24),s_dist_02char(24),s_dist_03char(24),s_dist_04char(24),s_dist_05char(24),s_dist_06char(24),s_dist_07char(24),s_dist_08char(24),s_dist_09char(24),s_dist_10char(24));StartingBenchmarkSQLLoadDatadriver=com.huawei.gauss.jdbc.ZenithDriverconn=jdbc:zenith:@127.0.0.1:1888user=aps2password=***********warehouses=10loadWorkers=4fileLocation(notdefined)csvNullValue(notdefined-usingdefault'NULL')Worker000:LoadingITEMWorker001:LoadingWarehouse1Worker002:LoadingWarehouse2Worker003:LoadingWarehouse3Worker000:LoadingITEMdoneWorker000:LoadingWarehouse4Worker002:LoadingWarehouse2doneWorker002:LoadingWarehouse5Worker001:LoadingWarehouse1doneWorker001:LoadingWarehouse6Worker003:LoadingWarehouse3doneWorker003:LoadingWarehouse7Worker000:LoadingWarehouse4doneWorker000:LoadingWarehouse8Worker001:LoadingWarehouse6doneWorker001:LoadingWarehouse9Worker002:LoadingWarehouse5doneWorker002:LoadingWarehouse10Worker003:LoadingWarehouse7doneWorker000:LoadingWarehouse8doneWorker001:LoadingWarehouse9doneWorker002:LoadingWarehouse10done#------------------------------------------------------------#LoadingSQLfile./sql.common/indexCreates.sql#------------------------------------------------------------altertablebmsql_warehouseaddconstraintbmsql_warehouse_pkeyprimarykey(w_id);altertablebmsql_districtaddconstraintbmsql_district_pkeyprimarykey(d_w_id,d_id);altertablebmsql_customeraddconstraintbmsql_customer_pkeyprimarykey(c_w_id,c_d_id,c_id);createindexbmsql_customer_idx1onbmsql_customer(c_w_id,c_d_id,c_last,c_first);altertablebmsql_oorderaddconstraintbmsql_oorder_pkeyprimarykey(o_w_id,o_d_id,o_id);createuniqueindexbmsql_oorder_idx1onbmsql_oorder(o_w_id,o_d_id,o_carrier_id,o_id);altertablebmsql_new_orderaddconstraintbmsql_new_order_pkeyprimarykey(no_w_id,no_d_id,no_o_id);altertablebmsql_order_lineaddconstraintbmsql_order_line_pkeyprimarykey(ol_w_id,ol_d_id,ol_o_id,ol_number);altertablebmsql_stockaddconstraintbmsql_stock_pkeyprimarykey(s_w_id,s_i_id);altertablebmsql_itemaddconstraintbmsql_item_pkeyprimarykey(i_id);#------------------------------------------------------------#LoadingSQLfile./sql.common/foreignKeys.sql#------------------------------------------------------------altertablebmsql_districtaddconstraintd_warehouse_fkeyforeignkey(d_w_id)referencesbmsql_warehouse(w_id);altertablebmsql_customeraddconstraintc_district_fkeyforeignkey(c_w_id,c_d_id)referencesbmsql_district(d_w_id,d_id);altertablebmsql_historyaddconstrainth_customer_fkeyforeignkey(h_c_w_id,h_c_d_id,h_c_id)referencesbmsql_customer(c_w_id,c_d_id,c_id);altertablebmsql_historyaddconstrainth_district_fkeyforeignkey(h_w_id,h_d_id)referencesbmsql_district(d_w_id,d_id);altertablebmsql_new_orderaddconstraintno_order_fkeyforeignkey(no_w_id,no_d_id,no_o_id)referencesbmsql_oorder(o_w_id,o_d_id,o_id);altertablebmsql_oorderaddconstrainto_customer_fkeyforeignkey(o_w_id,o_d_id,o_c_id)referencesbmsql_customer(c_w_id,c_d_id,c_id);altertablebmsql_order_lineaddconstraintol_order_fkeyforeignkey(ol_w_id,ol_d_id,ol_o_id)referencesbmsql_oorder(o_w_id,o_d_id,o_id);altertablebmsql_order_lineaddconstraintol_stock_fkeyforeignkey(ol_supply_w_id,ol_i_id)referencesbmsql_stock(s_w_id,s_i_id);altertablebmsql_stockaddconstraints_warehouse_fkeyforeignkey(s_w_id)referencesbmsql_warehouse(w_id);altertablebmsql_stockaddconstraints_item_fkeyforeignkey(s_i_id)referencesbmsql_item(i_id);#------------------------------------------------------------#LoadingSQLfile./sql.oracle/extraHistID.sql#--------------------------------------------------------------------ExtraSchemaobjects/definitionsforhistory.hist_idinOracle--------------ThisisanextracolumnnotpresentintheTPC-C--specs.Itisusefulforreplicationsystemslike--BucardoandSlony-I,whichliketohaveaprimary--keyonatable.Itisanauto-incrementorserial--columntype.Thedefinitionbelowiscompatible--withOracle11g,usingthesequenceinatrigger.--------Adjustthesequenceabovethecurrentmax(hist_id)altersequencebmsql_hist_id_seqincrementby30000;declareninteger;iinteger;dummyinteger;beginselectmax(hist_id)intonfrombmsql_history;i:=0;whilei<=nloopselectbmsql_hist_id_seq.nextvalintodummyfromdual;i:=i+30000;endloop;end;;altersequencebmsql_hist_id_seqincrementby1;--Createatriggerthatforceshist_idtobehist_id_seq.nextvalcreatetriggerbmsql_history_before_insertbeforeinsertonbmsql_historyforeachrowbeginif:new.hist_idisnullthenselectbmsql_hist_id_seq.nextvalinto:new.hist_idfromdual;endif;end;;--Addaprimarykeyhistory(hist_id)altertablebmsql_historyaddprimarykey(hist_id);#------------------------------------------------------------#LoadingSQLfile./sql.common/buildFinish.sql#--------------------------------------------------------------------Extracommandstorunafterthetablesarecreated,loaded,--indexesbuiltandextra'screated.------#2.9 开始测试

进入benchmarksql的run目录下，运行./runBenchmark.sh props.gaussdb脚本，开始性能测试。

#pwd/software/benchmark/benchmarksql-5.0/run#./runBenchmark.shprops.gaussdb15:55:35,431[main]INFOjTPCC:Term-00,15:55:35,443[main]INFOjTPCC:Term-00,+-------------------------------------------------+15:55:35,443[main]INFOjTPCC:Term-00,BenchmarkSQLv5.015:55:35,444[main]INFOjTPCC:Term-00,+-------------------------------------------------+15:55:35,444[main]INFOjTPCC:Term-00,(c)2003,RaulBarbosa15:55:35,444[main]INFOjTPCC:Term-00,(c)2004-2016,DenisLussier15:55:35,448[main]INFOjTPCC:Term-00,(c)2016,JanWieck15:55:35,448[main]INFOjTPCC:Term-00,+-------------------------------------------------+15:55:35,448[main]INFOjTPCC:Term-00,15:55:35,448[main]INFOjTPCC:Term-00,db=oracle15:55:35,449[main]INFOjTPCC:Term-00,driver=com.huawei.gauss.jdbc.ZenithDriver15:55:35,449[main]INFOjTPCC:Term-00,conn=jdbc:zenith:@127.0.0.1:188815:55:35,449[main]INFOjTPCC:Term-00,user=aps215:55:35,449[main]INFOjTPCC:Term-00,15:55:35,449[main]INFOjTPCC:Term-00,warehouses=1015:55:35,450[main]INFOjTPCC:Term-00,terminals=1015:55:35,452[main]INFOjTPCC:Term-00,runMins=515:55:35,452[main]INFOjTPCC:Term-00,limitTxnsPerMin=30015:55:35,452[main]INFOjTPCC:Term-00,terminalWarehouseFixed=true15:55:35,452[main]INFOjTPCC:Term-00,15:55:35,453[main]INFOjTPCC:Term-00,newOrderWeight=4515:55:35,453[main]INFOjTPCC:Term-00,paymentWeight=4315:55:35,453[main]INFOjTPCC:Term-00,orderStatusWeight=415:55:35,453[main]INFOjTPCC:Term-00,deliveryWeight=415:55:35,453[main]INFOjTPCC:Term-00,stockLevelWeight=415:55:35,453[main]INFOjTPCC:Term-00,15:55:35,453[main]INFOjTPCC:Term-00,resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS15:55:35,454[main]INFOjTPCC:Term-00,osCollectorScript=./misc/os_collector_linux.py15:55:35,454[main]INFOjTPCC:Term-00,15:55:35,552[main]INFOjTPCC:Term-00,copiedprops.gaussdbtomy_result_2020-03-10_155535/run.properties15:55:35,552[main]INFOjTPCC:Term-00,createdmy_result_2020-03-10_155535/data/runInfo.csvforrunID215:55:35,552[main]INFOjTPCC:Term-00,writingpertransactionresultstomy_result_2020-03-10_155535/data/result.csv15:55:35,554[main]INFOjTPCC:Term-00,osCollectorScript=./misc/os_collector_linux.py15:55:35,554[main]INFOjTPCC:Term-00,osCollectorInterval=115:55:35,554[main]INFOjTPCC:Term-00,osCollectorSSHAddr=null15:55:35,554[main]INFOjTPCC:Term-00,osCollectorDevices=net_eth0blk_sda15:55:35,679[main]INFOjTPCC:Term-00,15:55:36,214[main]INFOjTPCC:Term-00,CvalueforC_LASTduringload:15615:55:36,215[main]INFOjTPCC:Term-00,CvalueforC_LASTthisrun:592.10 测试结果

运行5分钟之后，自动显示运行结果，如下：

16:00:38,727[Thread-3]INFOjTPCC:Term-00,16:00:38,727[Thread-3]INFOjTPCC:Term-00,MeasuredtpmC(NewOrders)=132.8116:00:38,727[Thread-3]INFOjTPCC:Term-00,MeasuredtpmTOTAL=299.9716:00:38,728[Thread-3]INFOjTPCC:Term-00,SessionStart=2020-03-1015:55:3616:00:38,728[Thread-3]INFOjTPCC:Term-00,SessionEnd=2020-03-1016:00:3816:00:38,728[Thread-3]INFOjTPCC:Term-00,TransactionCount=1510

结果也会保存到csv文件，可用来生成相关报表：

#treemy_result_2020-03-10_155535my_result_2020-03-10_155535├──data│├──blk_sda.csv│├──net_eth0.csv│├──result.csv│├──runInfo.csv│└──sys_info.csv└──run.properties1directory,6files#2.11 清理数据

#./runDatabaseDestroy.shprops.gaussdb#------------------------------------------------------------#LoadingSQLfile./sql.common/tableDrops.sql#------------------------------------------------------------droptablebmsql_config;droptablebmsql_new_order;droptablebmsql_order_line;droptablebmsql_oorder;droptablebmsql_history;droptablebmsql_customer;droptablebmsql_stock;droptablebmsql_item;droptablebmsql_district;droptablebmsql_warehouse;dropsequencebmsql_hist_id_seq;#