原文链接: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技术社区,南大通用致力于成为用户最信赖的数据库产品供应商。