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

一、 参数调整1、块I/O参数1)参数描述此参数用来设置块设备参数。2)现参数：现没有设置块I/O参数。3)加入参数：#vi/etc/rc.d/rc.localblockdev--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= 256960net.core.rmem_max= 256960net.core.wmem_default= 256960net.core.wmem_max= 256960net.ipv4.tcp_timestamps= 0net.ipv4.tcp_sack= 0net.ipv4.tcp_window_scaling= 1然后重新启动网络守护程序/etc/rc.d/init.d/networkrestart。4、系统参数(已完成)1、参数描绘cat/etc/security/limits.conf2、现参数：* soft nofile65536* hard nofile65536* soft nproc131072* hard nproc1310725、GP相关参数#vi/etc/sysctl.confkernel.sem =250 64000 100 512kernel.shmmax =500000000kernel.shmmni =4096kernel.shmall =4000000000kernel.sem =250 64000 100 512kernel.sysrq =1kernel.core_uses_pid= 1kernel.msgmnb =65536kernel.msgmax =65536net.ipv4.tcp_syncookies= 1net.ipv4.ip_forward= 0net.ipv4.conf.default.accept_source_route= 0net.ipv4.tcp_tw_recycle=1net.ipv4.tcp_max_syn_backlog=4096net.ipv4.conf.all.arp_filter= 1net.core.netdev_max_backlog=10000net.ipv4.ip_local_port_range= 1025 655351、kernel.shmmax = 7516192768 （8G，取物理内存16G的50%）2、kernel.shmall = 4000000000（物理内存16G时相应大小）3、kernel.sem = 250 64000 100 10246、GP参数注：segment host上是4个primary instance，4个mirror instance.cat/gpmaster/gp-1/postgresql.conf1、shared_buffers(local, max_connections*16K)shared_buffers= 1600MB # master、standbyshared_buffers= 200MB # segment2、work_mem（，global,物理内存的2%-4%）work_mem =640MB # master、standby3、effective_cache_size（master节点，设为物理内存的85%，15032385536）effective_cache_size= 9600MB4、mainteance_work_mem（global，CREATEINDEX, VACUUM等时用到）maintenance_work_mem= 800MB5、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、 VACUUMVACUUM [ FULL |FREEZE ] [ VERBOSE ] [ table ]VACUUM [ FULL |FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]3、 AnalyzeANALYZE [ table [ (column [, ...] ) ] ]三、 按列存储优化（以kn_weblog_detail为例）1、 建表：create tabletemp_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 INDEXgoods_id _bmp_idx ON temp_huagai_weblog_Detail USING bitmap (goods_id);四、 性能评估1、 I/O测试sqlselect count(1)from kn_webloG_all_detail where visit_date >= '2011-11-01' and visit_date2、 CPU测试sqlSELECT 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_pageFROM temp_huagai_weblog_Detail Aleft join(select a.id,B.PROVINCE,B.CITY from temp_huagai_weblog_Detail aINNER JOIN ODS_IP B ON A.LOG_IP >= B.HOST_FROM WHERE A.LOG_IP) B on A.ID = B.ID;3、评估方法gpcheckperf -fall_file -d /dbfast1 -d dw采用gpcheckperf，统计性能数据，完成评估报告。五、 问题1、 Sql2、insert into OL_MONITORING_HOUR_FACTselect 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_visitfrom kn_weblog_Detail_hourwhere visit_date >= '2011-11-11' and visit_dategroup by visit_day,hh24order by 1,2)t1left join(SELECTTO_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_amtFROM ods_shop101_tbl_goods_orderWHERE to_timestamp(add_time) >= '2011-11-11' AND to_timestamp(add_time)GROUP BY TO_CHAR(to_timestamp(add_time),'YYYYMMDD') ,EXTRACT(HOUR FROMto_timestamp(add_time))ORDER BY 1,2)t2 on t1.visit_day = t2.visit_day and t1.hh24 = t2.hh24order by 1,2六、 测试结果序号参数取值调整范围结论1kernel.shmmax物理内存50%master对GP无明显影响2 小于物理内存25%segment过大,GP无法启动3kernel.shmall物理内存Master/segment对GP无明显影响4kernel.sem1024Master/segment对GP无明显影响5块参数16384Master/segment对GP无明显影响6IO调度算法 Master/segment对GP无明显影响7网络参数 Master/segment对GP无明显影响8max_connections200masterGP启动正常9 1200segmentGP启动正常10max_prepared_transactions300Master/segmentGP启动正常11shared_buffers400MBmasterGP启动正常12work_mem160MBglobalGP启动正常13effective_cache_size500MBmaster对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