greenplum数据库优化方案（实验）

一、 参数调整

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