benchmarksql 5.0 支持对MySQL的TPC-C测试
根据官方文档介绍的测试环境，本文中涉及的TPC-C测试将使用 BenchmarkSQL5.0 软件实现。BenchmarkSQL是一款基于JDBC实现的类似于OLTP的TPC-C标准测试工具，目前支持的数据库如：PostgreSQL、Oracle等。
一.下载&编译安装
1.先要确定服务器是否安装了 JDK1.8
[root@sjjh /]# java -version java version "1.7.0_75" OpenJDK Runtime Environment (rhel-2.5.4.2.0.1.el7_0-x86_64 u75-b13) OpenJDK 64-Bit Server VM (build 24.75-b04, mixed mode)
因为自带jdk存储在/usr/lib/jvm下，需要将其删除。
[root@sjjh lib]# mv jvm jvmold [root@sjjh lib]# java -version -bash: /usr/bin/java: ???
2.安装JDK1.8
[root@sjjh /]# tar -zxvf jdk-linux-x64.tar.gz
[root@sjjh /]# mkdir /usr/java
[root@sjjh /]# mv jdk1.8.0_131 /usr/java/
[root@sjjh /]# vi /etc/profile
....
export JAVA_HOME=/usr/java/jdk1.8.0_131
export JRE_HOME=${JAVA_HOME}/jre
export CLASSPATH=.:${JAVA_HOME}/lib:${JRE_HOME}/lib:$CLASSPATH
export JAVA_PATH=${JAVA_HOME}/bin:${JRE_HOME}/bin
export PATH=$PATH:${JAVA_PATH}
[root@sjjh /]# source /etc/profile
[root@sjjh /]# java -version
java version "1.8.0_131"
Java(TM) SE Runtime Environment (build 1.8.0_131-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.131-b11, mixed mode)
[root@shard1 /]# unzip benchmarksql-5.0.zip
[root@shard1 /]# cd benchmarksql-5.0/
加载oracle 驱动
[oracle@sjjh lib]$ ls -lrt total 26084 -rw-r--r--. 1 oracle oinstall 2091135 Jul 11 2013 ojdbc5.jar -rw-r--r--. 1 oracle oinstall 2739616 Jul 11 2013 ojdbc6.jar -rw-r--r--. 1 oracle oinstall 2609739 Jul 11 2013 ojdbc5dms.jar -rw-r--r--. 1 oracle oinstall 3350717 Jul 11 2013 ojdbc6dms.jar -rw-r--r--. 1 oracle oinstall 3424145 Jul 11 2013 ojdbc5_g.jar -rw-r--r--. 1 oracle oinstall 3445412 Jul 11 2013 ojdbc5dms_g.jar -rw-r--r--. 1 oracle oinstall 4494540 Jul 11 2013 ojdbc6_g.jar -rw-r--r--. 1 oracle oinstall 4517780 Jul 11 2013 ojdbc6dms_g.jar -rw-r--r--. 1 oracle oinstall 20365 Jul 11 2013 simplefan.jar [oracle@sjjh lib]$ pwd /u01/app/oracle/product/11.2.0.4/db/jdbc/lib [oracle@sjjh lib]$ cp ojdbc6.jar /benchmarksql-5.0/lib/oracle/ [oracle@sjjh lib]$ cd /benchmarksql-5.0/lib/oracle/ [oracle@sjjh oracle]$ pwd /benchmarksql-5.0/lib/oracle [oracle@sjjh oracle]$ ls -lrt total 2680 -rwxrwxr-x. 1 oracle oinstall 174 May 26 2016 README.txt -rw-r--r--. 1 oracle oinstall 2739616 Nov 14 10:21 ojdbc6.jar
修改props.ora
[oracle@sjjh run]$ vi props.ora db=oracle driver=oracle.jdbc.driver.OracleDriver conn=jdbc:oracle:thin:@localhost:1521:sjjh user=tpcs password=tpcs warehouses=40 loadWorkers=10 terminals=10 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal= //To run for specified minutes- runTxnsPerTerminal must equal zero runMins=10 //Number of total transactions per minute limitTxnsPerMin=0 //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 100 newOrderWeight=45 paymentWeight=43 orderStatusWeight=4 deliveryWeight=4 stockLevelWeight=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
使用ant进行编译
[oracle@sjjh benchmarksql-5.0]$ ant
Buildfile: /benchmarksql-5.0/build.xml
init:
compile:
[javac] Compiling 11 source files to /benchmarksql-5.0/build
dist:
[mkdir] Created dir: /benchmarksql-5.0/dist
[jar] Building jar: /benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar
BUILD SUCCESSFUL
Total time: 2 seconds
初始化数据
[oracle@sjjh run]$ ./runDatabaseBuild.sh props.ora # ------------------------------------------------------------ # 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=oracle.jdbc.driver.OracleDriver conn=jdbc:oracle:thin:@10.138.130.101:1521:sjjh user=tpcs password=*********** warehouses=40 loadWorkers=10 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 004: Loading Warehouse 4 Worker 005: Loading Warehouse 5 Worker 006: Loading Warehouse 6 Worker 007: Loading Warehouse 7 Worker 008: Loading Warehouse 8 Worker 009: Loading Warehouse 9 Worker 000: Loading ITEM done Worker 000: Loading Warehouse 10 Worker 004: Loading Warehouse 4 done Worker 004: Loading Warehouse 11 Worker 007: Loading Warehouse 7 done Worker 007: Loading Warehouse 12 Worker 005: Loading Warehouse 5 done Worker 005: Loading Warehouse 13 Worker 008: Loading Warehouse 8 done Worker 008: Loading Warehouse 14 Worker 006: Loading Warehouse 6 done Worker 001: Loading Warehouse 1 done Worker 006: Loading Warehouse 15 Worker 001: Loading Warehouse 16 Worker 009: Loading Warehouse 9 done Worker 009: Loading Warehouse 17 Worker 002: Loading Warehouse 2 done Worker 002: Loading Warehouse 18 Worker 003: Loading Warehouse 3 done Worker 003: Loading Warehouse 19 Worker 000: Loading Warehouse 10 done Worker 000: Loading Warehouse 20 Worker 004: Loading Warehouse 11 done Worker 004: Loading Warehouse 21 Worker 008: Loading Warehouse 14 done Worker 008: Loading Warehouse 22 Worker 005: Loading Warehouse 13 done Worker 005: Loading Warehouse 23 Worker 007: Loading Warehouse 12 done Worker 006: Loading Warehouse 15 done Worker 007: Loading Warehouse 24 Worker 006: Loading Warehouse 25 Worker 003: Loading Warehouse 19 done Worker 003: Loading Warehouse 26 Worker 002: Loading Warehouse 18 done Worker 002: Loading Warehouse 27 Worker 000: Loading Warehouse 20 done Worker 000: Loading Warehouse 28 Worker 001: Loading Warehouse 16 done Worker 001: Loading Warehouse 29 Worker 009: Loading Warehouse 17 done Worker 009: Loading Warehouse 30 Worker 004: Loading Warehouse 21 done Worker 004: Loading Warehouse 31 Worker 008: Loading Warehouse 22 done Worker 008: Loading Warehouse 32 Worker 006: Loading Warehouse 25 done Worker 006: Loading Warehouse 33 Worker 007: Loading Warehouse 24 done Worker 007: Loading Warehouse 34 Worker 003: Loading Warehouse 26 done Worker 001: Loading Warehouse 29 done Worker 003: Loading Warehouse 35 Worker 001: Loading Warehouse 36 Worker 005: Loading Warehouse 23 done Worker 005: Loading Warehouse 37 Worker 002: Loading Warehouse 27 done Worker 002: Loading Warehouse 38 Worker 000: Loading Warehouse 28 done Worker 000: Loading Warehouse 39 Worker 009: Loading Warehouse 30 done Worker 009: Loading Warehouse 40 Worker 007: Loading Warehouse 34 done Worker 004: Loading Warehouse 31 done Worker 008: Loading Warehouse 32 done Worker 006: Loading Warehouse 33 done Worker 003: Loading Warehouse 35 done Worker 001: Loading Warehouse 36 done Worker 005: Loading Warehouse 37 done Worker 000: Loading Warehouse 39 done Worker 009: Loading Warehouse 40 done Worker 002: Loading Warehouse 38 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_idx1 on 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_idx1 on 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; declare n integer; i integer; dummy integer; begin select max(hist_id) into n from bmsql_history; i := 0; while i < = n loop select 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.nextval create trigger bmsql_history_before_insert before insert on bmsql_history for each row begin if :new.hist_id is null then select 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. -- ----‘
执行压测
[oracle@sjjh run]$ ./runBenchmark.sh props.ora 10:51:30,810 [main] INFO jTPCC : Term-00, 10:51:30,815 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 10:51:30,816 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0 10:51:30,816 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 10:51:30,816 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa 10:51:30,817 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier 10:51:30,821 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck 10:51:30,821 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 10:51:30,822 [main] INFO jTPCC : Term-00, 10:51:30,822 [main] INFO jTPCC : Term-00, db=oracle 10:51:30,822 [main] INFO jTPCC : Term-00, driver=oracle.jdbc.driver.OracleDriver 10:51:30,823 [main] INFO jTPCC : Term-00, conn=jdbc:oracle:thin:@10.138.130.101:1521:sjjh 10:51:30,823 [main] INFO jTPCC : Term-00, user=tpcs 10:51:30,823 [main] INFO jTPCC : Term-00, 10:51:30,823 [main] INFO jTPCC : Term-00, warehouses=40 10:51:30,824 [main] INFO jTPCC : Term-00, terminals=10 10:51:30,827 [main] INFO jTPCC : Term-00, runMins=10 10:51:30,827 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0 10:51:30,827 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true 10:51:30,828 [main] INFO jTPCC : Term-00, 10:51:30,828 [main] INFO jTPCC : Term-00, newOrderWeight=45 10:51:30,828 [main] INFO jTPCC : Term-00, paymentWeight=43 10:51:30,828 [main] INFO jTPCC : Term-00, orderStatusWeight=4 10:51:30,829 [main] INFO jTPCC : Term-00, deliveryWeight=4 10:51:30,829 [main] INFO jTPCC : Term-00, stockLevelWeight=4 10:51:30,829 [main] INFO jTPCC : Term-00, 10:51:30,829 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS 10:51:30,830 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 10:51:30,830 [main] INFO jTPCC : Term-00, 10:51:31,026 [main] INFO jTPCC : Term-00, copied props.ora to my_result_2022-11-14_105131/run.properties 10:51:31,027 [main] INFO jTPCC : Term-00, created my_result_2022-11-14_105131/data/runInfo.csv for runID 1 10:51:31,027 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-11-14_105131/data/result.csv 10:51:31,028 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 10:51:31,029 [main] INFO jTPCC : Term-00, osCollectorInterval=1 10:51:31,029 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null 10:51:31,029 [main] INFO jTPCC : Term-00, osCollectorDevices=null 10:51:31,161 [main] INFO jTPCC : Term-00, 10:51:31,676 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 243 10:51:31,677 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 125 10:51:31,677 [main] INFO jTPCC : Term-00, Term-00, Running Average tpmTOTAL: 10382.94 Current tpmTOTAL: 684828 Memory Usage: 107MB / 649MB 11:01:32,117 [Thread-2] INFO jTPCC : Term-00, 11:01:32,118 [Thread-2] INFO jTPCC : Term-00, 11:01:32,118 [Thread-2] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 4686.82 11:01:32,118 [Thread-2] INFO jTPCC : Term-00, Measured tpmTOTAL = 10382.59 11:01:32,118 [Thread-2] INFO jTPCC : Term-00, Session Start = 2022-11-14 10:51:32 11:01:32,118 [Thread-2] INFO jTPCC : Term-00, Session End = 2022-11-14 11:01:32 11:01:32,119 [Thread-2] INFO jTPCC : Term-00, Transaction Count = 103831'
清理数据
[oracle@sjjh run]$ ./runDatabaseDestroy.sh props.ora # ------------------------------------------------------------ # 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;