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

DTCCDM针对大数据量环境下分析型应用支持方案大纲·一种实际案例·挑战和解决方案·下一步工作规划

DTCCDTCC一种实际案例案例简介

DTCC·海量数据·基于已有硬件投资–单服务器节点–操作库和分析库合并·以查询分析为主，兼顾少量数据维护硬件与拓扑千兆互换机

DTCC应用服务器数据汇总文本数据源文本Excel数据

数据清洗与入库

数据库服务器P550Cpux4Mem32GB

P550Cpux4Mem32GB源

源

16X1TBSASRAID5文本数据源数据文本数据源数据案例简介-数据

DTCC·以常规数据为主，重要为数值、字符串、时间类型·日增长数据量为约56G，3亿条元组·当前数据量3TB·最大单表为计费表，当前约150亿条记录·数据保存后归档为历史数据·在线数据规模将超过400TB典型业务流程

DTCC–源数据清洗入库–分析记录型查询·第一步过滤筛选条件不拟定·试错式查询分析过程，成功后固化，普通包括20各种环节·大规模连接查询、子查询、联合查询、数据分组与排序、临时成果集与暂时表等·复杂SQL不多，但IO非常大–寻常数据维护·手工修改记录内容·批量删除·定期维护案例需求

DTCC·核心在查询性能–第一种过滤环节·筛选字段由顾客随机定义，因而无法使用索引·普通会得到千万级别成果集–大量多表连接查询·数据装载性能·初始入库48亿条，近1T：限48小时，相称于3万条/s·后续每3天入库一次，9亿条，168G，限10小时内完毕DTCC挑战-核心是性能原有产品难以支持分析型应用DTCC·······

只支持行式存储查询优化器比较简陋虚拟机实现不尽合理物理存储设计有待优化日记系统过于复杂不能充分运用多机资源提高性能数据分片技术不完善于开始新一代产品DM7研制DTCC实验室原型技术积累阶段实现各类原则

持续技术积累5.6引入物理操作符,虚拟机6.0引入高档特性和oracle兼容特性

5

DM7稳定性及功能与开源系统有差距

3

DM5.6

4

DM6

对DM4-DM6技术总结融合列存储与行存储基于向量数据1DM1-DM3

2

DM4

执行内核原生MVCCOLAP应用支持1988-DM系统研制历程DM系统研制历程对于性能理解

DTCC应用系统设计表达式计算

优化器综合性能数据/控制权传递

I/O效率并发/并行数据控制权传递-批量技术DTCC–向量数据解决–在数据泵一次传送一批数据–减少控制转移CPU损耗；–有助于批量表达式计算老式数据传递PROJECTFILTER

一次只传递一条记录每个操作符一次只解决一行记录1

1

1

…

控制权需要重复传递SCANDTCCDTCC向量式数据传递PROJECT减少控制权限重复传递提高CPU有效运用率FILTER便于表达式批量计算SCAN12…N12…N…………12…N12…N…………DTCCDTCC批量技术-数据入库

DTCC–将系统初始数据入库–原有BCP接口达到5000条/s，仍无法满足规定–改进：·在服务器端实现批量，减少执行流程中控制跳转·效率提高８倍批量技术-全表更新

DTCC普通批量

普通批量绑定针对大表更新特定批量绑定消息

筹划生成生成特定计划，减少执行流程

单趟扫描一种ID进行更新，执行20万次ID进行排序，单趟扫描20万个ID并进行更新性能提高100倍以上，控制在2秒以内批量技术-LIKE谓词·selectcount(*)fromorderswhereo_commentnotlike'%special%requests%‘

DTCCDBMS‘O’11g:

3.3DBMS‘S’：10DM7:

0.4orders：1,500,000记录cpu2.2G,多次执行DTCC·一种表达式浮现多次–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;

···

一次计算一批数据运用CPUCACHE运用CPUSIMD特性·

避免老式DBMS函数重复调用代价··

接近于C效率比一次一行模式快10-100倍以上·虚拟机支持批量计算指令DTCC·虚拟机支持批量计算指令DTCC批量尺寸对性能影响·

SF=1，TPCHQ1·BDTA_SIZE：可配置批量大小参数·增大BDTA_SIZE可以有效提高执行效率DTCCDTCCDTCCTPCHDM7DBMS‘O’TPCHDM7DBMS‘O’11PGSQL8.3DBMS‘S’2005Q121.579.154.622.08Q131.734.475.1312.03Q140.718.972.801.16Q150.668.985.511.04Q160.870.334.525.41Q171.038.94>1001.80Q181.279.2122.012.90Q191.929.065.624.17Q200.789.23>1000.79Q212.248.8833.015.49Q220.240.34>1001.16TPCHTPCHDM7DBMS‘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.46TPC-H/SF=1TPC-H/SF=1对比测试(S)优化器-分析器流程

DTCCSQL脚本语法分析语法树语义分析SFW构造关系代数变换关系树代价优化优化了关系树物理筹划生成执行筹划智能优化器·基于多趟分析代价优化器·语义分析、代价优化过程分离·灵活筹划变换控制·基于时间单位(ms)代价计算·解决记录信息使用性问题·增长频率直方图·增长高度直方图桶数

DTCC查询优化：关系变换

DTCC·SFW构造转换为关系树

Select：ID，nameFrom：T

SFW构造–投影(PROJECT)–连接(JOIN)–半连接(SEMIJOIN)–选取(SELECT)–基本表(BASETABLE)

Where：ID=10PROJECT(ID，name)SELECT(ID=10)BASE_TABLE(T)

关系树查询优化：关系变换核心DTCC·消除子查询，“平坦”关系树·子查询一律转化为半连接（SEMIJOIN）例：selectfromT1wheret1.idin(selectIDfromT2)PROJECTSEMIJOINT1

T2查询优化：待选关系树生成DTCC·考虑三个因素·A.拟定连接顺序·B.拟定卡特兰2叉树形状·C.与否下放过滤条件·采用暂时成果减少重复计算·代价模型基本覆盖所有状况·对连接表个数非常多状况,特殊解决查询优化：记录信息

DTCC·记录数据分布状况，用于精准行数预计，特别是数据分布不规则状况，对基数及代价计算有重大影响·频率直方图：不同值较少

·等高直方图：不同值较多

40504000

4002

3990

4032

39803950390038503800

3950

3960

3888DTCC·列存储:–数据按列存储–结合自适应压缩技术–与批量计算技术紧密结合·列存储优缺陷–大幅提高扫描性能–适合批量装载与删除–不适合频繁插入、删除和更新·融合列存储和行存储–提供按列存储选项–结合分区技术–同步适应OLAP和OLTP应用需求I/O效率I/O效率-融合列存储和行存储I/O效率·行存储优化–简化物理记录格式–字段物理顺序与逻辑顺序分离·多buffer类型–常驻内存和常规方式裁减–顾客可以指定·批量读：预解决·支持垂直分区和水平分区

DTCC提高并发度·支持并行插入物理数据存储·并行备份和恢复·分区技术及相应并行查询操作符号

DTCC典型场景一：大成果集

DTCC·场景描述–某表T，31个字段，48亿条记录–随机基于某字段筛选：SELECT*FROMTWHEREFLD1=753–查询符合条件成果集达到千万条记录·分析––––

SQL语句非常简朴，没有更优等效语句成果集筛选条件不拟定，无法使用索引服务器内存为32G，在扫描过程中必然浮现页面裁减由于基本数据量大，因而虽然命中率不高（0.2%），也会生成960万条记录成果集典型场景一：大成果集

DTCC从3个方向入手，提高全表扫描IO效率·批量技术·减少成果集解决时间消耗·调节数据页读取方略典型场景一：大成果集

DTCC·返回成果集方略改进–优化前·依照通信块大小决定成果集分批次返回数量·第一批成果集返回后，自动完毕后续成果集获取和返回–优化后·由应用设定第一批成果集大小和返回时机·当返回第一批成果集后，工作线程暂停SQL查询祈求，直到下一批成果集祈求到来或开始新事务–效果·迅速返回某些成果集，提高顾客体验·避免自动返回所有成果集，减少服务器资源消耗典型场景一：大成果集

DTCC·调节数据读取方略–数据页（page）是数据读写单位–优化前全表扫描：按页读取，每次IO只扫描一种页–优化后：一次扫描各种页，减少IO数量–测试：通过优化后，磁盘吞吐量提高1倍典型场景二：大表连接

DTCC·场景描述–表T1，31个字段，5000W条记录，数据类型涉及int、varchar、datetime、Dec；表T2,15个字段，500W条记录，数据类型涉及varchar、datetime、Dec；–SELECTT1.NAME，T2.TITLEFROMPERSON.PERSONT1，RESOURCES.EMPLOYEET2WHERET1.PERSONID=T2.PERSONIDANDT1.SEX='M';–连接查询字段由最后顾客暂时指定，表上未建索引–成果集不大，但查询表数据量大，连接查询响应时间陡增典型场景二：大表连接

DTCC·分析·行存储特性：连接查询所连接字段在数据页中存储非持续，进行连接查询，需将所有数据页读到内存，IO消耗巨大；·连接匹配时，要对读入缓存中所有页进行扫描。·行存储：–连接列分散在每个数据页中Cn+1

页1

…

Cn+1

页NC1C2C1C2…CnC1…Cm…………C1C1C2…CnC1…Cm…………典型场景二：大表连接

DTCC·优化方向：列存储–按字段存储–连接列被集中存储Cn+1Cn+1…页1

Cn+1

页N–读入缓存中数据页明显减少，系统IO下降C1C1C1C1…C1C2C2…C3…Cm…………典型场景二：大表连接·优化方向：存储压缩–合用于列存储模式压缩算法–初步压缩成果：

DTCC····

采用本案例数据进行测试Float54%（压缩后大小/压缩前大小）Double33%Dec52%·

字符

56%典型场景二：大表连接·优化效果从17小时降至10分钟以内

DTCC典型场景三：全表查询建表DTCC·场景描述–表T,15个字段，500W条记录，数据类型涉及int、varchar、datetime、Dec；–依照T进行查询建表：CREATETABLETTasSELECT*FROMT;典型场景三：全表查询建表DTCC·分析–大表进行查询建表时，需通过如下五个环节初始化目标表

全表扫描

生成成果集

插入成果

事务提交–这个过程中可优化操作有：查询与成果集生成和大量数据插入操作典型场景三：全表查询建表DTCC·直接B树操作–避免成果集解决与数据插入操作–直接复制根节点和叶子是在内存中进行操作，速度更快·优化效果对案例中T进行建表查询–优化前耗时约35S–优化后耗时约4S，性能提高9倍

装载表数据到内存源表B树扫描复制B树典型场景四：重复表达式计算DTCC·场景描述–针对500万条登记表进行如下查询–SELECTIDnum,sub(6,8,IDnum)as生日,(now()-sub(6,8,IDnum))as年龄from…·问题分析·表达式sub(6,8,IDnum)可重用典型场景四：重复表达式计算DTCC·改进优化：–一种表达式浮现多次，只计算一次–本例中性能提高70%。其她场景性能提高限度取决于计算表达式复杂度与数据量典型场景五：并行查询插入DTCC·场景描述–同构造表T1~T10，每张表500万条记录，需要将10张表所有数据合并到一种暂时表Ttmp中–INSERTINTOTtmpSELECT*FROMT1–INSERTINTOTtmpSELECT*FROMT2。。。–应用并行化并没有带来较大提高–分析–Ttmp成为瓶颈：原有逻辑Rowid成为资源瓶颈–逻辑Rowid：不代表物理存储位置，更新、插入、重组等操作代价减少，但Rowid需要通过临界资源获取–原有产品针对OLTP业务场景，OLTP事务以分散、短小事务为主，原有RowID机制不会成为突出瓶颈典型场景五：并行查询插入DTCC·改进–物理RowID：代表记录物理存储位置–各种工作线程进行插入操作，无需进入临界资源获取rowid，每个工作线程自行生成RowID–实现真正意义上并发插入应用优化

DTCC·好性能需要应用与数据库配合实现–应用架构设计应站在系统全局考虑性能问题–应用与数据库应当取长补短·数据存储–基于分区表进行数据划分·应用并行化–复杂事务分解为各种可并行简朴事务应用优化-手段

DTCC····

保存第一步过滤成果集运用视图减少中间成果集保存数据按月份分区TOP查询减少不必要全成果集应用优化-大表全表扫描DTCC·典型场景–5000万无索引TOP查询：SELECT*FROMT6WHERENAMELIKE‘张三’–优化前：数据库服务器CPU满载而应用服务器没有负载–在最坏状况下，将需要扫描整个表·分析：–系统设计需要站在全局角度，充分考虑应用、中间件、数据库之间负载分派–充分运用已有硬件应用优化-大表全表扫描DTCC·改进：·数据进行分表和分区·DM已实现分区表并行查询操作符，提供了分区表优化支持·应用根据分表更改查询模块，从单线程改为多线程·在应用服务器将各分表查询成果合并·效果：·按最坏状况测试，查询时间由本来不可预期，提高到2分钟内应用优化-数据清洗与入库DTCC–最初方式：–基于JDBC驱动数据迁移工具进行清洗和入库操作–批量绑定–迁移工具资源消耗随着迁移时间持续增长，导致迁移速度在运营3天后急剧下降–初始数据（1T）入库时间达到1个月，相称于400条/s应用优化-数据清洗与入库DTCC–问题分析：–超过100亿条记录，虽然每5000条提交一次，也有2百万次解析-筹划-代价-执行流程–大量数据库redo与undo日记操作–解决方案–运用批量＋ＢＣＰ–运用并行化充分发挥多CPU解决能力，增长IO吞吐量–JDBC方式转变为JNI+ODBC–实现动态编译型ETL脚本引擎DTCC图DMETL内嵌BCP应用优化-DMETL技术改进应用优化-DMETL技术改进DTCC应用优化-数据划分和并行化应用优化-数据划分和并行化应用优化-BCP

DTCC·将清洗与入库分离·并行化清洗和装载入库·入库应用BCP方式·通过批量绑定减少了网络开销·服务器内部为BCP专门实现了”bcp_fast_insert”办法·绕过SQL解决流程，直接操作B树叶子节点·不进行Redo与Undo·不进行约束检查·对原有BCP也进行了服务器端批量化解决最后