针对大数据量环境下分析型应用的支持方案

DTCC2011DM针对大数据量环境下分析型应用的支持方案大纲DTCC2011·一个实际案例·挑战和解决方案·下一步工作规划DTCC2011一个实际案例案例简介DTCC2011·海量数据·基于已有硬件投资–单服务器节点–操作库和分析库合并·以查询分析为主，兼顾少量数据维护硬件与拓扑DTCC2011应用服务千兆交换机器P550数据汇总数据清洗与入数据库Cpux4文本库服务器Mem32GB数据P550源Cpux4文本ExcelMem32GB文本数据源源16X1TBSASRAID5案例简介-数据DTCC2011·以常规数据为主，主要为数值、字符串、时间类型·日增长数据量为约56G，3亿条元组·当前数据量3TB·最大单表为计费表，目前约150亿条记录·数据保存20年后归档为历史数据·在线数据规模将超过400TB典型业务流程DTCC2011–源数据清洗入库–分析统计型查询·第一步过滤的筛选条件不确定·试错式的查询分析过程，成功后固化，一般包含20多个步骤精品文档放心下载·大规模的连接查询、子查询、联合查询、数据分组与排序、临精品文档放心下载时结果集与临时表等·复杂SQL不多，但IO非常大–日常数据维护·手工修改记录内容·批量删除·定期维护案例需求DTCC2011·关键在查询性能–第一个过滤步骤·筛选字段由用户随机定义，因此无法使用索引·一般会得到千万级别的结果集–大量的多表连接查询·数据装载性能·初始入库48亿条，近1T：限48小时，相当于3万条/s谢谢阅读·后续每3天入库一次，9亿条，168G，限10小时内完成感谢阅读DTCC2011挑战-核心是性能·

······优化器比较简陋

只支虚拟机实现不尽合理

持行物理存储设计有待优化

式存日志系统过于复杂

储不能充分利用多机资源提升性能

查询数据分片技术不完善于2009年开始新一代产品DM7的研制DTCC2011实验室原型持续的技术积累5DM7技术积累阶段5.6引入物理操作符,虚拟机2011实现各类标准6.0引入高级特性和oracle兼容特性谢谢阅读稳定性及功能43DM5.6DM6对DM4-DM6的技与开源系术总结统有差距2009融合列存储与行存储基于向量数据的2200420071DM4DM1-DM3执行内核原生的MVCCOLAP应用的支持感谢阅读1988-2003对于性能的理解DTCC2011应用系统的I/O效率优化器设计表达式计算综合性能数据/控制权传递并发/并行数据控制权传递-批量技术DTCC2011–向量数据处理–在数据泵一次传送一批数据–减少控制转移的CPU损耗；–有利于批量的表达式计算传统的数据传递PROJECT一次只传递一条记录FILTER每个操作符一次只处理一行记录111…控制权需要反复传递SCAN向量式的数据传递PROJECT减少控制权限的反复传递提升CPU的有效利用率FILTER便于表达式批量计算SCAN批量技术-数据入库DTCC2011–将系统的初始数据入库–原有BCP接口达到5000条/s，仍无法满足要求精品文档放心下载–改进：·在服务器端实现批量，减少执行流程中的控制跳转·效率提升８倍批量技术-全表更新DTCC2011普通普通批量计划生成单趟扫描一个ID进行绑定更新，执行20万次生成特定计批量划，减少执针对大表更ID进行排序，单趟扫描20行流程新的特定的万个ID并进行更新批量绑定消息性能提升100倍以上，控制在2秒以内批量技术-LIKE谓词DTCC2011·selectcount(*)fromorderswhere谢谢阅读o_mentnotlike'%special%requests%‘DBMS‘O’11g:3.3DBMS‘S’2005:100.4orders:1,500,000记录cpu2.2G,多次执行DTCC2011·一个表达式出现多次–Selectsum(2*c1),sum(3*(2*c1))fromt谢谢阅读·只计算一次，结果缓存–v1=2*c1；–Selectsum(v1),sum(3*v1)fromt谢谢阅读·类似思路：中间结果重用–一个复杂查询在一条sql语句中使用多次的情况–将复杂查询提取，并将结果缓存，多次使用批量表达式计算for(i=0;i<n;i++){r=(int64)opr1[i]+opr2;if(r!=(int)r)returnEC_DATA_OVERFLOW;}res[i]=(int)r;···计算一利用CPU的批数据CACHE一次利用CPU的SIMD特性··接近于C的效率·比一次一行模式快10-100倍以上批量尺寸对性能的影响·SF=1,TPCHQ1·BDTA_SIZE:可配置的批量大小参数·增大BDTA_SIZE可以有效的提高执行效率DTCC2011TPCHDM7DBMS‘O’11PGSQL8.3DBMS‘S’2005Q11.3149.0916.0112.87Q20.160.0460.190.14Q30.8621.619.302.78Q40.989.030.800.68Q51.49.054.611.58Q60.7892.720.96Q71.6111.7319.542.35Q82.30.282.972.01Q931.6118.015.45Q101.369.165.832.23Q110.1944.670.550.46优化器-分析器流程DTCC2011SQL脚本语法分析语法树语义分析SFW结构关系代数变换关系树代价优化优化了的关系树物理计划生成执行计划智能优化器·增加高度直方图的桶数·基于多趟分析的代价优化器·语义分析、代价优化过程分离·灵活的计划变换控制·基于时间单位(ms)的代价计算·解决统计信息的使用性问题·增加频率直方图DTCC2011查询优化：关系变换DTCC2011·SFW结构转换为关系树Select:ID,name精品文档放心下载From:TSFW结构–投影(PROJECT)–连接(JOIN)–半连接(SEMIJOIN)–选择(SELECT)–基本表(BASETABLE)(ID,name)Where:ID=10

SELECTPROJECT(ID=10)关系树BASE_TABLE(T)·消除子查询，“平坦”的关系树·子查询一律转化为半连接（SEMIJOIN）例：selectfromT1wheret1.idin(selectIDfromT2)感谢阅读PROJECTSEMIJOINT1T2·考虑三个因素·A.确定的连接次序·B.确定的卡特兰2叉树形状·C.是否下放过滤条件·采用临时结果减少重复计算·代价模型基本覆盖所有情况·对连接表的个数非常多的情况,特殊处理查询优化：统计信息DTCC2011·记录数据分布情况，用于精确行数估计，特别是数据分布不规则的情况，对基数及代价计算有重大影响·频率直方图：不同值较少感谢阅读·等高直方图：不同值较多40504000400239904032398039503950396039003850

38883800DTCC2011·列存储:–数据按列存储–结合自适应压缩技术–与批量计算技术紧密结合·列存储优缺点–大幅提升扫描性能–适合批量装载与删除–不适合频繁的插入、删除和更新·融合列存储和行存储–提供按列存储选项–结合分区技术–同时适应OLAP和OLTP应用需求I/O效率·支持垂直分区和水平分区·行存储优化–简化物理记录格式–字段物理次序与逻辑次序分离·多buffer类型–常驻内存和常规方式淘汰–用户可以指定·批量读：预处理DTCC2011提高并发度DTCC2011·支持并行插入的物理数据存储·并行备份和恢复·分区技术及相应的并行查询操作符号典型场景一：大结果集DTCC2011·场景描述–某表T，31个字段，48亿条记录–随机基于某字段筛选：SELECT*FROMTWHERE谢谢阅读FLD1=753–查询符合条件的结果集达到千万条记录·分析–––SQL语句非常简单，没有更优的等效语句–结果集筛选条件不确定，无法使用索引服务器内存为32G，在扫描的过程中必然出现页面淘汰感谢阅读由于基础数据量大，因此即使命中率不高（0.2%），也会生成960万条记录的结果集典型场景一：大结果集DTCC2011从3个方向入手，提升全表扫描的IO效率·批量技术·降低结果集处理的时间消耗·调整数据页读取策略典型场景一：大结果集DTCC2011·返回结果集策略改进–优化前·根据通信块大小决定结果集分批次返回的数量·第一批结果集返回后，自动完成后续结果集获取和返回–优化后·由应用设定第一批结果集的大小和返回的时机·当返回第一批结果集后，工作线程暂停SQL查询请求，直到下谢谢阅读一批结果集请求到来或开始新事务–效果·快速返回部分结果集，提高用户体验·避免自动返回所有结果集，降低服务器资源消耗典型场景一：大结果集DTCC2011·调整数据读取策略–数据页（page）是数据读写的单位–优化前的全表扫描：按页读取，每次IO只扫描一个页–优化后：一次扫描多个页，减少IO数量–测试：经过优化后，磁盘的吞吐量提升1倍典型场景二：大表连接DTCC2011·场景描述–表T1，31个字段，5000W条记录，数据类型包括int、感谢阅读varchar、datetime、Dec；表T2,15个字段，500W条记录，精品文档放心下载数据类型包括varchar、datetime、Dec；谢谢阅读–SELECTT1.NAME,T2.TITLEFROM谢谢阅读T1,T2WHERET1.PERSONID=T2.PERSONIDANDT1.SEX='M';谢谢阅读–连接查询字段由最终用户临时指定，表上未建索引–结果集不大，但查询表数据量大，连接查询响应时间陡增感谢阅读典型场景二：大表连接DTCC2011·分析·行存储特性：连接查询所连接的字段在数据页中的存储非连续，进行连接查询，需将所有数据页读到内存，IO消耗巨大；·连接匹配时，要对读入缓存中的所有页进行扫描。·行存储：–连接列分散在每个数据页中Cn+1页1…Cn+1页NC1C2…CnC1…Cm…………典型场景二：大表连接DTCC2011·优化方向：列存储–按字段存储–连接列被集中存储Cn+1Cn+1…Cn+1页N页1–读入缓存中的数据页明显减少，系统IO下降……Cm…………典型场景二：大表连接DTCC2011·优化方向：存储压缩–适用于列存储模式的压缩算法–初步压缩结果：·采用本案例数据进行测试·Float54%（压缩后大小/压缩前大小）·Double33%·Dec52%·字符56%典型场景二：大表连接DTCC2011·优化效果从17小时降至10分钟以内·场景描述–表T,15个字段，500W条记录，数据类型包括int、谢谢阅读varchar、datetime、Dec；–根据T进行查询建表：CREATETABLETTasSELECT*FROM精品文档放心下载T;·分析–大表进行查询建表时，需经过以下五个步骤初始全表生成插入事务化目扫描结果结果提交标表集–这个过程中可优化的操作有：查询与结果集的生成和大量数据的插入操作·直接B树操作–避免结果集处理与数据插入–优化前耗时约35S操作–优化后耗时约4S，–直接复制根节点和叶子是在性能提升内存中进行操作，速度更快9倍·优化效果对案例中的T进行建表查询装载表数据到内存源表B树扫描复制B树·场景描述–针对500万条记录的表进行如下查询–SELECTIDnum,sub(6,8,IDnum)as生日,(now()-精品文档放心下载sub(6,8,IDnum))as年龄from…感谢阅读·问题分析·表达式sub(6,8,IDnum)可重用·改进优化：–一个表达式出现多次，只计算一次–本例中性能提升70%。其他场景性能提升程度取决于计算表达式的复杂度与数据量·场景描述–同结构的表T1~T10，每张表500万条记录，需要将10精品文档放心下载张表的所有数据合并到一个临时表Ttmp中–INSERTINTOTtmpSELECT*FROMT1感谢阅读–INSERTINTOTtmpSELECT*FROMT2。。。精品文档放心下载–应用的并行化并没有带来较大的提升–分析–Ttmp成为瓶颈：原有的逻辑Rowid成为资源瓶颈精品文档放心下载–逻辑Rowid：不代表物理存储位置，更新、插入、重组精品文档放心下载等操作代价降低，但Rowid需要通过临界资源获取谢谢阅读–原有产品针对OLTP业务场景，OLTP事务以分散、短精品文档放心下载小事务为主，原有的RowID机制不会成为突出瓶颈谢谢阅读·改进–物理RowID：代表记录的物理存储位置–多个工作线程进行插入操作，无需进入临界资源获取rowid，每个工作线程自行生成RowID感谢阅读–实现真正意义上的并发插入应用优化DTCC2011·好的性能需要应用与数据库的配合实现–应用架构设计应站在系统全局考虑性能问题–应用与数据库应该取长补短·数据存储–基于分区表进行数据划分·应用的并行化–复杂事务分解为多个可并行的简单事务应用优化-手段DTCC2011·保存第一步过滤结果集·利用视图减少中间结果集的保存·数据按月份分区·TOP查询减少不必要的全结果集应用优化-大表的全表扫描DTCC2011·典型场景–5000万无索引TOP查询：SELECT*FROMT6谢谢阅读WHERENAMELIKE‘张三’–优化前：数据库服务器CPU满载而应用服务器没有精品文档放心下载负载–在最坏情况下，将需要扫描整个表·分析：–系统设计需要站在全局角度，充分考虑应用、中间件、数据库之间的负载分配–充分利用已有的硬件应用优化-大表的全表扫描DTCC2011·改进：·数据进行分表和分区·DM已实现的分区表并行查询操作符，提供了分区表优化的支持·应用依据分表更改查询模块，从单线程改为多线程·在应用服务器将各分表的查询结果合并·效果：·按最坏情况测试，查询时间由原来的不可预期，提升到2分钟内应用优化-数据清洗与入库DTCC2011–最初方式：–基于JDBC驱动的数据迁移工具进行清洗和入库操作–批量绑定–迁移工具的资源消耗随着迁移时间的持续增加，导致迁移速度在运行3天后急剧下降–初始数据（1T）入库时间达到1个月，相当于400条/s应用优化-数据清洗与入库DTCC2011–问题分析：–超过100亿条记录，即使每5000条提交一次，精品文档放心下载也有2百万次的解析-计划-代价-执行流程–大量的数据库redo与undo日志操作–解决方案–利用批量＋ＢＣＰ–利用并行化充分发挥多CPU处理能力，增加IO感谢阅读的吞吐量–JDBC方式转变为JNI+ODBC–实现动态编译型的ETL脚本引擎DTCC2011图DMETL内嵌BCPDTCC2011应用优化-BCPDTCC2011·将清洗与入库分离·并行化清洗和装载入库·入库应用BCP方式·通过批量绑定减少了网络开销·服务器内部为BCP专门实现了”bcp_fast_insert”方法谢谢阅读·绕过SQL处理流程，直接操作B树叶子节点·不进行Redo与Undo·不进行约束检查·对原有BCP也进行了服务器端的批量化处理最终效果：性能提升100倍，能够在8小时内完成感谢阅读海量数据备份的难题DTCC2011·备份的效率问题–整库备份操作耗时太长·备份粒度问题–需要灵活的针对整库、文