阿里数据库团队PostgreSQL实践

性能优化
• 避免长事物
– 读取大量数据需要使用事物来防止溢出，但是使用 长事物可能造成性能问题。
– 长事物会导致vacuum进程无法回收已经删除数据 的存储空间，新的数据写入只能使用新的数据块上， 导致磁盘空间持续增长。
– 解决办法：
• 数据库上监控长事物 • 程序上排查长事物产生的原因并进行修复
– 批量修改代码，将程序中获取数据值时将字段 名小写。 （操作性差，工作量大）
• row.getInt(“user_id”)…;
应用程序改造
• Oracle PG 默认元数据大小写差异解决办法
– 持久层框架改造，修改数据库字段到JavaBean的映射 方法(Mapping) SQL：select user_name from t where user_id=? Oracle 元数据：USER_NAME 程序获取数据：row.get(“USER_NAME”) PG 元数据：user_name，原来的程序无法获取到数据：
速卖 通
芝麻 信用
Fra Baidu bibliotek
用户服务层---数据库服务平台(iDB)
资源 申请 数据 访问 开发 设计 生产 变更 分库 分表 SQL 审核 流程 管控 权限 管控
基 础 技 术 层 运 维 组 件 层
AliSQL
OceanBase
PostgreSQL
MongoDB
Oracle
SQLServer
……
HA
监控 告警
SQL改造
– Oracle Merge Into 插入或者更新的SQL改造
• PostgreSQL需要联合使用 with 查询, update returning, insert来进行选择性更新或者插入。
with data as (select #id#::integer id,#name#::varchar name,#status#::varchar status), upsert as (update my_user dst set name=d.name, gmt_modified=now(), status=d.status from data d where d.name is not null and d.id=dst.id returning dst.* ) insert into my_user(id,name,status) select nextVal('seq_my_user'),d.name,d.status from data d where not exists(select 1 from my_user dst where dst.id = d.id)
2010年淘宝启动“去IOE”项目
PostgreSQL实践原因
• IDB帮助集团实现了去O，但是自己还在使用 Oracle。 • 2014年底，这台Oracle服务器已经集团仅剩的 几台服务器之一，需要承担高昂的License费 用。 • 去O势在必行，用什么来替换它呢？
– MySQL? 需要对应用和SQL做大量改造。 – 达梦? 功能与Oracle兼容，稳定性达不到要求 – PostgreSQL? 改造成本低，安全稳定
SQL改造
• 部分数据类型转换
– Clob 大字段类型
– Oracle 布尔值 char(1) „Y‟/‟N‟
• 转换成 PG 类型 boolean • 转换成PG类型varchar • 增加实际列或者使用视图 • PG不允许存储，过滤掉再保存
– 虚拟列
– Oracle \u0000字符
– 修改字段类型时可以使用 USING {表达式} 进行字段值 转换
阿里数据库团队PostgreSQL实践
杨洋洋(阳帅)
yang_yang8708@163.com
阿里巴巴数据库团队
提纲
• • • • IDB产品介绍 PostgreSQL实践原因 实践步骤 实践总结
IDB 定位
淘宝 天猫 168 8 支付 宝 余额 宝 口碑 高德 地图 阿里 云 UC 钉钉 菜鸟 物流 …
性能优化
• 受长事务影响膨胀的表的处理
– 小表可使用 vacuum full 来处理。
– 大表使用 pg_reorg 来进行在线空间收缩，不锁 表，不影响业务。
性能优化
• 分页排序优化
– 部分Oracle的复杂SQL使用到PG上会产生性能 问题，多层子查询只在最外层排序分页的时候 性能影响明显，尽量在子查询里进行关联，过 滤，分页。
USER_NAME User_Age USER_NAME USER_AGE user_name user_age
应用程序改造
• Oracle 与 PG 元数据大小写差异解决办法
– 批量修改SQL文件，为字段加入大写别名（操 作性差，工作量大）
• Select user_id “USER_ID” from t where …;
SQL:user_name -> PG:user_name -> 框架转 换：”USER_NAME” ->原来的程序正常获取到数据。
应用程序改造
• 持久层框架改造改造示例：
– rsmd ＝ ResultSet.getMetaData() – for (int i = 0, n = rsmd.getColumnCount(); i < n; i++) { – String columnName = rsmd.getColumnName(i + 1); – if (delegate.isUseColumnLabel()) { – String columnLabel = rsmd.getColumnLabel(i + 1); –
• 如alter table user alter column is_deleted type boolean
USING case is_deleted when 'Y' then true else false end ;
• alter table user alter column is_deleted type boolean
} else { columnName = columnLabel; }
}
应用程序改造
• 游标/流式数据处理 需要开启事物
– PG-JDBC驱动默认加载所有行，如果结果集大 会造成应用(JAVA)内存溢出(OutOfMemory) – 使用游标或者设置fetchSize需要开启事物
应用程序改造
• 配合表数据类型的改造进行修改。
–
– – – –
– – – –
if (isPostgreSQL) { if(columnLabel.equals(columnLabel.toLowerCase())){ //小写 的字段名直接改大写。 columnName = columnLabel.toUpperCase(); } else { columnName = columnLabel; //”user_ID” 大小写敏感字 段保留 }
• 分组合并：Oracle中wm_concat(xxx)转换成PG中的 string_agg(xxx,„,‟) over (partition by X oder by Y)来实 现。 • Oracle中的regexp_replace(xxx,reg)在PG需要加上第 三个参数 “,” • Bitand(A,B) ：替换成运算符 & , A & B • sysdate： now, current_timestamp • NVL ： coalesce • DECODE：case when then else end
SQL改造
– 整数参数传入空字符串的处理 使用 #id#::numeric时，程序传递空字符串‟‟作为参数， 则会出现 invalid input syntax for type numeric 错误
– 需要使用to_number(#param#) （EDB）
• Select * from t where status = „new‟ and pid = to_number(#param#)
USING is_deleted = 'Y';
SQL改造
• 递归查询
– START WITH CONNECT BY
例如
• 转换成 WITH RECURSIVE 查询
• select * from emp start with empno=7 connect by mgr=prior empno;
转换成PG的递归SQL： with recursive r_emp r as (select a.id, a.name, a.pid from emp a where id = 1001 union all select b.id, b.name, b.pid from emp b inner join r on r.id = b.pid ) select id, name from r
实践步骤
• 实践评估：可行性分析、工作量评估。 • 制定方案：确定迁移的重点和难点，制定 迁移方案。 • 应用改造： SQL改造，代码改造。 • 数据迁移：结构迁移，数据迁移。 • 回归与测试：功能回归、性能测试。 • 性能调优：针对上线后的性能问题进行分 析和优化。
SQL改造
– 时间间隔
• Oracle时间相减得到间隔，单位为天。PG时间相减得到 interval值，需要转换为具体的时间值。 • Oracle中sysdate + interval xxxx unit(precision) 类型的数 据，PG中用now()+ „时间间隔字符串‟ 的方式来实现。
– javaBean.setDeleted(“Y”.equals(row.getString(„I S_DELETE‟)) – javaBean.setDeleted(row.getBoolean(„IS_DEL ETE‟))
• 通知下游数据使用方进行程序改造。
性能优化
• 模糊查询：
– 如果没有修改PG库的locale，使用 like ‟abc%‟ 查询时，默认会扫描所有行，即使有索引也不 走索引，引发性能问题。原因是要查询的数据 类型和索引的数据类型不匹配。 – 解决方式：重建索引，为索引列指定 pattern_ops 模式，如 varchar_pattern_ops 。 create index idx_t_name on t(name varchar_pattern_ops);
应用程序改造
• 字段名的大小转换与映射问题。
– Oracle 字段名默认大写，PG字段名默认小写。
全部大写 SQL字段名
Oracle得到的元数据 PG得到的元数据
大小写混合
区分大小写 “User_Id” User_Id User_Id
区分大小写 “use_desc” use_desc use_desc
SQL改造
– 分页
• Oracle
SELECT * from ( SELECT rownum AS rn, t.* FROM t WHERE condition AND rownum <={end} ) where rn >= {start}
• PG
SELECT * from t where condition offset {start} limit {pagesize}
性能 压测
自动化 调度
元数 据
备份 恢复
安装 配置
数据 质量
安全 审计
…
IDB是数据库团队自主研发的一个数据库服务产品， 是集团去O的重要实施平台。 先后实现对Oracle,MySQL,Oceanbase,SqlServer, PostgreSQL等数据库的查询，数据变更，结构变更 的支持。并且支持分库分表的操作。
• select id, „new‟ ::varchar status from t;
这样才能保证应用程序中获取到的status数据类型为 String。 不强制转换获取到的是Object 对象，会导致JavaBean或者 DO对象的属性值设置失败。
SQL改造
– 函数替换：将Oracle部分PG不支持的函数替换 为PG的等价函数。
– Now() + „1 day‟ – Now() + „14400‟::interval
– 别名设置：
• 避免使用关键字role,data,label,type,name 作别名，或者 加上as 关键字
SQL改造
– 常量参数需要进行强制类型转换，否则类型则 unknown，框架无法自动解析类型。