阿里云RDS PostgreSQL OSS 外部表 - 并行写提速案例-阿里云开发者社区

开发者社区> 阿里云数据库> 正文

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

简介:

标签

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的写出速度。

版权声明:本文中所有内容均属于阿里云开发者社区所有,任何媒体、网站或个人未经阿里云开发者社区协议授权不得转载、链接、转贴或以其他方式复制发布/发表。申请授权请邮件developerteam@list.alibaba-inc.com,已获得阿里云开发者社区协议授权的媒体、网站,在转载使用时必须注明"稿件来源:阿里云开发者社区,原文作者姓名",违者本社区将依法追究责任。 如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developer2020@service.aliyun.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
阿里云数据库
使用钉钉扫一扫加入圈子
+ 订阅

帮用户承担一切数据库风险,给您何止是安心!

官方博客
链接