PostgreSQL 9.6 sharding + 單元化 (based on postgres_fdw) 最佳實踐 - 通用水準分庫場景設計與實踐

digoal

2016-10-05

postgresql , 9.6 , 水準分庫 , sharding , 單元化

本文将以實踐為主，定一個小目标，講解一下如何使用postgres_fdw實作sharding。

單元化則可以結合bdr插件來實作。

我以一個最簡單易懂的場景來舉例，例如所有的業務表都包含了使用者id這個字段，并且業務通路資料時，一定會帶上使用者id進行通路。

同時使用者沒有跨使用者id的通路需求。

以上設計是大多數公司使用資料庫sharding的最佳實踐設計。

以典型的關系系統為例，建構一個測試場景，每個表都帶有使用者id，以使用者id哈希取模為分片規則。

1. 使用者資訊表

2. 日志表

3. 使用者正向關系表（user like who）

聚合物化視圖

4. 使用者反向關系表（who like user）

ddl-1.sql如下

業務需求

初設256個分片，4個datanode，每個datanode存放64個分片。

測試時使用本地環境模拟，真實環境修改為對應的ip位址和端口即可。

segment 1 : 127.0.0.1:5281:db0:role0:pwd , schema_name role0

segment 2 : 127.0.0.1:5281:db1:role1:pwd , schema_name role1

segment 3 : 127.0.0.1:5281:db2:role2:pwd , schema_name role2

segment 4 : 127.0.0.1:5281:db3:role3:pwd , schema_name role3

master 1 : 127.0.0.1:5281:mas1:digoal:pwd , schema_name digoal

master 2 : 127.0.0.1:5281:mas2:digoal:pwd , schema_name digoal

修改pg_hba.conf，確定127.0.0.1使用md5認證

0. 初始化測試環境

1. 初始化segments (datanodes)

初始化segment1

初始化segment2

初始化segment3

初始化segment4

2. 初始化masters (coordinators)

master節點隻存儲中繼資料，無狀态，完全對稱，可以橫向或者。

初始化 master_1

導入外部表結構

建構限制

建構繼承關系

建構插入觸發器

對于業務需要插入資料的表，建構插入觸發器，例子使用動态sql，未優化效率。

初始化 master_n

如果中繼資料發生變更，注意同步master的meta資訊。

可以使用邏輯複制的方式，複制出多個一樣的master。

1. 所有sql都需要帶上分區條件，例如

2. userinfo 增删改查

新增使用者、銷毀使用者、修改使用者資料、查詢使用者資料

3. user_log 增查

新增使用者登陸日志、查詢使用者日志

4. user_membership 增删查

新增使用者關系，删除使用者關系，查詢使用者關系

5. user_membership_rev 增删查

新增反向使用者關系，删除反向使用者關系，查詢反向使用者關系

6. 物化視圖

mv_user_membership 重新整理、查詢

mv_user_membership_rev 重新整理、查詢

聚合物化視圖主要是提高關系查詢效率用的，重新整理物化視圖，查詢物化視圖

1. 檢視執行計劃，sql是否下推到對應節點執行，是否隻通路目标資料節點。

以上sql滿足業務需求。

對于sql中帶的使用者新增的函數或其他條件，如果要下推，則必須使用extension來管理這些函數，master與所有的segment都需要建立這些extension，并且在建立server時包含這些extension。

文法見我上一篇講postgres_fdw的文檔。

1. 全局唯一序列

步調一緻，起始值不一緻。

由于master隻存儲了meta資料，是以master 都是對等的，如果master成為瓶頸的話，我們可以新增master節點，上層使用負載均衡的方式連接配接多個master即可。

通常master不會成為瓶頸，因為master不參與計算，對于9.6來說，單節點的master已經可以處理百萬級别的qps(基于pk的查詢)。

1. 外部表暫時不支援truncate

2. 外部表暫時不支援透傳upsert

簡單場景未包含需要在多個segment之間複制的次元表，小表。

下一篇将為大家介紹

二、複雜場景設計

三、單元化

<a href="http://info.flagcounter.com/h9v1">count</a>