阿里数据库团队PostgreSQL实践

阿里数据库团队PostgreSQL实践杨洋洋(阳帅)[e-mail]阿里巴巴数据库团队提纲•IDB产品介绍•PostgreSQL实践原因•实践步骤•实践总结IDB定位HA备份恢复性能压测自动化调度数据质量安全审计监控告警安装配置元数据…用户服务层---数据库服务平台(iDB)资源申请数据访问开发设计生产变更分库分表SQL审核流程管控权限管控运维组件层淘宝天猫支付宝余额宝口碑芝麻信用钉钉1688速卖通高德地图UC阿里云菜鸟物流…AliSQLMongoDBPostgreSQLOceanBase……Oracle基础技术层SQLServerIDB是数据库团队自主研发的一个数据库服务产品，是集团去O的重要实施平台。先后实现对Oracle,MySQL,Oceanbase,SqlServer,PostgreSQL等数据库的查询，数据变更，结构变更的支持。并且支持分库分表的操作。2010年淘宝启动“去IOE”项目PostgreSQL实践原因•IDB帮助集团实现了去O，但是自己还在使用Oracle。•2014年底，这台Oracle服务器已经集团仅剩的几台服务器之一，需要承担高昂的License费用。•去O势在必行，用什么来替换它呢？–MySQL?需要对应用和SQL做大量改造。–达梦?功能与Oracle兼容，稳定性达不到要求–PostgreSQL?改造成本低，安全稳定实践步骤•实践评估：可行性分析、工作量评估。•制定方案：确定迁移的重点和难点，制定迁移方案。•应用改造：SQL改造，代码改造。•数据迁移：结构迁移，数据迁移。•回归与测试：功能回归、性能测试。•性能调优：针对上线后的性能问题进行分析和优化。SQL改造–时间间隔•Oracle时间相减得到间隔，单位为天。PG时间相减得到interval值，需要转换为具体的时间值。•Oracle中sysdate+intervalxxxxunit(precision)类型的数据，PG中用now()+‘时间间隔字符串’的方式来实现。–Now()+‘1day’–Now()+‘14400’::interval–别名设置：•避免使用关键字role,data,label,type,name作别名，或者加上as关键字SQL改造–常量参数需要进行强制类型转换，否则类型则unknown，框架无法自动解析类型。•selectid,‘new’::varcharstatusfromt;这样才能保证应用程序中获取到的status数据类型为String。不强制转换获取到的是Object对象，会导致JavaBean或者DO对象的属性值设置失败。SQL改造–函数替换：将Oracle部分PG不支持的函数替换为PG的等价函数。•分组合并：Oracle中wm_concat(xxx)转换成PG中的string_agg(xxx,‘,’)over(partitionbyXoderbyY)来实现。•Oracle中的regexp_replace(xxx,reg)在PG需要加上第三个参数“,”•Bitand(A,B)：替换成运算符&,A&B•sysdate：now,current_timestamp•NVL：coalesce•DECODE：casewhenthenelseendSQL改造–OracleMergeInto插入或者更新的SQL改造•PostgreSQL需要联合使用with查询,updatereturning,insert来进行选择性更新或者插入。withdataas(select#id#::integerid,#name#::varcharname,#status#::varcharstatus),upsertas(updatemy_userdstsetname=d.name,gmt_modified=now(),status=d.statusfromdatadwhered.nameisnotnullandd.id=dst.idreturningdst.*)insertintomy_user(id,name,status)selectnextVal('seq_my_user'),d.name,d.statusfromdatadwherenotexists(select1frommy_userdstwheredst.id=d.id)SQL改造–整数参数传入空字符串的处理使用#id#::numeric时，程序传递空字符串’’作为参数，则会出现invalidinputsyntaxfortypenumeric错误–需要使用to_number(#param#)（EDB）•Select*fromtwherestatus=‘new’andpid=to_number(#param#)SQL改造–分页•OracleSELECT*from(SELECTrownumASrn,t.*FROMtWHEREconditionANDrownum={end})wherern={start}•PGSELECT*fromtwhereconditionoffset{start}limit{pagesize}SQL改造•部分数据类型转换–Oracle布尔值char(1)‘Y’/’N’•转换成PG类型boolean–Clob大字段类型•转换成PG类型varchar–虚拟列•增加实际列或者使用视图–Oracle\u0000字符•PG不允许存储，过滤掉再保存–修改字段类型时可以使用USING{表达式}进行字段值转换•如altertableuseraltercolumnis_deletedtypebooleanUSINGcaseis_deletedwhen'Y'thentrueelsefalseend;•altertableuseraltercolumnis_deletedtypebooleanUSINGis_deleted='Y';SQL改造•递归查询–STARTWITHCONNECTBY•转换成WITHRECURSIVE查询例如•select*fromempstartwithempno=7connectbymgr=priorempno;转换成PG的递归SQL：withrecursiver_empras(selecta.id,a.name,a.pidfromempawhereid=1001unionallselectb.id,b.name,b.pidfromempbinnerjoinronr.id=b.pid)selectid,namefromr应用程序改造•字段名的大小转换与映射问题。–Oracle字段名默认大写，PG字段名默认小写。全部大写大小写混合区分大小写区分大小写SQL字段名USER_NAMEUser_Age“User_Id”“use_desc”Oracle得到的元数据USER_NAMEUSER_AGEUser_Iduse_descPG得到的元数据user_nameuser_ageUser_Iduse_desc应用程序改造•Oracle与PG元数据大小写差异解决办法–批量修改SQL文件，为字段加入大写别名（操作性差，工作量大）•Selectuser_id“USER_ID”fromtwhere…;–批量修改代码，将程序中获取数据值时将字段名小写。（操作性差，工作量大）•row.getInt(“user_id”)…;应用程序改造•OraclePG默认元数据大小写差异解决办法–持久层框架改造，修改数据库字段到JavaBean的映射方法(Mapping)SQL：selectuser_namefromtwhereuser_id=?Oracle元数据：USER_NAME程序获取数据：row.get(“USER_NAME”)PG元数据：user_name，原来的程序无法获取到数据：SQL:user_name-PG:user_name-框架转换：”USER_NAME”-原来的程序正常获取到数据。应用程序改造•持久层框架改造改造示例：–rsmd＝ResultSet.getMetaData()–for(inti=0,n=rsmd.getColumnCount();in;i++){–StringcolumnName=rsmd.getColumnName(i+1);–if(delegate.isUseColumnLabel()){–StringcolumnLabel=rsmd.getColumnLabel(i+1);–if(isPostgreSQL){–if(columnLabel.equals(columnLabel.toLowerCase())){//小写的字段名直接改大写。–columnName=columnLabel.toUpperCase();–}else{–columnName=columnLabel;//”user_ID”大小写敏感字段保留–}–}else{–columnName=columnLabel;–}–}应用程序改造•游标/流式数据处理需要开启事物–PG-JDBC驱动默认加载所有行，如果结果集大会造成应用(JAVA)内存溢出(OutOfMemory)–使用游标或者设置fetchSize需要开启事物应用程序改造•配合表数据类型的改造进行修改。–javaBean.setDeleted(“Y”.equals(row.getString(‘IS_DELETE’))–javaBean.setDeleted(row.getBoolean(‘IS_DELETE’))•通知下游数据使用方进行程序改造。性能优化•模糊查询：–如果没有修改PG库的locale，使用like’abc%’查询时，默认会扫描所有行，即使有索引也不走索引，引发性能问题。原因是要查询的数据类型和索引的数据类型不匹配。–解决方式：重建索引，为索引列指定pattern_ops模式，如varchar_pattern_ops。createindexidx_t_nameont(namevarchar_pattern_ops);性能优化•避免长事物–读取大量数据需要使用事物来防止溢出，但是使用长事物可能造成性能问题。–长事物会导致vacuum进程无法回收已经删除数据的存储空间，新的数据写入只能使用新的数据块上，导致磁盘空间持续增长。–解决办法：•数据库上监控长事物•程序上排查长事物产生的原因并进行修复性能优化•受长事务影响膨胀的表的处理–小表可使用vacuumfull来处理。–大表使用pg_reorg来进行在线空间收缩，不锁表，不影响业务。性能优化•分页排序优化–部分Oracle的复杂SQL使用到PG上会产生性能问题，多层子查询只在最外层排序分页的时候性能影响明显，尽量在子查询里进行关联，过滤，分页。当表和子查询多时，表的join顺序没有Oracle优化得好，可能会走错索引，所以尽量避免子查询，使用join来做。数据迁移•确定迁移工具•制定迁移方案•制定迁移脚本，开始迁移•验证迁移后的数据正确性数据迁移•工具选择–MTK–Ora2PG，DBConvert–DSQL(分布式数据库SQL引擎),–DTS（阿里云的数据迁移服务），数据迁移•制定迁移方案：MTK+DSQL(分布式数据库SQL引擎)–MTK迁移任务可以并行到表级别–DSQL迁移任务可以并行到行级别，解决大表迁移时间长的问题。–重要的数据使用触发器记录变更，全量迁移完成之后再做增量迁移数据迁移•制定迁移脚本–元数据迁移•Schema,表结构，视图，序列，……–制定数据迁移任务脚本•大表单独使用任务优先迁移–如任务一：runMTK.sht1&任务二：runMTK.sht2&•小表批量迁移–如任务三：runMTK.sht7,t8,t9,t10&数据迁移•先建立索引再进行数据迁移–建立索引比较耗时，每个索引创建时间与接近迁移一次数据时间一个带7个索引的样本表进行测试样本（21G/）同步数据时间(分钟)创建索引时间(分钟)CPU总耗时先数据后索引111306%(60%)/4%(100%)2H先索引后数据（多线程写）33014%(