TPC-H、TPC-H、TPC-DS布置测验
TPC-H、TPC-H、TPC-DS布置测验
概述
TPC-C
TPC-C是业界常用的一套Benchmark，用于评价在线事务处理（OLTP）体系功用的基准测验。它模拟了一个产品批发公司的出售模型，包括办理订单、办理库存、办理账号出入等操作。TPC-C测验的中心是新订单操作，用于衡量体系每分钟所能处理的买卖数量（tpmC）。
TPC-H
TPC-H是业界常用的一套Benchmark，用于评测数据库的剖析型查询才能，TPC-H 侧重于OLAP。TPC-H查询包括8张数据表、22条杂乱的SQL查询，大多数查询包括若干表Join、子查询和Group-by聚合等。
TPC-DS
TPC-DS选用星型、雪花型等多维数据形式，TPC-DS侧重于OLAP。它包括7张现实表，17张纬度表均匀每张表含有18列。其作业负载包括99个SQL查询，掩盖SQL99和2003的中心部分以及OLAP。这个测验集包括对大数据集的计算、报表生成、联机查询、数据发掘等杂乱运用，测验用的数据和值是有歪斜的，与实在数据共同。
TPC-C布置测验
一、BenchMarkSQL东西布置与装备
1.1运用rz指令上传BenchMarkSQL软件至/home/kingbase中，并解压
|
[root@kingbase ~]# su - kingbase [kingbase@kingbase ~]$ ll benchmarksql-5.0.zip -rw-r--r-- 1 kingbase kingbase 2263539 11月 4 18:15 benchmarksql-5.0.zip [kingbase@kingbase ~]$ unzip benchmarksql-5.0.zip |
1.2装备JDBC
|
[kingbase@kingbase ~]$ cd benchmarksql-5.0/lib [kingbase@kingbase lib]$ mkdir -p kingbase8 [kingbase@kingbase lib]$ cp /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0020/Interface/jdbc/kingbase*.jar /home/kingbase/benchmarksql-5.0/lib/kingbase8 [kingbase@kingbase lib]$ cp /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0020/Interface/jdbc/kingbase8-8.6.0.jar /home/kingbase/benchmarksql-5.0/lib [kingbase@kingbase lib]$ ll kingbase8 -rw-rw-r-- 1 kingbase kingbase 1189389 11月 5 14:54 kingbase8-8.6.0.jar -rw-rw-r-- 1 kingbase kingbase 1045436 11月 5 14:54 kingbase8-8.6.0.jre6.jar -rw-rw-r-- 1 kingbase kingbase 1185028 11月 5 14:54 kingbase8-8.6.0.jre7.jar [kingbase@kingbase lib]$ ll kingbase8-8.6.0.jar -rw-rw-r-- 1 kingbase kingbase 1189389 11月 5 14:54 kingbase8-8.6.0.jar |
1.3运用ant重新编绎
|
（1）上传并解压apache-ant-1.10.15-bin.zip至/home/kingbase [kingbase@kingbase ~]$ cd /home/kingbase [kingbase@kingbase ~]$ ls -l apache-ant-1.10.15-bin.zip [kingbase@kingbase ~]$ unzip apache-ant-1.10.15-bin.zip （2）上传并解压jdk-8u341-linux-x64.tar.gz，并装备jdk环境变量 [kingbase@kingbase ~]$ cd /home/kingbase [kingbase@kingbase ~]$ ls -l jdk-8u341-linux-x64.tar.gz [kingbase@kingbase ~]$ tar -xvf jdk-8u341-linux-x64.tar.gz 装备jdk环境变量： [kingbase@kingbase ~]$ vi ~/.bash_profile 增加如下内容： export ANT_HOME=/home/kingbase/apache-ant-1.10.15 export PATH=$PATH:$ANT_HOME/bin export JAVA_HOME=/home/kingbase/jdk1.8.0_341 export JRE_HOME=$JAVA_HOME/jre export CLASSPATH=$JAVA_HOME/lib:$JRE_HOME/lib:$CLASSPATH export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$PATH 收效环境变量： [kingbase@kingbase ~]$ source ~/.bash_profile （3）运用ant重编译benchmarksql [kingbase@kingbase ~]$ cd /home/kingbase/benchmarksql-5.0 [kingbase@kingbase benchmarksql-5.0]$ ant clean Buildfile: /home/kingbase/benchmarksql-5.0/build.xml clean: [delete] Deleting directory /home/kingbase/benchmarksql-5.0/build BUILD SUCCESSFUL Total time: 1 second [kingbase@kingbase benchmarksql-5.0]$ ant Buildfile: /home/kingbase/benchmarksql-5.0/build.xml init: [mkdir] Created dir: /home/kingbase/benchmarksql-5.0/build compile: [javac] Compiling 11 source files to /home/kingbase/benchmarksql-5.0/build dist: [mkdir] Created dir: /home/kingbase/benchmarksql-5.0/dist [jar] Building jar: /home/kingbase/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar BUILD SUCCESSFUL Total time: 4 seconds |
二、预备测验数据
2.1创立tpcc用户并颁发相关权限
|
[kingbase@kingbase ~]$ ksql -Usystem -dtest 用户 system 的口令： 输入 "help" 来获取协助信息. test=# CREATE USER tpcc with superuser password 'tpcc'; CREATE ROLE test=# GRANT ALL privileges on database test to tpcc; GRANT |
2.2修正benchmarksql装备文件
|
修正./run/props.kingbase文件，并修正内容： [kingbase@kingbase ~]$ cd /home/kingbase/benchmarksql-5.0 [kingbase@kingbase benchmarksql-5.0]$ cp ./run/props.pg ./run/props.kingbase [kingbase@kingbase benchmarksql-5.0]$ vi ./run/props.kingbase 修正后内容参照如下 db=postgres driver=com.kingbase8.Driver conn=jdbc:kingbase8://192.168.40.111:54321/test user=tpcc password=tpcc warehouses=10 ##仓数(10仓大约1.12G数据) loadWorkers=4 ##加载数据的进程数 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 ##压测时刻(分钟)：一般20分钟左右，视情况而定 //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 //The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec 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_enp0s3 blk_sda |
2.3增加测验表及测验数据
|
[kingbase@kingbase ~]$ cd /home/kingbase/benchmarksql-5.0/run [kingbase@kingbase run]$ chmod +x *.sh [kingbase@kingbase run]$ bash runDatabaseBuild.sh props.kingbase # ------------------------------------------------------------ # 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.kingbase8.Driver conn=jdbc:kingbase8://192.168.56.5:54321/test user=tpcc 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 001: Loading Warehouse 1 done Worker 001: Loading Warehouse 5 Worker 002: Loading Warehouse 2 done Worker 002: 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 5 done Worker 000: Loading Warehouse 9 Worker 002: Loading Warehouse 6 done Worker 002: Loading Warehouse 10 Worker 003: Loading Warehouse 7 done Worker 000: Loading Warehouse 8 done Worker 000: 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_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.postgres/extraHistID.sql # ------------------------------------------------------------ -- ---- -- Extra Schema objects/definitions for history.hist_id in PostgreSQL -- ---- -- ---- -- 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 a sequence and a trigger. -- ---- -- Adjust the sequence above the current max(hist_id) select setval('bmsql_hist_id_seq', (select max(hist_id) from bmsql_history)); -- Make nextval(seq) the default value of the hist_id column. alter table bmsql_history alter column hist_id set default nextval('bmsql_hist_id_seq'); -- Add a primary key history(hist_id) alter table bmsql_history add primary key (hist_id); # ------------------------------------------------------------ # Loading SQL file ./sql.postgres/buildFinish.sql # ------------------------------------------------------------ -- ---- -- Extra commands to run after the tables are created, loaded, -- indexes built and extra's created. -- PostgreSQL version. -- ---- vacuum analyze; [kingbase@kingbase run]$ |
三、调整数据库装备
3.1调整目标的束缚
|
[kingbase@kingbase ~]$ ksql -Usystem -dtest 用户 system 的口令： 输入 "help" 来获取协助信息. test=# CREATE INDEX idx_oorder_2 ON bmsql_oorder(o_w_id,o_d_id,o_c_id,o_id); CREATE INDEX test=# CHECKPOINT; CHECKPOINT |
3.2依据硬件装备调整相关参数
|
依据实际情况而定： 1.调整体系参数：封闭透明大页、翻开网卡的large-receive-offload(lro)功用等 2.网卡中止中心数绑定 3.修正网卡的mtu 4.禁用numa_balancing 5.数据库参数调优 6.绑核发动数据库等等 |
四、开端测验
4.1衔接测验用户
|
[kingbase@kingbase ~]$ ksql -Usystem -dtest 用户 system 的口令： 输入 "help" 来获取协助信息. test=# CREATE EXTENSION sys_prewarm; ##创立sys_prewarm扩展，进行数据预热 test=# SELECT * FROM sys_available_extensions WHERE name like '%warm%'; |
4.2预加热数据
|
SELECT sys_prewarm('bmsql_config'); SELECT sys_prewarm('bmsql_customer'); SELECT sys_prewarm('bmsql_district'); SELECT sys_prewarm('bmsql_hist_id_seq'); SELECT sys_prewarm('bmsql_history'); SELECT sys_prewarm('bmsql_item'); SELECT sys_prewarm('bmsql_new_order'); SELECT sys_prewarm('bmsql_oorder'); SELECT sys_prewarm('bmsql_order_line'); SELECT sys_prewarm('bmsql_stock'); SELECT sys_prewarm('bmsql_warehouse'); SELECT sys_prewarm('bmsql_config_pkey'); SELECT sys_prewarm('bmsql_customer_idx1'); SELECT sys_prewarm('bmsql_customer_pkey'); SELECT sys_prewarm('bmsql_district_pkey'); SELECT sys_prewarm('bmsql_item_pkey'); SELECT sys_prewarm('bmsql_new_order_pkey'); SELECT sys_prewarm('bmsql_oorder_idx1'); SELECT sys_prewarm('bmsql_oorder_pkey'); SELECT sys_prewarm('bmsql_order_line_pkey'); SELECT sys_prewarm('bmsql_warehouse_pkey'); SELECT sys_prewarm('IDX_OORDER_2'); |
4.3测验
|
[kingbase@kingbase ~]$ cd /home/kingbase/benchmarksql-5.0/run [kingbase@kingbase run]$ bash runBenchmark.sh props.kingbase |
4.4测验成果
|
测验成果：
|
附：htop东西装置
1.运用rz指令上传htop-2.2.0.tar.gz到服务器并解压
|
[root@kingbase ~]# gunzip htop-2.2.0.tar.gz [root@kingbase ~]# tar -xvf htop-2.2.0.tar |
2.装置依靠包
|
[root@kingbase ~]# yum install gcc ncurses-devel -y |
3.编译装置
|
[root@kingbase ~]# cd htop-2.2.0/ [root@kingbase htop-2.2.0]# ./configure [root@kingbase htop-2.2.0]# make && make install |
|
[root@kingbase ~]# htop
|
TPC-H布置测验
一、TPC-H东西布置与装备
1.1运用rz指令上传TPC-H-Tool_v3.0.1.zip软件至/home/kingbase中，并解压
|
[root@kingbase ~]# su - kingbase [kingbase@kingbase ~]$ unzip TPC-H-Tool_v3.0.1.zip [kingbase@kingbase ~]$ mv TPC-H\ V3.0.1 tpc-h-tool |
1.2修正修正makefile.suite
|
[kingbase@kingbase ~]$ cd /home/kingbase/tpc-h-tool/dbgen/ [kingbase@kingbase dbgen]$ vi makefile.suite --在CC后填写gcc CC = gcc DATABASE= POSTGRESQL MACHINE = LINUX WORKLOAD = TPCH
|
1.3修正tpcd.h，增加以下宏
|
[kingbase@kingbase ~]$ cd /home/kingbase/tpc-h-tool/dbgen/ [kingbase@kingbase dbgen]$ vi tpcd.h 在文件最终增加以下宏即可： #ifdef POSTGRESQL #define GEN_QUERY_PLAN "EXPLAIN" #define |
