817
阿裏雲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、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的寫出速度。
最後更新：2017-09-11 17:02:57