页眉内容
email varchar(128) NULL, ord int8 NULL, hh24 int8 NULL, ip varchar(17) NULL, area_prov varchar(32) NULL, area_city varchar(32) NULL, if_in_page varchar(2) NULL, if_out_page varchar(2) NULL, if_main_act_page varchar(2) NULL, if_last_main_act_page varchar(2) NULL, if_above_page varchar(2) NULL, page_on_time int8 NULL, goods_id varchar(50) NULL, utm_source varchar(128) NULL, utm_source_type int4 NULL, utm_medium varchar(128) NULL, utm_campaign varchar(128) NULL, loc_url varchar(4096) NULL, ref_url varchar(4096) NULL, user_agent varchar(4096) NULL, browser_name varchar(64) NULL, os varchar(64) NULL, resolution varchar(64) NULL, client_type varchar(64) NULL,
GC.WO-1102
页眉内容
ck_cps_uid varchar(12) NULL, ck_cps_cid varchar(12) NULL, url_cps_uid varchar(12) NULL, url_cps_cid varchar(12) NULL, goods_history varchar(1024) NULL, order_id int8 NULL, ref_id varchar(128) NULL, time_stamp timestamp NULL, log_ip int8 NULL
)WITH (appendonly=true, orientation=column,compresstype=QuickLZ,compresslevel=1) DISTRIBUTED BY (id); 2、 创建位图索引
CREATE INDEX goods_id _bmp_idx ON temp_huagai_weblog_Detail USING bitmap (goods_id); 四、 性能评估
1、 I/O测试sql
select count(1) from kn_webloG_all_detail where visit_date >= '2011-11-01' and visit_date < '2011-11-02'； 2、 CPU测试sql SELECT a.ID ,a.SESSION_ID ,a.ticket_id ,a.global_user_id , a.email GC.WO-1102
页眉内容
,a.ORD ,a.VISIT_DATE ,a.visit_day ,a.HH24 ,A.IP
,coalesce(B.Province,'-') as area_prov ,coalesce(B.city,'-') as area_city , if_in_page
FROM temp_huagai_weblog_Detail A left join (
select a.id,B.PROVINCE,B.CITY from temp_huagai_weblog_Detail a
INNER JOIN ODS_IP B ON A.LOG_IP >= B.HOST_FROM WHERE A.LOG_IP <= B.HOST_TO ) B on A.ID = B.ID; 3、评估方法
gpcheckperf -f all_file -d /dbfast1 -d dw
采用gpcheckperf，统计性能数据，完成评估报告。
五、 问题
1、 Sql
2、 insert into OL_MONITORING_HOUR_FACT select t1.visit_day ,t1.hh24 GC.WO-1102
页眉内容
,t1.pv ,t1.visits ,t1.uv ,t1.uip ,t1.bounces ,t1.exit_visit
,coalesce(t2.order_num,0) visit_order ,coalesce(t2.order_amt,0) visit_order_amt ,coalesce(t2.order_num,0)*1.0/t1.visits exchange_rate ,t1.bounces*1.0/t1.visits bounce_rate ,t1.exit_visit*1.0/t1.pv exit_rate ,now() from (
select visit_day,hh24 ,count(1) pv
,count(distinct session_id) visits ,count(distinct ticket_id)uv ,count(distinct ip) uip
,sum(case when if_out_page= 1 and if_in_page = 1 then 1 else 0 end) bounces ,sum(case when if_out_page = 1 then 1 else 0 end) exit_visit from kn_weblog_Detail_hour
where visit_date >= '2011-11-11' and visit_date < '2011-11-12' group by visit_day,hh24
GC.WO-1102
页眉内容
order by 1,2 )t1 left join ( SELECT
TO_CHAR(to_timestamp(add_time),'YYYYMMDD') visit_day ,EXTRACT(HOUR FROM to_timestamp(add_time)) hh24 ,count(distinct id) order_num ,sum(ORDER_PRICE) order_amt FROM ods_shop101_tbl_goods_order
WHERE to_timestamp(add_time) >= '2011-11-11' AND to_timestamp(add_time) < '2011-11-12'
GROUP BY TO_CHAR(to_timestamp(add_time),'YYYYMMDD') ,EXTRACT(HOUR FROM to_timestamp(add_time)) ORDER BY 1,2
)t2 on t1.visit_day = t2.visit_day and t1.hh24 = t2.hh24 order by 1,2 六、 测试结果
序号 1 参数 取值 调整范围 master 结论 kernel.shmmax 物理内存50% 对GP无明显影响 2 segment 小于物理内存25% 过大,GP无法启动 3 kernel.shmall 物理内存 Master/segment 对GP无明显影响 GC.WO-1102
GP优化方案
