內容來源：2017 年 10 月 20 日，蘇寧雲商IT總部資深技術經理陳華軍在“PostgreSQL 2017中國技術大會”進行《蘇寧citus分佈式數據庫應用實踐》演講分享。IT 大咖說（微信id：itdakashuo）作為獨家視頻合作方，經主辦方和講者審閱授權發佈。

閱讀字數：5089 | 13分鐘閱讀

嘉賓演講視頻回放及PPT，請複製鏈接：http://t.cn/Rg5Wjrc，粘貼至瀏覽器地址欄即可。

摘要

本次分享主要介紹瞭如何通過Citus打造分佈式數據庫，對具體的部署情況進行了講解。

業務場景

上圖的系統架構主要是做訂單的分析，它會定時的從其他的業務系統中抽取訂單以及訂單的更新信息。每5分鐘進行一次批量的處理，更新10張左右的明細表。在數據庫中同樣也是5分鐘做一次處理，首先會對明細表進行計算，之後的計算結果會被放到報表中。架構外層還有一些其他系統，比如

這套系統中我們採用的數據庫是DB 2，平時的CPU負載都達到了50%左右，大促期間更是超過了80%，可以算是不堪重負。

DB負載在哪？

如此高的負載，到底問題是出在那些地方？其實主要是在明細更新、報表計算、報表查詢/明細查詢上。

明細更新時是5分鐘更新10張明細表，這其中最寬的表有400字段，大概每行2.5kB。每次更新最寬的表約10w記錄，總體上是30w。我們還要保持最近數天的數據。這樣看下來其實主要的壓力是在隨機更新，換算一下大概每秒要做5k條記錄的更新，關鍵是這 5K條記錄還都是寬表。

報表計算也是每5分鐘計算30多張報表，要求2分鐘完成，每個報表平均執行14次明細表集合查詢。估算下來大概是每分鐘200次明細表的聚合運算。

報表查詢/明細查詢中要求的併發度是大於30，但正常情況下沒有這麼高，大概只有10個左右。同時要求的響應時間要小於3秒。

由於我們的系統接入的業務需要擴張，預計年內負載還會增加10倍，也就是說原先的每秒5k的明細表隨機更新和3000w明細表數據，將提升為每秒5k的明細表隨機更新和3億明細表數據。

這樣的背景下基於單機的DB 2肯定是搞不定的，我們需要的應該是一種分佈式方案。

方案選型

上圖列出的就是我們當時所考察的各種方案，因為PG在分析上還是比較有優勢，所以這些方案都和PG相關。第一個Greenplum由於已經比較成熟了，所以我們一開始就比較看好，但是它更新慢、併發低的缺陷，不符合明細更新的性能要求，因此被排除在外。第二個postgres_fdw由於不支持聚合下推和並行查詢，所以不符合明細表查詢性能要求。第三個PG_XL方案我們並沒有做深入的評估，但是GMT對性能是有影響的，估計很難滿足我們對隨機更新的需求。最後的citus的優勢在於它是一個擴展，穩定性和可維護性都比較好，同時分片表的管理也很方便，最終我們選擇的就是這個方案。

Citus介紹

Citus架構與原理

這張是Citus的架構圖，可以看到它由1個maste和多個worker組成，數據表通過hash或者append分片的方式分散到每個worker上。這裡的表被分為分片表和參考表，參考表只有一個分片，並且每個worker上都有一份。

在應用訪問的時候master接收應用的查詢SQL，然後對查詢SQL進行解析生成分佈式執行計劃，再將子執行路徑分發到worker上執行，最後彙總執行結果返回給應用。

Citus主要適用於兩種環境，一種是實時數據分析，一種是多租戶應用。

案例演示

這裡演示的是Citus的使用過程。分片表的創建和普通表是一樣的，只不過完成之後需要設置分片數，最後執行create_distributed_table函數，參數為需要分片的表以及分片字段，還可以指定分片方法，默認是hash方式。參考表的不同在於函數換成了create_reference_table。這兩個函數主要做了兩件事，首先是在每個worker上創建分片，其次是更新元數據。元數據定義了分片信息。

元數據pg_dist_partition中存放的是分片表和分片規則，可以從圖中看到，h代表的hash分片，n表示的是參考表。分片表中有一個partkey，它用來指定哪個字段做分片以及分片類型。

元數據- pg_dist_shard定義了每個分片以及分片對應的hash範圍，不過參考表由於只有一個分片，所以沒有hash範圍。

元數據-pg_dist_shard_placement定義了每個分片存放的位置，第一列是分片的ID號，後面是所在的worker節點位置和端口號。

基於元數據master可以生成分佈式執行計劃，比如聚合查詢就會生成如上圖所示的執行計劃。上半部分是在每個worker上預聚合，每個分片並行執行，下面則是master對worker的結果做最終的聚合。

SQL限制—查詢

Citus最大的缺陷在於有著SQL限制，並不是所有SQL都支持。最典型的就是對Join的限制，它不支持2個非親和分片表的outer join，僅task-tracker執行器支持2個非親和分片表的inner join，對分片表和參考表的outer join，參考表只能出現在left join的右邊或right join的左邊。對子查詢也有著限制，子查詢不能參與join，不能出現order by，limit和offset。一些SQL特性Citus同樣不支持，比如CTE、Window函數、集合操作、非分片列的count(distinct)。最後還有一點需要注意，即本地表不能和分片表(參考表)混用。

這些限制其實都可以使用某些方法繞過，比如通過Hll(HyperLogLog)插件支持count(distinct)，對於其他的一些操作也可以通過臨時表或dblink中轉。不過臨時表的問題在於會將一個SQL拆成多個SQL。

SQL限制—更新

在更新上也存在一些限制，它不支持跨分片的更新SQL和事務，‘insert into ... select ... from ...’的支持存在部分限制，插入源表和目的表必須是具有親和性的分片表，不允許出現Stable and volatile函數，不支持LIMIT，OFFSET，窗口函數，集合操作，Grouping sets，DISTINCT。

當然這些限制也存在對應的迴避方法，首先是使用copy代替insert，其次是用SELECT master_modify_multiple_shards(‘…’)實現擴分片更新。

上圖展示的是對DDL的支持情況，這裡面大部分都是支持的，對於不支持的可以通過創建對等的唯一索引代替變更主鍵，或者使用`run_command_on_placements`函數，直接在所有分片位置上執行DDL的方式來進行迴避。

兩種執行器

Citus有兩種執行器，通過

默認的real-time又分為router和非router方式。router適用於只需在一個shard上執行的SQL，1個master後端進程對每個worker只創建一個連接，並緩存連接。非route下master後端進程會對所有worker上的所有shard同時發起連接，並執行SQL，SQL完成後斷開連接。

如果使用task-tracker執行器。Master是隻和worker上的task-tracker進程交互，task-tracker進程負責worker上的任務調度，任務結束後master從worker上取回結果。worker上總的併發任務數可以通過參數控制。

這裡對這兩種執行器進行了比較。real-time的優勢主要在於響應時間小。task-tracker則是支持數據重分佈，SQL支持也比real-time略好，同時併發數，資源消耗可控。

部署方案

痛點

我們的系統中首先面臨的痛點就是對隨機更新速度要求高。上圖左邊是Citus官方展示的性能數據，看似接近所需的性能要求，實際上遠遠不夠，因為這裡記錄的是普通的窄表，而我們的是寬表而且還有其他的負載。

圖中右邊是我這邊做的性能測試。單機狀態下插入速度是每秒13萬條，使用Citus後下降到了5w多，這主要是由於master要對SQL進行解析和分發。在嘗試對Citus進行優化後，使Citus不解析SQL，提升也不是很明顯。最後一種方式是不使用master，將每個worker作為master，這次的效果達到了每秒30萬條。

第二個痛點就是前面提到的SQL限制問題，雖然這些限制都有方法迴避，但是對應用的改造量比較大。

解決方案

這是我們最終的解決方案。首先對於插入和更新數據慢的問題，不在走master，直接在worker上更新。在更新之前會現在worker上查詢分片的元數據，然後再進行更新。

另外為了儘量減少SQL限制對應用的影響，我們採用的策略是儘量少做分片，只對明細表進行分片。應用在查詢的時候會將報表和維表做join，也會將明細表和維表做join，那麼這裡就會出現問題，因為本地表和參考表不能出現在同一個SQL裡。所以我們做了N份參考表，每個worker放一份，同時再將一份本地維表放在master上，由報表做join用，最後在更新的時候通過觸發器同步本地維表和參考表。

輔助工具函數開發

為了支撐前面提到的兩個策略，我們實現了兩個函數。pg_get_dist_shard_placement()函數用來批量獲取記錄所在分片位置函數。create_sync_trigger_for_table()函數用來自動生成本地維表和參考維表同步觸發器的函數。

連接池

因為業務對SQL的響應時間要求較高，所以我們使用的是real time執行器。但是由於real time存在的缺陷，因此我們在master上部署了兩套pgbounce連接池。一個在PostgreSQL前面，應用在連接PostgreSQL前先連接到pgbouncer。另一個在master和worker之間。

實際的使用的時候由於pgbounce不支持prepare語句，所以有些應用還是要直連到master。

效果

上圖是POC壓測的結果，基本上明細更新和報表結算滿足了性能要求。測試的時候我們使用的是8個worker，而在部署的時候其實是先部署4臺，然後再擴容到8臺。

日常維護

Citus的維護和普通的PG維護在大部分情況下區別不大，不過有些有時候DDL執行會無法分發，這時可以用它的一些公有函數來完成。

另外更新多副本分片表的途中worker發生故障，可能導致該worker上的副本沒有被正確更新。此時citus會在系統表pg_dist_shard_placement 中將其標識為“失效”狀態。使用master_copy_shard_placement() 函數就能夠進行恢復。

Citus對DDL、copy等跨庫操作採用2PC保障事務一致，2PC中途發生故障會產生未決事務。對每個2PC事務中的操作都記錄到系統表pg_dist_transaction，通過該表就能夠判斷哪些事務該回滾或提交。

踩過的坑

在實際的應用中我們並沒有碰到什麼大坑，主要是一些小問題。

第一個是由於master(real-time)到worker用的短連接，pgbouncer默認記錄連接和斷連接事件，導致日誌文件增長太快。後來我們將其關閉了。

第二個是master(real-time)會瞬間創建大量到worker 的併發連接，而默認的unix套接字的 backlog連接數偏低， master節點的 PostgreSQL日誌中經常發現大量連接出錯的告警。對此的解決辦法是修改修改pgbouncer的listen_backlog，然後硬重啟pgbouncer。

以上為今天的全部分享內容，謝謝大家！

注:本文內容基於較早的citus 6.x版，當前版本citus中“master”節點的名稱已改為“Coordinator”。另外，本文中描述的SQL限制，除join外大部分限制已經在7.2以後版本中被解除，

詳細參考：https://pan.baidu.com/s/1_tkfp9xLSQuwA2LEInQWTw。

閱讀更多 IT大咖說 的文章

關鍵字: 電子商務 SQL 蘇寧