南大通用GBase 8s中的IWA安装及使用详解 - 02

原文链接：https://www.gbase.cn/community/post/4296
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

在上一篇文章中，我们介绍了GBase 8s中的内存数据仓库加速器（IWA）的基本概念和安装配置流程。本文将进一步深入IWA的使用，从数据加载到查询优化，为您提供一个完整的IWA使用指南。

如需查阅前文，请参考如下链接：南大通用GBase 8s中的IWA安装及使用详解 - 01

1、启动IWA

首先，用gbasedbt用户创建目录 /opt/hyq/3.5.0_2O2_for_iwa/ids/dwa/demo，这个目录来自于”配置IWA”一节。然后，启动IWA：

#让配置生效
[root@8s-225 ids]# ondwa setup
If you do not already have a default sbspace created and configured in the GBase database server:
a. Create the sbspace. Example:
   onspaces -c -S sbspace1 -p $GBASEDBTDIR/tmp/sbspace1 -o 0 -s 30000
b. In the ONCONFIG configuration file, set the name of the default sbspace in the SBSPACENAME configuration parameter. Example:
   SBSPACENAME sbspace1
c. Restart the GBase database server.

#启动iwa
[root@8s-225 ids]# ondwa start

#查看状态
[root@8s-225 ids]# ondwa status
ID    | Role        | Cat-Status  | HB-Status    | Hostname         | System ID
-----+-------------+-------------+--------------+------------------+------------
0     | COORDINATOR | ACTIVE       |   Healthy    | 8s-225           | 1
1     | WORKER      | ACTIVE      |    Healthy    | 8s-225           | 2

Cluster is in state    : Fully Operational
Expected node count    : 1 coordinator and 1 worker nodes

2、准备加速器及数据

使用prepare_data.sh 脚本文件来准备加速器及数据，prepare_data.sh的内容如下：

#!/bin/sh
# shell need ids and iwa install in the same path.
# This shell need run with gbasedbt

echo --- create database ---
dbaccess - - <<EOF
drop database if exists hyq;
create database hyq with log;
EOF

echo --- create accelerator ---
ondwa getpin | awk 'END{print $1>"tmp.1"; print $2>"tmp.2";print $3>"tmp.3"}'
ip=`cat tmp.1`
port=`cat tmp.2`
pair_code=`cat tmp.3`
dbaccess hyq - <<EOF
execute function ifx_removeDWA('IWAD');
execute function ifx_setupDWA('IWAD', '$ip', '$port', '$pair_code');
EOF

echo --- gbase mode --- create table, insert data ---
dbaccess hyq - <<EOF
drop table if exists  article;
drop table if exists  supplier;
drop table if exists  inventory;
create table  article(
 a_artid integer,
 a_colorid integer,
 a_fitemid integer,
 a_designerid integer,
 a_labelid integer,
 a_package char(25),
 a_size integer,
 a_stickerprice decimal(15,2),
 a_descr char(125)
);

create table  supplier(
 su_suppid integer,
 su_stateid integer,
 su_name char(60),
 su_street char(40),
 su_streetindex char(10),
 su_rebate decimal(15,2),
 su_acctbal decimal(15,2),
 su_indexhits char(140)
);

create table  inventory(
 i_artid integer,
 i_suppid integer,
 i_quantity integer,
 i_descr char(199)
 );

 alter table   article add constraint primary key (a_artid) constraint p7;
 alter table   supplier add constraint primary key (su_suppid) constraint p8;
 alter table   inventory add constraint primary key (i_artid, i_suppid) constraint p11;
 alter table   inventory add constraint foreign key (i_artid) references  article(a_artid) constraint f9;
 alter table   inventory add constraint foreign key (i_suppid) references  supplier(su_suppid) constraint f10;

insert into  article values(1,1,1,1,1,"aaaaaaaaaaaaaaaaaaaa01",1,1,"bbbbbbbbbbbbbbbbbbbb0001");
insert into  article values(2,2,2,2,2,"aaaaaaaaaaaaaaaaaaaa02",2,2,"bbbbbbbbbbbbbbbbbbbb0002");
insert into  article values(3,3,3,3,3,"aaaaaaaaaaaaaaaaaaaa03",3,3,"bbbbbbbbbbbbbbbbbbbb0003");
insert into  article values(4,4,4,4,4,"aaaaaaaaaaaaaaaaaaaa04",4,4,"bbbbbbbbbbbbbbbbbbbb0004");
insert into  supplier values(1,1,"a","aaaaaaaaaaaaaaaaaaaa01","a",1,1,"bbbbbbbbbbbbbbbbbbbb0001");
insert into  supplier values(2,2,"a","aaaaaaaaaaaaaaaaaaaa02","a",2,2,"bbbbbbbbbbbbbbbbbbbb0002");
insert into  supplier values(3,3,"a","aaaaaaaaaaaaaaaaaaaa03","a",3,3,"bbbbbbbbbbbbbbbbbbbb0003");
insert into  supplier values(4,4,"a","aaaaaaaaaaaaaaaaaaaa04","a",4,4,"bbbbbbbbbbbbbbbbbbbb0004");
insert into  inventory values(1,1,1,"aaaaaaaaaaaaaaaaaaaa01");
insert into  inventory values(1,2,1,"aaaaaaaaaaaaaaaaaaaa02");
insert into  inventory values(1,3,1,"aaaaaaaaaaaaaaaaaaaa03");
insert into  inventory values(1,4,1,"aaaaaaaaaaaaaaaaaaaa04");
EOF

使用gbasedbt用户执行脚本prepare_data.sh 

[gbasedbt@8s-225 ids]$ sh prepare_data.sh 

3、创建数据集市，并加载数据至IWA

这里数据集市是由探针生成的。

①启动探针，并执行查询

dbaccess - -

database sysadmin;
update statistics low;
set environment use_dwa 'probe cleanup';
set environment use_dwa 'probe start';
execute function task("set sql tracing on", "1000", "4", "low", "global");

database hyq;
set explain on avoid_execute;
select * from  inventory a,  supplier b,   article c 
where a.i_artid < 108 and a.i_suppid=b.su_stateid and a.i_artid = c.a_artid 
order by a.i_artid desc;

select count(*) from  inventory a,  supplier b,   article c
where a.i_artid < 108 and a.i_suppid=b.su_stateid and a.i_artid=c.a_artid;

②打印探针探测的结果

[root@8s-225 testiwa]# onstat -g probe
On-Line -- Up 04:01:19 -- 315012 Kbytes

DWA probing data for database hyq:

statement 10769:
columns: tabid[colno,...]
         1000[1,2,3,4,5,6,7,8,9]
         1001[1,2,3,4,5,6,7,8]
         1002[1,2,3,4] f
joins:    tabid[colno,...] = tabid[colno,...] (type) {u:unique}
         1002[1] = 1000[1] (inner) u
         1002[2] = 1001[2] (inner)

statement 10770:
columns: tabid[colno,...]
         1000[1]
         1001[2]
         1002[1,2] f
joins:    tabid[colno,...] = tabid[colno,...] (type) {u:unique}
         1002[1] = 1000[1] (inner) u
         1002[2] = 1001[2] (inner)

③由探针生成 mart

按照onstat -g probe填写statement_id对应值

dbaccess hyq –

execute procedure ifx_probe2Mart('hyq', 'ma1', <statement_id>);
execute procedure ifx_probe2Mart('hyq', 'ma2', <statement_id>);

④将mart导出为xml

dbaccess hyq -

execute function lotofile(ifx_genMartDef('ma1'), 'ma1.xml', 'client');
execute function lotofile(ifx_genMartDef('ma2'), 'ma2.xml', 'client');

⑤将mart 加入加速器

dbaccess hyq -

execute function ifx_dropMart('IWAD', 'ma1');
execute function ifx_dropMart('IWAD', 'ma2');
execute function ifx_createMart('IWAD', 'ma1','hyq');
execute function ifx_createMart('IWAD', 'ma2','hyq');
execute function ifx_listMarts('IWAD');

⑥加载数据

dbaccess hyq -

execute function ifx_loadMart('IWAD', 'ma1',  'NONE');
execute function ifx_loadMart('IWAD', 'ma2',  'NONE');

查看统计数据

dbaccess hyq -

execute function ifx_getMartStat('IWAD', 'ma1');
execute function ifx_getMartStat('IWAD', 'ma2');

execute function ifx_listMarts('IWAD');

4、打开执行计划，确认查询使用IWA

①打开查询计划，并且执行查询：

dbaccess hyq -

set explain file to 'sqexplain_iwa.out';
set explain on;
set environment use_dwa '1';

select * from  inventory a,  supplier b,   article c where a.i_artid<108 and a.i_suppid=b.su_stateid and a.i_artid=c.a_artid order by a.i_artid desc;

select count(*) from  inventory a,  supplier b,   article c where a.i_artid < 108 and a.i_suppid=b.su_stateid and a.i_artid=c.a_artid;

②查看查询计划

[root@8s-225 testiwa]# cat sqexplain_iwa.out

QUERY: DWA executed:(OPTIMIZATION TIMESTAMP: 08-07-2024 15:06:00)
------
select * from  inventory a,  supplier b,   article c where a.i_artid<108 and a.i_suppid=b.su_stateid and a.i_artid=c.a_artid order by a.i_artid desc

Estimated Cost: 6
Estimated # of Rows Returned: 1

   1) hyq@IWAD:gbasedbt.aqt89d2011e-700d-4644-becc-133c5d5aced7: DWA REMOTE PATH

     Remote SQL Request:
     {QUERY {FROM   gbasedbt.aqt89d2011e-700d-4644-becc-133c5d5aced7} {WHERE (({< COL12 108 } {ISNOTNULL COL21 } ){ISNOTNULL COL02 } )} {SELECT {SYSCAST COL12 AS INTEGER} {SYSCAST COL15 AS INTEGER} {SYSCAST COL14 AS INTEGER} {SYSCAST COL13 AS CHAR 199 819} {SYSC
     AST COL24 AS INTEGER} {SYSCAST COL21 AS INTEGER} {SYSCAST COL19 AS CHAR 60 819} {SYSCAST COL22 AS CHAR 40 819} {SYSCAST COL23 AS CHAR 10 819} {SYSCAST COL20 AS DECIMAL 15 2} {SYSCAST COL17 AS DECIMAL 15 2} {SYSCAST COL18 AS CHAR 140 819} {SYSCAST COL02 AS
     INTEGER} {SYSCAST COL03 AS INTEGER} {SYSCAST COL06 AS INTEGER} {SYSCAST COL05 AS INTEGER} {SYSCAST COL07 AS INTEGER} {SYSCAST COL08 AS CHAR 25 819} {SYSCAST COL09 AS INTEGER} {SYSCAST COL10 AS DECIMAL 15 2} {SYSCAST COL04 AS CHAR 125 819} } {ORDER COL12  D
     ESC  NULLSFIRST } }                                                                                                                                                                                                                   



QUERY: IDS FYI:(OPTIMIZATION TIMESTAMP: 08-07-2024 15:06:00)
------
select * from  inventory a,  supplier b,   article c where a.i_artid<108 and a.i_suppid=b.su_stateid and a.i_artid=c.a_artid order by a.i_artid desc

Estimated Cost: 6
Estimated # of Rows Returned: 1

   1) gbasedbt.b: SEQUENTIAL SCAN

   2) gbasedbt.a: INDEX PATH

         Filters: gbasedbt.a.i_artid < 108

     (1) Index Name: gbasedbt. 1002_5
         Index Keys: i_suppid   (Serial, fragments: ALL)
         Lower Index Filter: gbasedbt.a.i_suppid = gbasedbt.b.su_stateid
NESTED LOOP JOIN

   3) gbasedbt.c: INDEX PATH

     (1) Index Name: gbasedbt. 1000_1
         Index Keys: a_artid   (Serial, fragments: ALL)
         Lower Index Filter: gbasedbt.a.i_artid = gbasedbt.c.a_artid
NESTED LOOP JOIN


Query statistics:
-----------------

   Table map :
   ----------------------------
   Internal name     Table name
   ----------------------------

   type     rows_prod  est_rows   time       est_cost
   -------------------------------------------------
   dwa      4          0         00:00.02   0


QUERY: DWA executed:(OPTIMIZATION TIMESTAMP: 08-07-2024 15:06:00)
------
select count(*) from  inventory a,  supplier b,   article c where a.i_artid < 108 and a.i_suppid=b.su_stateid and a.i_artid=c.a_artid

Estimated Cost: 5
Estimated # of Rows Returned: 1

   1) hyq@IWAD:gbasedbt.aqt8b68bc76-b461-4653-b809-339b29a74fde: DWA REMOTE PATH

     Remote SQL Request:
     {QUERY {FROM   gbasedbt.aqt8b68bc76-b461-4653-b809-339b29a74fde} {WHERE (({< COL4 108 } {ISNOTNULL COL7 } ){ISNOTNULL COL2 } )} {SELECT {COUNT_BIG(*) } } }                                                                             



QUERY: IDS FYI:(OPTIMIZATION TIMESTAMP: 08-07-2024 15:06:00)
------
select count(*) from  inventory a,  supplier b,   article c where a.i_artid < 108 and a.i_suppid=b.su_stateid and a.i_artid=c.a_artid

Estimated Cost: 5
Estimated # of Rows Returned: 1

   1) gbasedbt.c: INDEX PATH

     (1) Index Name: gbasedbt. 1000_1
         Index Keys: a_artid   (Key-Only)  (Serial, fragments: ALL)
         Upper Index Filter: gbasedbt.c.a_artid < 108

   2) gbasedbt.b: SEQUENTIAL SCAN
NESTED LOOP JOIN

   3) gbasedbt.a: INDEX PATH

     (1) Index Name: gbasedbt. 1002_3
         Index Keys: i_artid i_suppid    (Key-Only)  (Serial, fragments: ALL)
         Lower Index Filter: (gbasedbt.a.i_artid = gbasedbt.c.a_artid AND gbasedbt.a.i_suppid = gbasedbt.b.su_stateid )
NESTED LOOP JOIN


Query statistics:
-----------------

   Table map :
   ----------------------------
   Internal name     Table name
   ----------------------------

   type     rows_prod  est_rows   time       est_cost
   -------------------------------------------------
   dwa      1          0         00:00.00   0

本文通过实际操作示例，展示了如何在GBase 8s中使用IWA进行数据加载和查询优化。IWA作为一种高效的内存计算解决方案，不仅能够提升查询速度，还能够简化数据分析流程。我们希望本文能够帮助您充分利用IWA的强大功能，为您的数据库应用带来更高效的数据处理能力。

原文链接：https://www.gbase.cn/community/post/4296
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。