GP优化方案
一、 参数调整
1、块I/O参数
1)参数描述
此参数用来设置块设备参数。
2)现参数:
现没有设置块I/O参数。
3)加入参数:
#vi /etc/rc.d/rc.local
blockdev --setra 16384 /dev/sdb
注:master、standby节点不需修改。
2、I/O调度算法
由于数据仓库属于IO敏感性应用,为了提高系统效率,生产环境中,我们应该在LINUX内核上修改IO调度的算法。以root身份编辑/boot/grub/menu.lst,添加一行
elevator=deadline,但是不要修改failsafe的定义,重启系统(必须),再以root身份执行命令cat /sys/block/*/queue/scheduler,输出的每行应该含有有[deadline]。
3、网络参数
rmem_default — 默认的接收窗口大小。
rmem_max — 接收窗口的最大大小。
wmem_default — 默认的发送窗口大小。
wmem_max — 发送窗口的最大大小
使用 /etc/sysctl.conf 在系统启动时把参数配置成您所设置的值:
net.core.rmem_default = 256960
net.core.rmem_max = 256960
net.core.wmem_default = 256960
net.core.wmem_max = 256960
net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_sack = 0
net.ipv4.tcp_window_scaling = 1
然后重新启动网络守护程序/etc/rc.d/init.d/network restart。
4、系统参数(已完成)
1、参数描绘
cat /etc/security/limits.conf
2、现参数:
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
5、GP相关参数
#vi /etc/sysctl.conf
kernel.sem = 250 64000 100 512
kernel.shmmax = 500000000
kernel.shmmni = 4096
kernel.shmall = 4000000000
kernel.sem = 250 64000 100 512
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
net.ipv4.tcp_syncookies = 1
net.ipv4.ip_forward = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_max_syn_backlog=4096
net.ipv4.conf.all.arp_filter = 1
net.core.netdev_max_backlog=10000
net.ipv4.ip_local_port_range = 1025 65535
1、 kernel.shmmax = 7516192768 (8G,取物理内存16G的50%)
2、 kernel.shmall = 4000000000(物理内存16G时相应大小)
3、 kernel.sem = 250 64000 100 1024
6、GP参数
注:segment host上是4个primary instance,4个mirror instance.
cat /gpmaster/gp-1/postgresql.conf
1、 shared_buffers(local, max_connections*16K)
shared_buffers = 1600MB # master、standby
shared_buffers = 200MB # segment
2、 work_mem(,global,物理内存的2%-4%)
work_mem = 640MB # master、standby
3、 effective_cache_size(master节点,设为物理内存的85%,15032385536)
effective_cache_size = 9600MB
4、 mainteance_work_mem(global,CREATE INDEX, VACUUM等时用到)
maintenance_work_mem = 800MB
5、 max_connections(local,最大连接数)
max_connections = 200 #(master、standby)
max_connections = 1200 #(segment)
6、 max_prepared_transactions(local,与master最大连接数相同)
max_prepraed_transacrions=300 #(master与segment instance相同)
二、 表整理(以kn_weblog_detail为例)
1、 表统计
select relname from pg_class t where t.relname like 'ods%';
select relname from pg_class t where t.relname like 'kn%';
2、 VACUUM
VACUUM [ FULL | FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL | FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
3、 Analyze
ANALYZE [ table [ (column [, ...] ) ] ]
三、 按列存储优化(以kn_weblog_detail为例)
1、 建表:
create table temp_huagai_weblog_Detail_test
(
id varchar(128) NULL,
session_id varchar(120) NULL,
ticket_id varchar(120) NULL,
global_user_id int8 NULL,
visit_date timestamp NULL,
visit_day int8 NULL,
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,
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
,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
,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
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 | kernel.shmmax | 物理内存50% | master | 对GP无明显影响 |
2 |
| 小于物理内存25% | segment | 过大,GP无法启动 |
3 | kernel.shmall | 物理内存 | Master/segment | 对GP无明显影响 |
4 | kernel.sem | 1024 | Master/segment | 对GP无明显影响 |
5 | 块参数 | 16384 | Master/segment | 对GP无明显影响 |
6 | IO调度算法 |
| Master/segment | 对GP无明显影响 |
7 | 网络参数 |
| Master/segment | 对GP无明显影响 |
8 | max_connections | 200 | master | GP启动正常 |
9 |
| 1200 | segment | GP启动正常 |
10 | max_prepared_transactions | 300 | Master/segment | GP启动正常 |
11 | shared_buffers | 400MB | master | GP启动正常 |
12 | work_mem | 160MB | global | GP启动正常 |
13 | effective_cache_size | 500MB | master | 对GP无明显影响 |
七、 结论
结合前面生产环境上参数优化分析,总结如下:
1、 在生产环境上max_connections和max_prepared_transactions修改导致GP无法启动的现象没有出现;
2、 测试环境上kernel.shmmax(master物理内存50%,segment物理内存25%),没有使用交换分区。之前,生产环境上,kernel.shmmax(master和 segment)均取物理内存85%,导致内存交换;
在生产环境上shared_buffers修改导致GP无法启动的现象没有出现。
八、 优化建议
可以进一步在生产环境上逐项优化。
优化方法及步骤如下:
1、 kernel.shmmax:master/standby取物理内存50%;segment取物理内存25%;
2、 max_connections(master/standby为200segment1200)
max_prepared_transactions(master/standby/segment均为300)
3、 shared_buffers:
master/standby取物理内存10%;segment取物理内存10%/实例数;
4、 块参数:16384;
5、 IO调度算法;
6、 网络参数;
7、 work_mem