PostgreSQL 優勢,MySQL 資料庫自身的特性並不十分豐富,觸發器和預存程序的支援較弱,Greenplum、AWS 的 Redshift 等都是基於 PostgreSQL 開發的

標籤：article   fabric   對象   rpo   大型   案例   安全   設計   實踐   

PostgreSQL 優勢2016-10-20 21:36 686人閱讀 評論(0) 收藏 舉報 分類：MYSQL資料庫（5） 

PostgreSQL 是一個自由的對象-關聯式資料庫伺服器（資料庫管理系統），功能很強大。包括了可以說是目前世界上最豐富的資料類型的支援，比如 IP 類型和幾何類型等等。

發現很多讀者都問過這樣一個問題：如果打算為項目選擇一款免費、開源的資料庫，那麼你可能會在MySQL與PostgreSQL之間猶豫不定。針對這個問題，我們採訪到了即將在Postgres中國使用者2016大會上作分享的平安科技資料庫技術部總監 汪洋，請他從實踐者的角度給出一些經驗分享和建議。

InfoQ：對於金融領域的應用來說，使用 PostgreSQL 與使用 mysql 相比，優勢有哪些？

汪洋：這個問題的本質迴歸到為什麼平安科技會選擇 PostgreSQL 來做為 Oracle 的替代品。

我們並不是沒有使用過 MySQL，事實上，從2013年底我們就開始引入和使用 MySQL 資料庫，比 PostgreSQL 還早了一年多。但最後，經過評估和實踐的檢驗，發現 PostgreSQL 對於大部分的金融應用情境更加適合，因此還是選擇了 PostgreSQL 資料庫做為科技的主流開來源資料庫。

這裡麵包含有多方面的因素。支付情境是金融領域裡較為受關注的部分。平安集團是一家綜合性金融服務企業，業務覆蓋了包括保險、基金、投資、證券等。保險業務就是最明顯的例子，商務邏輯極其複雜，即使是 OLTP 系統，也涉及對大量資料的訪問和計算。因此，很多商務邏輯都是在資料庫內部通過 package 或者 stored procedure 來實現的，這種處理方式對於 PostgreSQL 來說遊刃有餘。

我認為 MySQL 屬於 Thin Database，而 oracle 和 PostgreSQL 屬於 Thick Database。Thin Database 的概念在於輕資料庫重應用。換句話說，就是資料庫只做為資料的儲存，只提供簡單的查詢訪問。而複雜的商務邏輯前移到應用伺服器端來完成。MySQL 資料庫自身的特性並不十分豐富，例如 innoDB 儲存引擎只提供索引組織表形式的資料存放區格式，某種程度上限制了它的使用情境。對於觸發器和預存程序的支援較弱，並不建議使用。應用的 CRUD 操作盡量通過主鍵進行，雖然支援二級索引，但通過二級索引操作會有效能損失。在進行關係型資料庫中必不可少的表關聯操作時，只支援 Nested Loops 關聯方法，缺少對  sort merge join 和 hash join 的支援。當關聯表超過2張時，MySQL 的最佳化器有時產生的執行計畫不優，造成效能下降。

也正因為如此，我們制定了針對 MySQL 的開發規範，例如表的大小不要超過多大，盡量書寫簡單查詢，通過主鍵來訪問資料，不要寫超過2張表相互關聯的 SQL 等。MySQL 更加適合商務邏輯簡單的 OLTP 應用。而對於 PostgreSQL 來說，無論商務邏輯簡單還是複雜，OLTP 還是 OLAP 負載，PostgreSQL 都可以支援，也都有很成熟的產品，很多著名的 OLAP 資料庫產品例如 Greenplum、AWS 的 Redshift 等都是基於 PostgreSQL 開發的。PostgreSQL 的查詢最佳化工具非常強大，而且對於三種表關聯方法 Nested Loops，Sort-Merge Join 和 Hash Join 全部支援，對於複雜的 SQL 陳述式最佳化起來也不是問題，而這正是保險行業進行商務邏輯處理需要的特性。

PostgreSQL 還提供了強大的資料庫內部 function 支援，而且可以用多種語言編寫，對於複雜商務邏輯計算以及大資料量訪問完全可以在資料庫本地化實現，大大減少了網路互動成本，從而整體提升應用效能。

因此，也有人說 PostgreSQL 不止是一個資料庫，更是一個強大的開發平台，足見其功能之豐富。

我認為，PostgreSQL 的進程體繫結構與 MySQL 的線程體繫結構的不同點在於更加適合於垂直擴充。現在大家都在談分布式架構，橫向擴充，share nothing，我們不妨微觀上來看一下進程和線程架構的不同。以進程為主的體繫結構，在作業系統內部來看，就是一個分布式架構，每個進程有自己的地址空間，share nothing，只在需要互動的時候通過 IPC 機制 shared memory 和 semaphore 來進行通訊，大多數情況下都是在處理自己的事務，互不干涉；而以線程為主的體繫結構，共用地址空間，相當於 share everything，需要處理好線程間的關係以及對資源的存取控制。因此，建立進程的成本雖然相比建立線程高，但卻容易擴充。對於平安如此體量的企業，系統負載非常大，雖然橫向擴充非常重要，但垂直擴充能力也是不容置疑需要考慮的因素。而且 PostgreSQL 和 MySQL 的橫向擴充能力都需要中介層或者代理層來實現，並不是依靠資料庫核心完成，在這一點上兩者並沒有區別，至少現在如此。這樣資料庫核心的垂直擴充能力更加重要。如果說到橫向擴充技術，MySQL 有 Fabric 和 TDDL，PostgreSQL 近年在分布式上發展也非常之快，有 Postgres-XL，pg_shard，Postgres-XZ 等產品。

還有重要的一點。在談到 TCO（Total Cost of Ownership）的時候，大家會覺得 PostgreSQL 和 MySQL 兩者都是開源產品，並沒有差別。但實際上，TCO 除了可能的 License 成本之外，還包括學習成本和營運成本。對於金融領域來講，過去使用的都是 Oracle、DB2 等商務資料庫。之前 Oracle 和 DB2 這些商業化資料庫發展了幾十年，資料庫自身的特性非常豐富，和 PostgreSQL 都屬於 Thick Database，也難怪 PostgreSQL 被稱為開源領域的 Oracle。大家可以想一下，對於用慣了商業化資料庫的開發人員還是 DBA 來講，哪一種資料庫對於他們來講更加熟悉一些？學習成本更低？多年的經驗更加容易轉化，複用？營運成本更低？我想答案是顯而易見的，這也是為什麼平安選擇 PostgreSQL 的一個重要考量因素。

InfoQ：當資料量很龐大的時候，平安科技是怎麼利用 PostgreSQL 實現異地備份的？

汪洋：平安對於系統的高可用和資料安全是極其重視的。我們結合了多年積累的資料庫營運經驗，為 PostgreSQL 建立了 MAA（Maximum Availability Architecture）架構。我理解這裡的備份應該是廣義的備份概念，而不單指的是磁帶備份。我們對於 PostgreSQL 的磁帶備份是在本地的主庫上進行的。由於不支援直接上帶，所以先把備份寫入到 WOS（Web Object Scaler）分布式儲存上，然後再由 WOS 上帶。

由於在主庫備份會與應用爭搶 IO 資源，最初也評估過在本地從庫備份，但我們採用的 pg_rman 工具還是需要在主庫發起 begin backup 和 end backup 命令，考慮到網路影響，加之當前 IO 資源也比較充足，所以備份暫時還是在主庫上進行，後續會考慮將備份操作遷移到從庫。異地的Database Backup是通過搭建遠程容災庫實現的，使用 PostgreSQL 自身的 Streaming Replication 技術以非同步方式傳輸並應用 WAL 日誌來保持本地主庫和遠程從庫之間的資料同步。

這種情況下，主從之間的差異取決於日誌量產生的速度，與資料庫大小關係不大。當前，平安科技最大的 PostgreSQL cluster 是 3TB，每分鐘產生的 WAL 在 80MB 左右，在這樣的負載下，本地主庫基本上和遠程從庫保持准同步的關係。

InfoQ：使用開源的 PostgreSQL 資料庫，平安科技在保證系統穩定、效能方面做了哪些最佳化措施？

汪洋：確實，PostgreSQL 是一個開來源資料庫，我們在引入的時候對於系統的穩定性也尤其關注和重視。加之平安是一個綜合金融服務集團，業務範圍涵蓋金融行業的方方面面，各個業務線之間也有複雜的相互調用關係。如果一個系統出現問題，經常會有“牽一髮而動全身”的影響。不僅可能會造成經濟損失，也會影響聲譽，所以在系統高可用、可靠性和穩定性上面我們做了大量工作。

最初我們採用的一主兩從的高可用架構。本地一主一從，遠程一從，大家都使用本地磁碟。這樣，當主要資料庫發生問題，可以快速切換到本地從庫繼續提供服務。可很快我們發現，這樣在故障切換時有可能會發生資料丟失！對於某些金融應用來講，資料丟失是不能接受的。由於考慮到主從之間的網路延遲會導致 OLTP 應用效能下降，主從之間並沒有採用同步複製（儘管PostgreSQL支援，這也是優於MySQL的地方）。

由於主從之間的強一致性沒有保證，所以切換時可能會發生資料庫丟失，影響 RPO。同時，使用本地碟使得計算和儲存強耦合。資料顯示，系統出現問題更多的是 CPU 和 RAM 而不是儲存。這樣，即使不是儲存問題，CPU 或者 RAM 的故障也會引發切換導致資料丟失。因此我們對架構進行了最佳化，一方面從使用本地碟切換為使用共用盤，另一方面為 PostgreSQL 建立了主備叢集。這樣，在伺服器發生故障時，叢集可以快速將 Postgres 執行個體從主機切換到備機，並且因為使用的是共用盤，資料的一致性和完整性得到保障，不會有絲毫的資料丟失。

從效能上來看，共用盤也提供了比本地碟更好的 IO 效能，無論從 IOPS 上還是輸送量上。在每一次的架構最佳化調整時，不止從理論上論證其可行性，也都是經過嚴格的測實驗證後才會實施。這點也體現在 Linux IO 調度器的選擇上。linux 核心在 IO 調度上有四種策略，分別為 NOOP，CFQ（Complete Fairness Queueing），Deadline 和 Anticipatory。我們分別針對機械磁碟和 SSD 進行了 IO 的基準測試，在測試中類比了資料庫讀寫負載 IO 的行為，包括隨機讀、隨機寫、順序讀、順序寫的不同組合，還包括對不同 IO 大小的測試，在各種組合下觀察監控不同 IO 調度策略下的效能資料。根據效能資料制定不同 IO 調度器設定規範。基於機械磁碟，CFQ 和 Deadline 表現優於其他兩種策略，但 CFQ 和 Deadline 之間差別不大，為了保持更大的適應性，選擇了 CFQ；而對於 SSD 快閃記憶體，經過測試 NOOP 和 Deadline 要由於 CFQ 和 Anticipatory，而且 Linux 7 核心已經預設使用 Deadline，因此在 SSD 上我們採用 Deadline。

對於開來源資料庫來講，監控自然也是必不可少的。我們所有的資料庫都採用統一的監控架構 Zabbix，極大降低了營運成本。根據 PostgreSQL 自己的特性，結合了多年 Oracle 營運的經驗，定製了很多監控指標。例如對慢查詢的監控，對於剩餘事務年齡的監控用於判斷是否需要及早進行 vacuum，以及對 checkpoint 效率的監控等。為了對每一個 cluster 的效能有一個全面的瞭解，方便進行效能趨勢分析和定位問題發生的異常時段，我們借鑒了 Oracle 中非常優秀的 AWR（Automatic Workload Repository）概念，自己開發了 PGAWR 資料庫效能報告工具。裡面不僅有 OS 的效能資料，也有 DB 整體的負載情況和效能資料，還有按照不同維度排序的 SQL 陳述式列表，可以對 DB 極其運行主機的情況一目瞭然，快速診斷問題和發現異常 SQL 陳述式。

種種，都是為了能夠讓 PostgreSQL 運行地更穩定，在問題發生時能夠快速定位、分析並加以解決，滿足金融行業對系統運行和恢複時效的需要。

InfoQ：能不能介紹一下平安科技 PostgreSQL 開來源資料庫的架構設計，以及有哪些規範？

汪洋：從高可用架構上來看，對於 PostgreSQL 資料庫分為兩個層面，首先是主機故障，其次是儲存故障。之前提到，大部分的問題是由主機故障導致，儲存故障只佔很少一部分。由於主機和故障發生機率的不對等性，我們進行了計算和儲存的解耦。為了防範主機故障，我們採用共用儲存，並且建立 Linux 伺服器叢集在主機發生問題時快速切換。由於計算和儲存解耦，也更加方便對主機進行主動維護，大大降低了資料丟失的可能性。雖然儲存故障發生機率小，但影響面卻很大，所以我們也沒有忽視對儲存故障的防護。在本地和遠程我們都建立了 PostgreSQL 的從庫，它們和主庫之間都採用非同步複製策略。如果主庫儲存發生問題，短時間無法恢複，就會切到本地從庫繼續提供服務；如果發生地區性問題，則切換到遠程繼續提供服務。實現兩地三中心的防護。

除了高可用架構規範，在 PostgreSQL 架構規範中我們還定義了：

1. 生產環境所採用的 PostgreSQL 版本號碼，以及 PostgreSQL 的 Patch 策略和升級策略；
2. 伺服器和作業系統使用者的命名規範；
3. 資料庫應用使用的字元集設定；
4. 每一個 cluster 的卷和目錄命名規則；
5. PostgreSQL 資料庫的標準參數設定規範。

此外我們還制定了使用者架構規範，在規範中，定義了 PostgreSQL cluster 都應該有哪些標準的初始化使用者，例如 DBA 使用者，部署專用使用者，複製使用者，監控使用者等。對於每一個 Database，建立屬主使用者以及應用串連使用者，以最小化許可權原則授予應用串連使用者必需的許可權，規避進行危害性操作的風險。對於運營人員，在規範中也有明確的定義，按照職責劃分建立不同的專屬使用者。這樣，實現 DBA、運營和開發人員的許可權分離，一方面最小化許可權將對資料庫的可能危害降到最低，另一方面便於後續的審計和追蹤。

對於資料庫系統的健康、穩定運行，開發規範一樣重要。在給開發人員制定的開發規範中，我們分別從命名規範和設計規範兩大方面進行了描述。命名規範包括對錶、欄位、索引、視圖等資料庫物件的命名要求，而設計規範包括對錶、索引、分區表在設計時的一些原則的描述。通過這些規範要求，讓開發人員可以相對簡單地進行應用系統的設計，降低系統運行時的效能風險。而且，我們還將這些規範整合到了資料庫自動化審計平台中，例如在版本提交階段自動審核心數據庫版本是否合規，SQL 陳述式是否包含全表掃描，隱式轉換等高風險操作等，進一步加強了品質控制，降低了系統上線後的風險，簡化了營運工作。

InfoQ：在後期營運過程中對架構、規範進行了怎樣的最佳化，引入了哪些新技術？目的是什嗎？

汪洋：PostgreSQL 畢竟在平安引入的時間不長，到今天也差不多1年半的時間。雖然，我們制定了 PostgreSQL 的推廣策略，按照系統複雜度、系統重要程度分層級逐步推廣，但在推廣使用的過程中，還是會遇到這樣那樣的一些問題。對此，我們要求每一個問題都要找到根本原因，只有這樣才能有的放矢地不斷調整和最佳化。

之前提到的從最初的使用主從架構和本地磁碟調整到使用 Linux 伺服器叢集加共用儲存就是一個明顯的例子。在這裡，我想再分享一個例子。有一次，在啟動並執行一個資料庫上，發現產生的 WAL 日誌非常之快，資料庫本身並不大，只有 100GB，但每小時產生的 WAL 卻可達到 300GB 之多。這是一個典型的小庫高負載的案例，通過詳細分析發現，雖然庫本身負載很高，但也和未經最佳化的 checkpoint 相關參數配置相關。負載高和 checkpoint 的配置形成了惡性迴圈，導致 WAL 產生量巨大，造成系統磁碟空間告急此類可用性風險。雖然調整過一次參數，但因為未找到根本原因，並沒有從根本上解決問題。隨著越來越多高負載的重要資料庫使用 PostgreSQL，對於以後繼續推廣存在較大隱患，必須找到根本原因，徹底解決問題。經過對 WAL 產生原理進行研究，對日誌進行分析，甚至對 PostgreSQL 源碼進行理解，終於對 WAL 產生機制有了較為清晰的瞭解。並針對性地制定了參數調整策略和未來使用新版本時的規範。問題主要和兩個參數相關，一個是 wal segment size，一個是 checkpoint_segments。預設值設定較低，wal_segment_size 是 16MB，checkpoint_segments 是 32，不太適合運行金融系統的高負載應用。由於 wal segment size 只能在 PostgreSQL 軟體編譯時間調整，所以對於現有系統，只能調整 checkpoint_segments。對於這個特定的資料庫，第一次在沒有找到根本原因的情況下將 checkpoint_segments 調整到了 128，但沒有從根本上解決問題。在分析後，調整到了 512，WAL 產生量大幅減少，達到了預期的效果。在未來使用新的 PostgreSQL 版本時，我們調整了規範，在編譯時間將 wal segment size 調整為 64MB（預設的4倍），並且根據系統的負載情況靈活調整從9.5開始引入的參數 max_wal_size，使得系統可以更加穩定地運行金融系統應用。

另外，隨著 PostgreSQL 對 JSON 的支援越來越豐富，越來越成熟，我們也開始在 PostgreSQL 上推薦使用 JSON 資料類型。特別要提到的是，在關係型資料庫領域，PostgreSQL 的 JSON 支援要比 MySQL 和 Oracle 都早，MySQL 從5.7開始支援 JSON，Oracle 是從 12cR1 將對 JSON 的支援 backport 到了 11.2.0.4，PostgreSQL 則走在了前面。和 Document DB 領域中原生支援 JSON 資料類型的佼佼者 MongoDB 比較，經過測實驗證，我們發現 PostgreSQL 的效能並不差。而 mongodb 由於屬於 NoSQL，並不支援表（或者說 collection）之間的關聯，PostgreSQL 恰好填補這方面的空缺。對於一些有 JSON 文檔儲存需求，但又需要和關係型或者結構化資料相關 App情境 PostgreSQL 無疑是非常適合的一款資料庫。

當然，我們對於 PostgreSQL 還在不斷地學習，不斷地探索。但隨著對它的認識越來越深入，我們的信心也在不斷增強。特別是前不久 PostgreSQL 9.6 的發布，裡面的很多新特性更是讓人期待，也讓大家看到了 PostgreSQL 的未來！

InfoQ：通過 PostgreSQL+金融的完美結合這一案例，你有哪些心得感想可以和 InfoQ 的讀者分享的？

汪洋：2015年可以稱之為平安的 PostgreSQL 元年，從2014年底開始接觸 PostgreSQL，到2015年初開始立項準備、調研、評估、測試、制定規範、制定推廣策略，到真正的使用推廣，到現在有超過1000個 PostgreSQL 的執行個體在運行，一路走來，既為取得的成績而高興，同時又感覺殊為不易。但今天我可以告訴大家，當初的選擇是正確的，我很慶幸選擇了 PostgreSQL 這樣一款優秀的資料庫產品，並且在團隊的努力下能夠讓 PostgreSQL 在平安紮根，發芽並且茁壯成長。過程中，不是沒有人質疑過，但我們相信自己的判斷和選擇，更重要的是我們相信 PostgreSQL 產品本身能夠協助我們實現支撐金融系統應用的穩定運行，降低資料庫運營成本，擁抱開源技術，達到資訊自主可控的目標。於是，我們耐心地說服，全力地配合，悉心地指導，以獲得大家的認同。漸漸地，事實和資料也證明了 PostgreSQL 是一款非常穩定的高效能資料庫產品，能夠運行金融層級的應用系統。

每一種資料庫產品都有它的應用情境，每一家公司也都有著不同的資源，在選擇一款資料庫產品時，不能隨大溜，跟潮流，一定要根據自身的情況，對包括未來資料庫技術的發展，業務的特性，金融監管的要求，公司人員具備的技能等做出全方位的分析，進而選擇一款最適合自己的資料庫。而且，在資料庫技術蓬勃發展的今天，企業可能也會根據不用的應用情境選擇多種資料庫產品，用以滿足存放和訪問不同資料類型的需要。這本身也是一個尋找最佳化配置的過程，實現價值的最大化。

還有一點我覺得很重要，就是每一種資料庫，都要按照它原本的設計思想和設計目標去使用它，而不能把它當成黑盒子。首先要去瞭解它，然後再去適配它，只有這樣才能達到最好的磨合效果，才有可能將效能發揮到極致。反之，則會出現強扭的瓜不甜，導致效能問題。

雖然平安科技的 PostgreSQL 應用規模已經很大，取得了不小的成績，但平安在 PostgreSQL 資料庫上還有很長的路要走。例如如何使用分布式架構來提升 PostgreSQL 的橫向擴充能力，支撐 VLDB（Very Large Database），不僅可以運行 OLAP 負載，也可以運行 OLTP 負載；還有如何豐富 PostgreSQL 的效能資料和診斷資料，進一步提升在問題發生時的分析、定位以及解決時效，將對系統的影響程度降到最低。

這些都是我們接下來面臨的挑戰，對於象平安這樣的綜合金融服務集團挑戰尤其嚴峻。但可喜的是，我們看到 PostgreSQL 的發展越來越成熟，功能越來越豐富，很多我們希望的特性要麼已經被實現，要麼就是已經被納入規劃。PostgreSQL 的社區也在不斷壯大，日趨活躍，不斷有新的公司開始採用 PostgreSQL。在這樣的大好形勢下，相信 PostgreSQL 一定可以鞏固在開來源資料庫領域的領導地位，不僅成為最先進的開來源資料庫，而且成為金融服務行業最強大的開來源資料庫！

PostgreSQL 優勢,MySQL 資料庫自身的特性並不十分豐富,觸發器和預存程序的支援較弱,Greenplum、AWS 的 Redshift 等都是基於 PostgreSQL 開發的