我們是如何為算法交易系統進行數據庫選型的？

作者：Prerak Sanghvi 2020-02-05 17:43:14

所有軟件系統中都必須包含一項關鍵組件，那就是用于存儲、檢索和分析數據的數據庫。本文中，我們將和大家一起探討適用于算法交易平臺的數據庫都會有哪些特性？有哪些可選擇的數據庫？

從廣義層面來看，數據庫提供了記錄和管理數據（OLTP）和分析數據（OLAP）的能力。大多數數據庫會擅長其中一種能力，同時在某些指標具備優勢，而在另一些方面則有所欠缺。舉例來說，擅長一致和持久事務的關系型數據庫可能在性能方面表現不是很好，因為它需要鎖定其數據結構并刷新所有磁盤寫入。相反，優先考慮性能的數據庫可能需要使用寬松的一致性模型。

根據特定功能和特性的優先級劃分，不同種類的數據庫適用于不同的場景。

1. 算法交易系統的數據存儲

如果我們想追求完美，即持久、一致地存儲大量數據并快速進行實時分析，這能做到嗎？盡管計算機科學理論警告我們不要太貪心，但是有一些工程思想值得參考。

主要設計目標包括：

快速將大量事件提取到持久存儲中（每天工作數小時，約 250K-1M 個事件 / 秒；我們預期每個事件約為 100-200 字節，并具有 10-30 個字段）
實時分析，包括匯總
能夠處理大量模式和趨勢歷史數據

基于以上的設計目標，我們可以構建幾種解決方案，不過基本上都需要對數據存儲進行分層，以提供多種附加能力。

其中的一種解決方案為：

將交易系統中的所有事件存儲到一個快速數據存儲中，例如僅附加文件日志（可能在高可用性系統的多個節點上復制或重新創建）。該文件提供持久存儲，但沒有真正的查詢功能。
將該日志文件的內容順序提取到一個內存數據庫 / 緩存中。這個步驟可以很快，甚至是實時的，因為在這一層沒有一致性檢查、復制或持久性要求。該層應提供實時聚合和分析。
定期將內存數據庫的內容持久存儲到磁盤（每小時或一天結束時等）。這里使用可以對磁盤上存儲的大量數據進行操作的數據庫。本質而言，對存儲的這些數據進行的操作被視為脫機或批處理模式，并且不期望瞬時響應時間。
（可選）僅將日志文件的相關部分提取到一個關系型數據庫中，以提交每日 / 每月報告。例如，只有訂單和執行會被加載到關系型數據庫中，而市場報價會被跳過。

另外，這個解決方案也是可以簡化的，例如可以將步驟 2、3 和 4 組合起來，使用一個提供多種存儲和分析數據模式的工具。

接下來，我們就來一起討論一下細分需求下的數據庫工作。

2. 我們對數據庫的要求

我們對數據庫的要求可分為非技術需求和技術需求兩部分。

非技術需求列表：

成本：作為一家注重成本的初創企業，我們正在尋找免費或相對便宜的產品。鑒于當今有許多 FOSS 方案，我們認為這不是什么瘋狂的要求。這也意味著 Oracle 和 MS SQL Server 等標準付費數據庫被排除在外了。

良好的文檔和社區支持：如果我們不支付許可和支持費用，就需要良好的文檔和另一種解答問題的途徑。可行途徑可以是郵件列表、活躍的在線社區，也可能只需 StackOverflow 即可。

運營工具：我們更喜歡相對成熟的產品，自帶用于設置、管理和監視部署（包括可能的多節點集群）的工具。

技術需求：

快速提取：我們需要數據庫能夠以每秒 250K 次插入的速度提取，越高越好。如果我們需要批量插入，那是可以接受的；如果我們需要使用多個線程或連接也可以。

快速聚合：我們打算在系統中使用事件源模式。按照這一架構模式的規定，我們會將系統中的所有狀態更改記錄為離散的不可變事件。為了從這些事件中重新創建系統的最新狀態，我們需要對內存中快速聚合的支持，包括窗口函數、upsert 和其他可能的橫截面聚合。

時間序列操作：支持諸如時間段、移動窗口聚合和 as-of joins 之類的操作。

表達力強的查詢語言：SQL 可以，但對于高級分析來說表達能力還是不夠。理想情況下，數據庫將使用帶有矢量化操作的函數語言支持數據訪問和處理。創建用戶定義函數或服務端腳本的能力也很有用。

內存中的表：用于快速分析工作數據集。

磁盤中的表：我們預期這一類別中的多數數據庫使用面向列的存儲。

數據庫應支持優化的磁盤數據布局，這會顯著提高性能。

數據按日期劃分并分段存儲，以便數據管理
對于每個分區，數據由 Symbol（交易代碼）跨多個節點分片，以實現并行性和冗余
在每個分區和分片中，數據記錄按（Symbol + Exchange，代碼 + 交易所）進行聚類，以方便順序讀取磁盤
最后，在每個聚簇鍵的記錄中，按時間戳對數據排序，以實現更快的時序操作
此外，可以將數據壓縮在磁盤上，以減少從磁盤讀取的數據總量
分層數據存儲：數據庫還可以支持分層存儲策略，將較舊的數據移動到速度較慢的存儲上，從而降低存儲成本。

下面是我們評估的數據規模估算：

每日數據增長：50–100 GB（未壓縮）〜1B 條記錄
歷史數據（最終）：100 TB（未壓縮）〜1T 條記錄

3. 如何進行測試？

我們所有的測試都是在單個或兩個 AWS 專用實例（m5n-2xlarge）上進行的。這些實例運行 Amazon Linux 2 AMI，包括 8 個 vCPU、32GB RAM 和 100–200GB SSD 卷。

我們知道，對于某些參與測試的數據庫來說，這些實例不算很大，尤其是在內存指標方面。但我們這樣選擇也有我們的考量，首先，我們認為這些資源足以進行我們想要的測試，其次，我們想了解在資源不足時，這些工具將如何降級或失敗。

在我們的時間限制內，我們盡了最大的努力來配置各個工具以使其發揮最佳性能，但是我們可能并沒有一直使用推薦的配置、硬件或節點數。我們也嘗試了遵循文檔并以最佳方式設置數據布局（例如分片方案）。

我們執行的實際測試包括：

加載一天的 NYSE TAQ 數據（20180730 的文件）。這會將 3500 萬筆交易加載到一個表中，并將 7.19 億個報價加載到另一個表中。我們不打算將此數據庫用于報價數據分析，但這肯定會成為一個很好的示例數據集。
對于每筆交易，在該交易所在的交易所中找到當前的報價。我們希望對單個繁忙的代碼（例如 SPY）的查詢將花費不到一分鐘的時間，對于所有代碼，我們希望查詢在 30 分鐘內完成。這是對查詢語言表示復雜聯接的能力，以及數據庫在合理時間內執行聯接能力的測試。
對于每個交易代碼，計算交易日每分鐘的交易數量、平均大小和交易量加權平均成交價。我們希望在整個交易表上花費的時間不超過 10 秒。
在交易日的每一分鐘計算每個交易代碼的 OHLC 條形。
計算交易日每個交易品種的時間加權平均價差。這是一個有趣的測試，其原因有兩個：1）確定報價的持續時間需要使用諸如 LEAD 或 next 之類的窗口函數；2）必須處理每個報價，因此這是對原始掃描速度的測試。

4. 備選方案

要說明一下，我們在 kdb+ 上擁有豐富的經驗，因此，我們對響應時間的預期大部分來自于這部分經驗。在原始單核速度方面，我們還沒有發現比 kdb + 更快的工具。但因為價格、陡峭的學習曲線和缺乏可操作工作等原因，我們沒有把 kdb+ 列在備選名單中。

平面文件（Flat File）

雖然數據庫是最常見的數據存儲，但是直接處理平面文件是真正關鍵的競爭優勢，因為它提供了存儲數據的最大靈活性。如今，有多種工具可以有效操作存儲在本地磁盤或 S3 存儲桶上的平面文件，例如：Python（帶有 Jupyter 的 Pandas）、Apache Spark、Amazon Redshift Spectrum 甚至 clickhouse-local。

我們在 AWS 上使用 Apache Spark 嘗試了 EMR（Elastic Map Reduce）集群，雖然設置起來相對容易，但我們仍舊花了一些時間才弄清楚如何從文件和 JDBC 源加載數據，以及如何使用 Spark 數據集和 PySpark 數據幀。我們的結論是，這可以用于具有適當擴展能力的批處理分析，但不能用作主數據庫。不過，我們對 Hadoop 和 Spark 的了解有限，因此對于結論判斷也會有所影響。

不過，我們仍然認為這是一個精心設計的系統，該系統以正確方式組織文件和目錄，還帶有相應的工具和規劃好的作業，對于能夠分配適當資源的高級用戶而言，這可能是一個可行的選擇。但是對于我們來說，我們認為它可能太脆弱且缺乏組織性，我們還需要其他一些花哨的功能。

MySQL

我們只把 MySQL 視為一個起點，主要是為了確認傳統的 RDBMS 對我們而言并不是真正的正確答案。MySQL 不是時間序列數據庫，也不是面向列的，并且不支持我們正在尋找的高級分析特性或性能指標。

它的優點是免費，還有龐大的社區。它的支持者會聲稱，只要你知道方法，它就可以做任何事情。在我們的測試中，MySQL（InnoDB 引擎）無法跟上連接池中 250K/ 秒的快速批量插入，并且隨著表增加到幾百萬條記錄，插入速率也下降了。磁盤上的數據大小看起來非常大，查詢幾百萬條記錄時的響應時間以秒為單位。即使可以添加索引，具有數百萬條記錄的聯接表也無法在可接受的時間內完成。

在校對本文的草稿時，一位前同事向我們推薦了 MariaDB 列存儲，由于時間限制，我們無法對其進行全面評估。

PostgreSQL 和 TimescaleDB

在我們的負載測試中，PostgreSQL 比 MySQL 更好，尤其是在插入速率和表大小增加時響應時間的退化水平方面，但對于實際需求而言還不夠好。

TimescaleDB 似乎很有競爭力——它是一個 PostgreSQL 擴展，使用大量常規 PostgreSQL 表創建一個稱為超表的虛擬表。在超表上的所有查詢和操作都向下傳遞到適當的塊表。這里的主要目的是提高插入速率，并在處理大量數據時提供可預測的查詢時間。TimescaleDB 還提供了一些與時間序列相關的功能，以幫助分析。

宣傳的效果很好，但實際跑起來就不行了。最初的插入速率很不錯（250K / 秒），但我們無法提取 3500 萬筆交易記錄——它莫名其妙地耗盡了內存。我們還注意到，文本文件加載器無法利用服務器上所有可用的內核。提取數據時，我們發現服務器上的 IOWait 時間比其他數據庫長得多，這可能是由于缺少磁盤壓縮所致。磁盤空間使用率也很高——存儲的數據比完全未壓縮的文本數據占用的空間還要多，這是很奇怪的（也許是因為預分配？）。我們知道最近的版本支持原生壓縮了，但是我們無法將其自動用于新提取的數據。

ClickHouse

ClickHouse 基本可以算是一個新玩家，幾乎擁有我們夢寐以求的所有特性：

它是 FOSS、速度超快、水平可伸縮、容錯、硬件支持良好，并且具有磁盤（包括分層存儲）上的高級數據管理；
開發過程非常透明，在 Github 上有活躍的社區，并且每 2 至 3 周發布一次更新，其中包含新功能、改進和修復；
文檔很好，很容易從維護者那里得到問題的答案。

ClickHouse 主要是一個 OLAP 引擎，沒有真正的事務支持可言——例如，它不支持插入數據的更新和刪除，除非通過笨拙的異步 ALTER TABLE 命令。它還不支持窗口函數（neighbor 和 runningAccumulate 這類特殊情況除外），這讓人有些驚訝，畢竟它主要針對的是時間序列。

我們在未啟用任何復制功能的單個節點上測試了 ClickHouse。ClickHouse 能夠以超過 1M/sec 的速度加載 3500 萬筆交易和 7.19 億筆報價。它使用特殊的磁盤數據結構（MergeTree）將數據盡快寫入臨時文件，然后在后臺合并，從而達到很高的速度。它永遠不會用完內存（只有一個例外），并且使用壓縮過的源文件節省了將近一半磁盤空間，效率極高。

遺憾的是，我們無法克服一些關鍵障礙：

發出查詢的唯一方法是使用類似 SQL 的查詢語言，但有一些嚴格的限制：每個請求只能發出一個選擇語句，并且不支持用戶定義函數（UDF）或存儲過程（Stored Procedure）。
他們的哲學可以概括為"只能聽我的"。維護人員對一些合理的用戶請求（例如支持日期時間數據類型中的亞秒級精度）給出了無法令人滿意的答復。公平地說，有些回應也有正當的理由，但是看到這些交流仍然有些令人不安。

總而言之，我們還是認為 ClickHouse 具有很大的潛力，將密切關注其發展，甚至我們會在系統中的非關鍵部分部署 ClickHouse。

DolphinDB

DolphinDB 是一種奇特的專用產品，在這次評估之前我們完全沒注意過它。這是一個快速的分布式時間序列分析數據庫，是 kdb+ 的可行替代方案。來自 kdb+ 的背景激發了我們的興趣，即便它是付費產品，也足以讓我們試用一下。

我們對它的總體印象是積極的。它比 ClickHouse 更快，甚至可能比 kdb+ 更快。它擁有對多節點集群的原生支持、功能豐富的函數式編程語言以及優化的內存上以及磁盤上的數據結構。它僅用 6 秒鐘就將我們的 3500 萬筆交易載入了一張表！它僅在 358 毫秒內就執行了所有 SPY 交易及其主要報價之間的 as-of join，在 25 秒鐘內對所有代碼執行了同樣的聯接，而在 kdb+ 上一次查詢大約需要 5 分鐘。另外，存儲數據的磁盤用量還不到壓縮后的源文件的一半。

它還有一些高級功能（我們未測試）包括：支持流和發布 / 訂閱、實時聚合 / 窗口引擎、實時異常檢測引擎、高級統計分析函數和機器學習函數

盡管它表現極佳，但仍有一些我們無法克服的負面因素：

成本：雖然它看起來比 kdb+ 便宜，但對我們來說仍然太貴了；
需要學習非標準語言（盡管比 kdb+ 容易得多），不過，好在它的文檔完整出色；
對于關鍵業務組件，我們真的可以考慮為（對我們而言）未經驗證且尚無法判斷其局限性的閉源產品付費嗎？讓人猶豫不決的是，它出現了幾次崩潰和莫名其妙的內存不足情況，這都是扣分點。

不過，看來我們可能已經發現了比 kdb+ 更快、功能更豐富的產品，這一點得分很高。我們將密切注意這款產品，如果對具有這些能力的產品（例如 tick 數據研究環境）有強烈的需求，我們一定會考慮它的。

MemSQL

現在要講的是，我們最終的選擇——MemSQL 了。MemSQL 是一種付費產品，但它也為初始集群提供了免費的商業許可證，其最多可包含 4 個節點、128 GB 內存和無限的磁盤數據。我們認為這足以滿足我們在考慮付費產品之前的初始需求了。

MemSQL 將自己定義為名為 HTAP（混合事務 / 分析處理）的新數據庫種類。MemSQL 的主要賣點有：它提供快速的分析功能，同時具有豐富的事務支持并充分兼容 SQL。它甚至可以與 MySQL 兼容，因此你可以使用所有 MySQL 工具和驅動程序。與龐大的工具生態系統集成是很棒的，但也存在一些障礙，因為它很難使用純 SQL 表示某些高級分析。由于它以 UDF 和存儲過程提供了對過程語言的全面支持，我們接受了這一特殊缺點 [注意：過程方法比通常的矢量化操作至少慢一個數量級]。

MemSQL 支持內存上行存儲表以及磁盤上列存儲表，帶有分片、排序和壓縮功能（它們最近還發布了混合單存儲格式。我們僅使用 Columnstore 進行了測試，特別是考慮到我們的測試實例只有 32GB 內存。就部署、管理、監視、集群設置甚至數據的加載和查詢而言，MemSQL 是最容易使用的工具之一。

我們能夠以超過 50 萬條記錄 / 秒的速度加載交易和報價。我們注意到，服務器上的加載過程能夠使用多個內核并行化提取。加載的數據占用的空間與壓縮后的源文件大致相同。我們還觀察到，使用 JDBC 接口時，外部工具能夠以超過 1Gbps 的速度從 MemSQL 讀取數據，這特別令人印象深刻。

大多數單表查詢以及多表聯接查詢的整體性能都很好。它在 as-of joins 中表現不佳，但畢竟它根本不是針對該用例設計的。我們花了很多時間試圖以最佳方式在 SQL 中表示一個 as-of join，最后我們強迫引擎執行（相對）快速的 MergeJoin。可以預期，廠商將來可以作為自定義操作添加對 as-of-join 的專門支持。

總而言之，MemSQL 是我們在調查中可以找到的最平衡解決方案。它很成熟、易于使用、免費（暫時）、快速、高效且可與我們想要的所有標準工具互操作。