阿里云RDS PostgreSQL OSS 外部表 - 并行写提速案例

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

标签

PostgreSQL , oss对象存储 , 阿里云RDS PG , 并行写 , dblink , 异步调用 , 异步任务监控 , OSS外部表 , 数据传输


背景

阿里云RDS PostgreSQL、HybridDB for PostgreSQL提供了一个非常强大的功能,OSS对象存储外部表。

阿里云的RDS PostgreSQL用户可以利用OSS存储冷数据(OSS外部表的形态呈现),实现冷热分离;也可以利用OSS作为数据的中转桥梁,打通其他云端业务,例如HDB FOR PostgreSQL分析型数据库。

oss外部表的用法文档如下。

https://help.aliyun.com/document_detail/44461.html

目前oss外部表支持文本\GZIP等格式。将来还会支持流行的列存格式(ORC,parquet等),扫描下推,并行读写OSS文件等,提升体验。

由于目前RDS PG的版本是9.4,9.4的版本目前不支持并行框架,单个写进程是15MB/s左右。采用gzip压缩格式,可能能提升到20MB/s。

采用并行框架的PostgreSQL 10,可以在写出到OSS时开启并行写,每个WORKER进程 20MB/s,单表导到OSS的速度将得到大幅度的提升(读取也一样支持并行)。

如果RDS PG 9.4的用户需要将大表快速的写出到OSS的话,有什么优化手段呢?

答案是通过PG DBLINK来实现异步并行。

业务背景

用户的订餐、购物、寄送包裹等操作,会产生订单,订单与业务逻辑挂钩,在各个业务系统流转会生成新的状态或属性(每个业务系统产生的数据字段可能都不一样)。

为了对订单数据进行统一管理、准实时数据分析、透视。需要实时的将订单数据在各个业务系统中生成的状态、属性进行合并,输送到分析型数据库HybridDB for PostgreSQL。

数据流

订单信息,从业务系统流入阿里云的流计算平台,从流计算平台实时写入RDS PG,从RDS PG批量写入OSS,从OSS批量合并到HybridDB PG(HybridDB PostgreSQL保存最完整的订单信息,提供分析透视)。

1、从流计算平台到RDS PG。

实时、批量,采用UPSERT的方式,PostgreSQL UPSERT的语法请参考:

《PostgreSQL upsert功能(insert on conflict do)的用法》

我们采用了其中FUNCTION批量upsert的方法。对于PostgreSQL 9.5以及以上版本,可以在function中使用insert into on conflict语法(因为insert into on conflict不支持values (),(),()...()的批量写法)。

2、从RDS PG写入OSS

由于RDS PG 9.4没有内置写OSS并行,当数据量很大的时候,单线程写速度很慢,容易成为瓶颈。

这个是本文的重点,RDS PG 9.4如何采用单表异步并行,写入OSS。(未来PG 10上线,内置了并行,不需要这么麻烦)

3、从OSS合并到HybridDB PostgreSQL

采用三步走的方法:

3.1 oss_tmp1 inner join big_table into tmp2 得到大表(总表)已有订单已有字段属性+订单新状态的数据tmp2。

3.2 delete from big_table using tmp2 删除总表中已剥离出来的tmp2。

3.3 insert into bit_table select * from oss_tmp1 left join tmp2 where tmp2.* is null (union all) tmp2。 将数据汇入总表。

RULE的方式,并不能提升效果

创建4个外部表(4个并行),表名不一样,其他外部参数(bucket, dir)一样,文件名会以表名来命名,所以不用担心写入OSS 同一目录的时候文件重名:  
  
tbl_oss_ext0  
tbl_oss_ext1  
tbl_oss_ext2  
tbl_oss_ext3  

创建一张规则表,与外部表定义一致:

create table tbl_entry (like tbl_oss_ext0);  

创建规则:

create rule r0 as on insert to tbl_entry where mod(order_id, 4)=0 do instead insert into tbl_oss_ext0 values (NEW.*);  
create rule r1 as on insert to tbl_entry where mod(order_id, 4)=1 do instead insert into tbl_oss_ext1 values (NEW.*);  
create rule r2 as on insert to tbl_entry where mod(order_id, 4)=2 do instead insert into tbl_oss_ext2 values (NEW.*);  
create rule r3 as on insert to tbl_entry where mod(order_id, 4)=3 do instead insert into tbl_oss_ext3 values (NEW.*);  

写入规则表,数据将重定向到4个外部表。

insert into tbl_entry select * from stream_table;  

因为只使用了一个进程在做这件事情,所以这种方法并不是真正的并行。

所以采用DBLINK异步调用,实现真正的并行。

https://www.postgresql.org/docs/10/static/dblink.html

基于DBLINK的并行设计

1、前端写分区表(可选)

例如写入到16个分区,导出时,每个分区表对应一个OSS外部表,可以实现16的并行度。

分区表有两种写法:

PG内置分区(继承、触发器、规则)。

业务层逻辑分区,业务层确定数据写入哪个分区。

这两种方法,方法1更灵活,但是性能会受到一定的影响。

如果不写分区表,单表开启并行的话,可以使用取模的方法来并行,会带来一定的重复扫描本地表的成本(每个并行都需要扫描所有记录,而且不建议用索引来分割,因为索引扫描速度也好不到哪里去)。

2、建立本地DBLINK连接(并设置连接指纹)

使用application_name来设置连接指纹。

select dblink_connect('外部表名_1','dbname=postgres user=xxx password=pwd application_name=外部表名_1');  
select dblink_connect('外部表名_2','dbname=postgres user=xxx password=pwd application_name=外部表名_2');  
select dblink_connect('外部表名_3','dbname=postgres user=xxx password=pwd application_name=外部表名_3');  
select dblink_connect('外部表名_4','dbname=postgres user=xxx password=pwd application_name=外部表名_4');  

3、使用DBLINK异步调用接口发起写请求

同时将只需结果输出到结果表。

select dblink_send_query('外部表名_1','begin; insert into 外部表1 select * from tmp where mod(order_id,4)=0; insert into tbl_result values(1); end;');  
select dblink_send_query('外部表名_2','begin; insert into 外部表2 select * from tmp where mod(order_id,4)=1; insert into tbl_result values(2); end;');  
select dblink_send_query('外部表名_3','begin; insert into 外部表3 select * from tmp where mod(order_id,4)=2; insert into tbl_result values(3); end;');  
select dblink_send_query('外部表名_4','begin; insert into 外部表4 select * from tmp where mod(order_id,4)=3; insert into tbl_result values(4); end;');  

4、查看异步任务状态

select * from pg_stat_activity where application_name in ('外部表名_1','外部表名_2','外部表名_3','外部表名4') and state !~ 'idle';  
-- 没有记录返回,说明任务跑完。  
  
通过查询tbl_result,如果记录数不等于线程数,则说明有任务失败。  
  
任务正常结束:清除tbl_result表。  
  
任务异常结束:清除tbl_result表、清除oss dir,重跑任务。  

5、关闭连接

开启了异步调用的连接,需要get异步调用的结果后,才能继续使用这个连接。或者关闭连接后,重新建立连接即可使用。

https://www.postgresql.org/docs/10/static/dblink.html

dblink_connect — opens a persistent connection to a remote database  
dblink_connect_u — opens a persistent connection to a remote database, insecurely  
dblink_disconnect — closes a persistent connection to a remote database  
dblink — executes a query in a remote database  
dblink_exec — executes a command in a remote database  
dblink_open — opens a cursor in a remote database  
dblink_fetch — returns rows from an open cursor in a remote database  
dblink_close — closes a cursor in a remote database  
dblink_get_connections — returns the names of all open named dblink connections  
dblink_error_message — gets last error message on the named connection  
dblink_send_query — sends an async query to a remote database  
dblink_is_busy — checks if connection is busy with an async query  
dblink_get_notify — retrieve async notifications on a connection  
dblink_get_result — gets an async query result  
dblink_cancel_query — cancels any active query on the named connection  
dblink_get_pkey — returns the positions and field names of a relation's primary key fields  
dblink_build_sql_insert — builds an INSERT statement using a local tuple, replacing the primary key field values with alternative supplied values  
dblink_build_sql_delete — builds a DELETE statement using supplied values for primary key field values  
dblink_build_sql_update — builds an UPDATE statement using a local tuple, replacing the primary key field values with alternative supplied values  

6、达到的效果

开启40个并行,26GB的数据,140秒,达到190MB/s的写出速度。

云端相关产品

阿里云 RDS PostgreSQL

阿里云 HybridDB for PostgreSQL

相关案例

《打造云端流计算、在线业务、数据分析的业务数据闭环 - 阿里云RDS、HybridDB for PostgreSQL最佳实践》

小结

目前阿里云RDS PostgreSQL、HybridDB PostgreSQL oss外部表支持文本\GZIP等格式。将来还会支持流行的列存格式(ORC,parquet等),扫描下推,并行读写OSS文件等,提升体验。

由于目前RDS PG的版本是9.4,9.4的版本目前不支持并行框架,单个写进程是15MB/s左右。采用gzip压缩格式,可能能提升到20MB/s。

采用并行框架的PostgreSQL 10,可以在写出到OSS时开启并行写,每个WORKER进程 20MB/s,单表导到OSS的速度将得到大幅度的提升(读取也一样支持并行)。

如果RDS PG 9.4的用户需要将大表快速的写出到OSS的话,通过PG DBLINK来实现异步并行。

开启40个并行,26GB的数据,140秒,达到190MB/s的写出速度。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
15天前
|
关系型数据库 数据库 数据安全/隐私保护
云数据库实战:基于阿里云RDS的Python应用开发与优化
在互联网时代,数据驱动的应用已成为企业竞争力的核心。阿里云RDS为开发者提供稳定高效的数据库托管服务,支持多种数据库引擎,具备自动化管理、高可用性和弹性扩展等优势。本文通过Python应用案例,从零开始搭建基于阿里云RDS的数据库应用,详细演示连接、CRUD操作及性能优化与安全管理实践,帮助读者快速上手并提升应用性能。
|
2月前
|
SQL 人工智能 关系型数据库
【PG锦囊】阿里云 RDS PostgreSQL 版插件—AI 插件(rds_ai)
本文介绍了AI 插件(rds_ai)的核心优势、适用场景等,帮助您更好地了解 rds_ai 插件。想了解更多 RDS 插件信息和讨论交流,欢迎加入 RDS PG 插件用户专项服务群(103525002795)
|
2月前
|
运维 关系型数据库 MySQL
体验领礼啦!体验自建数据库迁移到阿里云数据库RDS,领取桌面置物架!
「技术解决方案【Cloud Up 挑战赛】」上线!本方案介绍如何将自建数据库平滑迁移至云数据库RDS,解决业务增长带来的运维难题。通过使用RDS MySQL,您可获得稳定、可靠和安全的企业级数据库服务,专注于核心业务发展。完成任务即可领取桌面置物架,每个工作日限量50个,先到先得。
|
5月前
|
容灾 关系型数据库 数据库
阿里云RDS服务巴黎奥运会赛事系统,助力云上奥运稳定运行
2024年巴黎奥运会,阿里云作为官方云服务合作伙伴,提供了稳定的技术支持。云数据库RDS通过备份恢复、实时监控、容灾切换等产品能力,确保了赛事系统的平稳运行。
 阿里云RDS服务巴黎奥运会赛事系统,助力云上奥运稳定运行
|
4月前
|
SQL DataWorks 关系型数据库
阿里云 DataWorks 正式支持 SelectDB & Apache Doris 数据源,实现 MySQL 整库实时同步
阿里云数据库 SelectDB 版是阿里云与飞轮科技联合基于 Apache Doris 内核打造的现代化数据仓库,支持大规模实时数据上的极速查询分析。通过实时、统一、弹性、开放的核心能力,能够为企业提供高性价比、简单易用、安全稳定、低成本的实时大数据分析支持。SelectDB 具备世界领先的实时分析能力,能够实现秒级的数据实时导入与同步,在宽表、复杂多表关联、高并发点查等不同场景下,提供超越一众国际知名的同类产品的优秀性能,多次登顶 ClickBench 全球数据库分析性能排行榜。
|
7月前
|
弹性计算 关系型数据库 MySQL
新一期陪跑班开课啦!阿里云专家手把手带你体验RDS通用云盘核心能力
本次课程将手把手带领用户创建一个云数据库RDS MySQL(通用云盘),并通过云服务器ECS对RDS MySQL实例进行压测,体验IO加速和IO突发带来的性能提升;并通过DMS执行DDL,将数据归档到OSS,再结合云盘缩容,体验数据归档带来的成本优势。
|
7月前
|
关系型数据库 MySQL 网络安全
阿里云安装Mysql
阿里云安装Mysql
473 1
|
7月前
|
关系型数据库 数据库 数据安全/隐私保护
"告别繁琐!Python大神揭秘:如何一键定制阿里云RDS备份策略,让数据安全与效率并肩飞,轻松玩转云端数据库!"
【8月更文挑战第14天】在云计算时代,数据库安全至关重要。阿里云RDS提供自动备份,但标准策略难以适应所有场景。传统手动备份灵活性差、管理成本高且恢复效率低。本文对比手动备份,介绍使用Python自定义阿里云RDS备份策略的方法,实现动态调整备份频率、集中管理和智能决策,提升备份效率与数据安全性。示例代码演示如何创建自动备份任务。通过自动化与智能化备份管理,支持企业数字化转型。
161 2
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
402 0
|
存储 缓存 关系型数据库

相关产品

  • 云数据库 RDS
  • 云数据库 RDS PostgreSQL 版
  • 云数据库 RDS MySQL 版