CPIC-Greenplum调优汇总.docx

Greenplum数据库调优报告 中国太平洋保险Greenplum数据库调优总结易安信电脑系统（中国）有限公司2013年03月20日第 27 页文档信息项目名称：Greenplum调优报告文档版本号：1.0文档作者：龙国荣生成日期：2013/03/20文档审核者：审核日期：文档维护记录版本号维护日期作者/维护人描述1.02013/03/20龙国荣创建版权说明本文件中出现的任何文字叙述、文档格式、插图、照片、方法、过程等内容，除另有特别注明，版权均属EMC公司所有，受到有关产权及版权法保护。任何个人、机构未经EMC公司的书面授权许可，不得复制、引用或传播本文件的任何片断，无论通过电子形式或非电子形式。目 录第一章调优需求4第二章调优时间安排5第三章调优过程63.1Greenplum执行计划分析63.1.1在不同时期执行计划比较73.1.2差异原因分析93.1.3调优原则103.2分布健选择策略103.2.1调优前效率103.2.2调优后效率103.2.3调优SQL103.2.4效率分析123.2.5调优方案153.2.6调优原则153.3索引、主健约束163.3.1调优前效率163.3.2调优后效率163.3.3调优SQL163.3.4效率分析163.3.5调优方案163.3.6调优原则173.4分区表规划173.4.1调优前效率173.4.2调优后效率173.4.3调优SQL173.4.4效率分析193.4.5调优方案193.4.6调优原则203.5关联查询效率优化203.5.1调优SQL203.5.2效率分析213.5.3调优方案223.5.4调优原则23第四章后续优化建议244.1硬件问题244.2磁盘空间回收244.3分布健调整254.4分区表建立25第一章 调优需求中国太保EDW平台部分新应用上线后，出现系统性能下降和部分function执行缓慢，需要对系统进行检查和调优。环境现状如下：2+12的GP集群环境，已保存28TB的生产数据，基本上没有使用压缩表和分区表。生产情况：在有2-3个任务并行的情况下，数据库相应非常缓慢，部分function在晚上有并发的时候执行，会很慢，但在白天单任务执行的时候会快很多。第二章 调优时间安排本次调优从2月18号进场开始，分成三个阶段，每阶段现场服务、调优一周。第一阶段：2月18号-2月22号，调优过程中发现GP环境中129节点磁盘读写性能很差，同一RAID卡上一个数据目录读写性能只有100+MB/S，另一磁盘目录有300+MB/S。经查是磁盘柜头硬件故障，21号晚更换硬件后，129节点的磁盘读写性能都能达到900MB/S左右。第二阶段：3月4号-3月8号，针对MIS应用进行调优，发现几类共性的问题。第三阶段：3月18号-3月22号，临时性的问题解决处理，调优问题总结和培训。第三章 调优过程本次现场服务，除了解决129节点的硬件问题外，还解决了寿险、产险等MIS模块的一些典型的SQL效率问题，具体如下：3.1 Greenplum执行计划分析explain select 200905, coalesce(A.book_set_id, ), coalesce(b.book_type, ),- 12月份数据book_type有三种状态：“1”，调整2 “2” 用一月份的期初数当12月份的期末数（因为期初数与期末数不平） “3”调整前 coalesce(B.branch_company_code, ), coalesce(B.cur_code, ), coalesce(b.main_product_code, ), coalesce(A.index_id, ), coalesce(a.INDEX_NAME, ), coalesce(a.F_INDEX_ID, ), coalesce(a.INDEX_LEVEL, ), sum( B.PERIOD_NET_CR+B.BEGIN_BALANCE_CR-B.PERIOD_NET_DR-B.BEGIN_BALANCE_DR) -本期借款 +期初借款-本期贷款-期初贷款 from T_FACT_FIN_AL_CONFIG A LEFT JOIN f_bb_fin_account_balances_mid B ON b.index_id between A.BEGIN_SUBJECT and A.END_SUBJECT WHERE a.index_level = 4 and b.date_stamp = 200905 AND A.BOOK_SET_ID=B.book_set_id GROUP BY A.D_OR_C, coalesce(A.book_set_id, ), coalesce(b.book_type, ), coalesce(B.branch_company_code, ), coalesce(B.cur_code, ), coalesce(b.main_product_code, ), coalesce(A.index_id, ), coalesce(a.INDEX_NAME, ), coalesce(a.F_INDEX_ID, ), coalesce(a.INDEX_LEVEL, );3.1.1 在不同时期执行计划比较f_bb_fin_account_balances_mid数据有几百万，t_fact_fin_al_config只有几千条数据Vacuum analyze 前Vacuum analyze 后-最初的执行计划 Gather Motion 48:1 (slice3; segments: 48) (cost=1658.53.1659.14 rows=2 width=770) - HashAggregate (cost=1658.53.1659.14 rows=2 width=770) Group By: a.d_or_c, ?column2?, ?column3?, ?column4?, ?column5?, ?column6?, ?column7?, ?column8?, ?column9?, ?column10? - Redistribute Motion 48:48 (slice2; segments: 48) (cost=1654.73.1656.69 rows=2 width=738) Hash Key: a.d_or_c, unnamed_attr_2, unnamed_attr_3, unnamed_attr_4, unnamed_attr_5, unnamed_attr_6, unnamed_attr_7, unnamed_attr_8, unnamed_attr_9, unnamed_attr_10 - HashAggregate (cost=1654.73.1655.71 rows=2 width=738) Group By: a.d_or_c, COALESCE(a.book_set_id, :character varying), COALESCE(b.book_type, :character varying), COALESCE(b.branch_company_code, :character varying), COALESCE(b.cur_code, :character varying), COALESCE(b.main_product_code, :character varying), COALESCE(a.index_id, :character varying), COALESCE(a.index_name, :character varying), COALESCE(a.f_index_id, :character varying), COALESCE(a.index_level, :character varying) - Hash Join (cost=1390.99.1653.24 rows=2 width=95) Hash Cond: a.book_set_id:text = b.book_set_id:text Join Filter: b.index_id:text = a.begin_subject:text AND b.index_id:text Seq Scan on t_fact_fin_al_config a (cost=0.00.81.39 rows=41 width=71) Filter: index_level:text = 4:text - Hash (cost=1390.39.1390.39 rows=1 width=60) - Broadcast Motion 48:48 (slice1; segments: 48) (cost=0.00.1390.39 rows=1 width=60) - Seq Scan on f_bb_fin_account_balances_mid b (cost=0.00.1389.90 rows=1 width=60) Filter: date_stamp:text = 200905:text-analyze后的执行计划Gather Motion 48:1 (slice3; segments: 48) (cost=684247810.79.696551807.37 rows=3778126 width=770) - HashAggregate (cost=684247810.79.696551807.37 rows=3778126 width=770) Group By: a.d_or_c, ?column2?, ?column3?, ?column4?, ?column5?, ?column6?, ?column7?, ?column8?, ?column9?, ?column10? - Redistribute Motion 48:48 (slice2; segments: 48) (cost=660030999.10.667410063.76 rows=3778126 width=738) Hash Key: a.d_or_c, unnamed_attr_2, unnamed_attr_3, unnamed_attr_4, unnamed_attr_5, unnamed_attr_6, unnamed_attr_7, unnamed_attr_8, unnamed_attr_9, unnamed_attr_10 - HashAggregate (cost=660030999.10.663783063.40 rows=3778126 width=738) Group By: a.d_or_c, COALESCE(a.book_set_id, :character varying), COALESCE(b.book_type, :character varying), COALESCE(b.branch_company_code, :character varying), COALESCE(b.cur_code, :character varying), COALESCE(b.main_product_code, :character varying), COALESCE(a.index_id, :character varying), COALESCE(a.index_name, :character varying), COALESCE(a.f_index_id, :character varying), COALESCE(a.index_level, :character varying) - Hash Join (cost=2212.44.654493592.29 rows=4195006 width=95) Hash Cond: b.book_set_id:text = a.book_set_id:text Join Filter: b.index_id:text = a.begin_subject:text AND b.index_id:text Seq Scan on f_bb_fin_account_balances_mid b (cost=0.00.61315.68 rows=77158 width=61) Filter: date_stamp:text = 200905:text - Hash (cost=1039.44.1039.44 rows=1956 width=70) - Broadcast Motion 48:48 (slice1; segments: 48) (cost=0.00.1039.44 rows=1956 width=70) - Seq Scan on t_fact_fin_al_config a (cost=0.00.81.49 rows=41 width=70) Filter: index_level:text = 4:text3.1.2 差异原因分析GP的SQL解析、优化器会自动根据关联的库表的统计信息选择库表关联顺序、Broadcast库表操作等。库表的统计系统只有执行analyze tablename;之后才会更新。上述案例中：同一条SQL，简单的进行关联，在对f_bb_fin_account_balances_mid表做analyze之前。数据库根据统计信息将f_bb_fin_account_balances_mid表做重分布，实际上这张表有几百万行记录，与之关联的t_fact_fin_al_config是张小表，只有2千多行记录。这是长期没有更新库表统计信息导致GP数据库SQL解释器选择了错误执行路径的典型案例。3.1.3 调优原则对于执行效率差的SQL，建议先对其涉及的库表做一次vacuum analyze 操作，得到最新的统计信息后，数据库所选择的执行计划才是最优执行计划。3.2 分布健选择策略mis_app.sp_mis_gen_t_fact_gen_fee_insured（）3.2.1 调优前效率3545.7秒3.2.2 调优后效率1059.1秒3.2.3 调优SQLSELECT *FROM P_FIN_PAYMENT_DUE_IN P1 LEFT JOIN P_FIN_PAYMENT_DUE_IN P2 ON P1.BRANCH_COMPANY_CODE=P2.BRANCH_COMPANY_CODE AND P1.POLICYNO=P2.POLICYNO AND P1.ENDORSENO=P2.ENDORSENO AND P1.PRODUCT_CODE=P2.PRODUCT_CODE AND P1.SEQUENCE_NUM=P2.SEQUENCE_NUM AND P2.PAYMENT_DUE_TYPE=2 LEFT JOIN P_AGT_INDIVIDUAL_AGMT P3 ON P1.BRANCH_COMPANY_CODE=P3.BRANCH_COMPANY_CODE AND P1.POLICYNO=P3.POLICYNO LEFT JOIN (SELECT BRANCH_COMPANY_CODE ,POLICYNO ,ENDORSENO FROM P_FIN_PAYMENT_DUE_IN WHERE DUE_DATEV_BEGIN_YEAR GROUP BY BRANCH_COMPANY_CODE ,POLICYNO ,ENDORSENO ) P4 ON P1.BRANCH_COMPANY_CODE=P4.BRANCH_COMPANY_CODE AND P1.POLICYNO=P4.POLICYNO AND P1.ENDORSENO=P4.ENDORSENO WHERE P1.DUE_DATE Hash Left Join (cost=640104379.00.1033934120.76 rows=9354049 width=3441) Hash Cond: p1.branch_company_code:text = p2.branch_company_code:text AND p1.policyno:text = p2.policyno:text AND p1.endorseno:text = p2.endorseno:text AND duct_code:text = duct_code:text AND p1.sequence_num = p2.sequence_num - Hash Left Join (cost=325059817.06.592357295.91 rows=9354049 width=3441) Hash Cond: p1.branch_company_code:text = p4.branch_company_code:text AND p1.policyno:text = p4.policyno:text AND p1.endorseno:text = p4.endorseno:text - Hash Left Join (cost=210358434.32.343080283.24 rows=9354049 width=3441) Hash Cond: p1.branch_company_code:text = p3.branch_company_code:text AND p1.policyno:text = p3.policyno:text - Seq Scan on p_fin_payment_due_in p1 (cost=0.00.13547434.00 rows=9354049 width=3441) Filter: due_date Hash (cost=83288889.00.83288889.00 rows=158434656 width=28) - Broadcast Motion 48:48 (slice1; segments: 48) (cost=0.00.83288889.00 rows=158434656 width=28) - Seq Scan on p_agt_individual_agmt p3 (cost=0.00.5655907.56 rows=3300722 width=28) - Hash (cost=45415945.86.45415945.86 rows=44899432 width=456) - Broadcast Motion 48:48 (slice3; segments: 48) (cost=21792662.19.45415945.86 rows=44899432 width=456) - HashAggregate (cost=21792662.19.22966229.86 rows=935405 width=456) Group By: p_fin_payment_due_in.branch_company_code, p_fin_payment_due_in.policyno, p_fin_payment_due_in.endorseno - Redistribute Motion 48:48 (slice2; segments: 48) (cost=16914891.39.20282348.78 rows=935405 width=456) Hash Key: p_fin_payment_due_in.branch_company_code, p_fin_payment_due_in.policyno, p_fin_payment_due_in.endorseno - HashAggregate (cost=16914891.39.19384360.14 rows=935405 width=456) Group By: p_fin_payment_due_in.branch_company_code, p_fin_payment_due_in.policyno, p_fin_payment_due_in.endorseno - Seq Scan on p_fin_payment_due_in (cost=0.00.13547434.00 rows=9354049 width=32) Filter: due_date Hash (cost=101109481.76.101109481.76 rows=178698057 width=50) - Broadcast Motion 48:48 (slice4; segments: 48) (cost=0.00.101109481.76 rows=178698057 width=50) - Seq Scan on p_fin_payment_due_in p2 (cost=0.00.13547434.00 rows=3722877 width=50) Filter: payment_due_type:text = 2:text-中间会有多次 Redistribute Motion和broadcast motion操作，结果集比较大，每个月会生成几千万结果集，连续重分布数据非常浪费时间。3.2.5 调优方案五张需要关联的主要库表都是用一个顺序字段分布，结果集存储的目标表使用保单号、险种分布，需要进行数据重分布，而且重分布时间会比较长。涉及的库表名为：atom.P_FIN_PAYMENT_DUE_IN、atom.P_AGT_INDIVIDUAL_AGMT、atom.P_AGT_PMP_COVERAGE_PREMIUM、atom.P_AGT_CHANGE_REQ、atom.P_AGT_PMP_PREMIUM与EDW同事协商后，将这五张表的分布健统一修改成POLICYNO，调优后，重新执行相同月份的数据，效率提升3/2。3.2.6 调优原则简单关联查询SQL，查询计划通过3.1步骤已经选择了最优路径，此时，分析SQL关联库表的分布方式，往往能带来比较大的性能提升。Greenplum分布健选择主要原则：1. 要求能均匀的分布数据；在此条件满足后适当考虑后面的其他条件；2. 最好选择库表未来查询时需要经常使用的字段；3. 单字段能分布均匀的，就不要选择多字段。3.3 索引、主健约束mis_app. sp_mis_gen_t_fact_gen_policy_sum()3.3.1 调优前效率该函数所使用的库表与3.2章节所使用的库表一致，3.2章节已经对本function所需的5张库表已经统一了分布健，已经进行了一次调优。没有具体的调优前数据，但以前执行该function时间会很长。3.3.2 调优后效率1237.0秒3.3.3 调优SQL3.3.4 效率分析Function处理过程中，分三次执行类似的SQL，将数据准备到mis_app. T_FACT_GEN_POLICY_SUM_TMP1库表，该库表使用随机分布。最后根据条件将结果插入目标表mis_app. mis_app. T_FACT_GEN_POLICY_SUM，该库表定义联合主键（PK），虽能校验非法数据，但会损失function处理效率。3.3.5 调优方案已完成的调优：对该function所使用的5张atom库表统一分布健，减少处理过程中的数据重分布。但由于中间处理临时表与目标表的主键还不一致，仍可继续调优。后续调优建议：1. mis_app. T_FACT_GEN_POLICY_SUM表的主键建议去掉，结果集一旦很大的时候，有PK的库表insert操作会比较慢；2. 修改mis_app. T_FACT_GEN_POLICY_SUM_TMP1的分布健，将其改成与mis_app. T_FACT_GEN_POLICY_SUM表一致；3. Atom模式下的P_AGT_INDIVIDUAL_AGMT、P_FIN_PAYMENT_DUE_IN、P_AGT_PMP_PREMIUM库表建议按月建分区表，对MIS的应用会有很大帮助，否则，这些Function还是会随着数据量增多越跑越慢；3.3.6 调优原则对于结果集很大的库表，比如：千万级的结果，插入目标表时如果有主健约束（PK），则目标表的insert操作会比较慢，在业务逻辑允许的情况下，建议删除目标表的PK。3.4 分区表规划mis_app. sp_mis_life_t_cir_fanal_zjb_perinsur_mx_new ()3.4.1 调优前效率30分钟3.4.2 调优后效率15分钟3.4.3 调优SQLInsert Into T_CIR_FANAL_ZJB_PERINSUR_MX_temp_20130219(department_code,Periodno, Midcode, sale_channel, Dataattr, Itemno, Val, Empno, Orgcode3)Select a.department_code, Substr(p_Date_Into, 1, 6) As Periodno, Trim(b.Midcode) As Midcode, Trim(a.sale_channel) As sale_channel, Case When Handmade_flag In (2, 3) Then1 Else0 End As Dataattr, Trim(c.Itemno) As Itemno, Case When Substr(Itemno, 8, 1) = 1 ThenCoalesce(a.Xbqj, 0) When Substr(Itemno, 8, 1) = 2 ThenCoalesce(a.Xqtwo, 0) When Substr(Itemno, 8, 1) = 3 ThenCoalesce(a.Xqthree, 0) When Substr(Itemno, 8, 1) = 4 ThenCoalesce(a.Xqmore, 0) End, Trim(a.Empno) As Empno, Trim(a.Orgcode3) As Orgcode3From T_CIR_SUM_POLICY_PREMIUM aInner Join T_CIR_SUM_DEFINED_RISKLISt b On a.Classcode = b.Classcode And b.Deptno = a.department_code and a.midcode = b.midcodeInner Join T_CIR_SUM_DEFINED_ITEMDEF c On a.PAY_FREQUENCY = c.Paycode And a.PAY_FREQUENCY In (10, 11, 12, 13) And(Case When a.Yearnum 9999 Then 9999 Else a.Yearnum End) Betweenc.Byearnum And c.Eyearnum And (Case When a.Year 9999 Then 9999 Else a.Year End) Between c.Byear And c.Eyear3.4.4 效率分析该SQL执行过程中不涉及数据重分布和结果表PK的问题。关联SQL的逻辑也比较简单，需要从关联库表的数据规模和操作行文进行分析。做关联的库表mis_app.T_CIR_SUM_POLICY_PREMIUM有4亿多条记录，每次处理都只取某个时间段的数据。对于关联查询所涉及的大型库表，可以考虑根据操作行为对库表建一些分区，每次查询只取部分分区的数据，通过缩小访问数据集的方式达到调优的目标。3.4.5 调优方案对mis_app.T_CIR_SUM_POLICY_PREMIUM库表根据month_id建分区，每个月一个分区：create table t_lif_agmt_fee_base_mon_20130304 ( like t_lif_agmt_fee_base_mon )distributed by( policy_no, classcode )partition by LIST(month_id)( PARTITION p201201 VALUES(201201), PARTITION p201202 VALUES(201202), PARTITION p201203 VALUES(201203), PARTITION p201204 VALUES(201204), PARTITION p201205 VALUES(201205), PARTITION p201206 VALUES(201206), PARTITION p201207 VALUES(201207), PARTITION p201208 VALUES(201208), PARTITION p201209 VALUES(201209), PARTITION p201210 VALUES(201210), PARTITION p201211 VALUES(201211), PARTITION p201212 VALUES(201212),PARTITION p201301 VALUES(201301), PARTITION p201302 VALUES(201302),）;3.4.6 调优原则如果做关联的左表数据规模超过千万级，且随着时间推移每个月都会继续增长，可考虑对左表进行重建，建成分区表。每次使用该表时只访问其中的部分子分区，达到整体SQL性能提升的目标。mis_app. sp_mis_t_fact_lif_premium_m()函数与该函数类似，将关联的左表改成分区表，执行时间从40分钟缩短到20分钟。3.5 关联查询效率优化Mis_app.sp_mis_life_t_cir_fanal_zjb_perinsur_mx_new()3.5.1 调优SQLInsert Into T_CIR_FANAL_ZJB_PERINSUR_MX_temp_20130219(department_code,Periodno, Midcode, sale_channel, Dataattr, Itemno, Val, Empno, Orgcode3)Select a.department_code, Substr(p_Date_Into, 1, 6) As Periodno, Trim(b.Midcode) As Midcode, Trim(a.sale_channel) As sale_channel, Case When Handmade_flag In (2,