TiDB 7.5.0 LTS 高效能資料批處理方案

發表於2024-02-19
TiDB

過去，TiDB 由於不支援儲存過程、大事務的使用也存在一些限制，使得在 TiDB 上進行一些複雜的資料批次處理變得比較複雜。

TiDB 在面向這種超大規模資料的批處理場景，其能力也一直在演進，其複雜度也變得越來越低：

○ 從 TiDB 5.0 開始，TiFlash 支援 MPP 平行計算能力，在大批次資料上進行聚合、關聯的查詢效能有了極大的提升

○ 到了 TiDB 6.1 版本，引入了 BATCH DML ( https://docs.pingcap.com/zh/tidb/stable/non-transactional-dml ) 功能，該功能可以將一個大事務自動拆成多個批次去處理，在單表基礎上進行大批次更新、刪除、寫入時能夠大幅提升處理效率，同時避免了大事務所產生的一些影響。

○ 而到了 7.1 LTS 版本，正式 GA 了 TiFlash 查詢結果物化 ( https://docs.pingcap.com/zh/tidb/stable/tiflash-results-mater...查詢結果物化 ) 的功能，使得 insert/replace into ... select ... 這種操作中的複雜 select 能夠利用 TiFlash MPP 並行處理的能力，大幅提升了這種操作的處理效能。

○ 前不久剛釋出的 7.5 LTS，正式 GA 了一個 IMPORT INTO ( https://docs.pingcap.com/zh/tidb/stable/sql-statement-import-... ) 的功能，該功能將原本 tidb-lightning 的物理匯入能力整合到 TiDB 計算節點上，使用一條 SQL 語句就可以完成大批次資料的匯入，大幅簡化了超大規模資料寫入時的複雜度。

TiDB 上之前有哪些批處理方案

  1. INSERT INTO ... SELECT 完成查詢和寫入

● 現狀：適用於小批次資料處理，效能較高

● 挑戰：大批次資料寫入時，會產生大事務，消耗記憶體較高

● 說明：寫入+單表查詢場景可使用 BATCH DML 功能自動拆批

  1. 針對 INSERT INTO/INSERT INTO ... ON DUPLICATE .../REPLACE INTO 這些 SQL 使用批次介面執行，降低應用與資料庫之間的互動次數，提升批次寫入時的效能

● 現狀：在合適的拆批方案、表結構設計上，處理效能非常高

● 挑戰：編碼不合理、表結構設計不合理時，可能會遇到熱點問題，導致效能不佳

  1. 透過 ETL 和排程平臺提供的資料讀取和寫入能力實現大批次資料的處理

● 現狀：主流的 ETL 平臺，如 datax、spark、kettle 等，在合理表結構設計時，效能也比較高

● 挑戰：多執行緒並行寫入時，也有可能會遇到熱點問題

  1. 針對上游傳過來的 csv 檔案的資料，使用 LOAD DATA 來完成批次資料的寫入，提升批次寫入時的效能

● 現狀：在對檔案進行拆分+多執行緒並行後，處理效能非常高

● 挑戰：當 LOAD DATA 一個大檔案時此時是大事務，導致效能不佳；多執行緒處理時也有可能遇到熱點問題，導致效能不佳

針對以上幾種批處理方案，以及最新推出的 IMPORT INTO 功能，我們開展了一次測試，探索哪種批處理方案效率最高，消耗資源更低，以及使用上更加簡單。

TiDB 中不同批處理方案的測試

1 測試環境

  1. TiDB 資源：3 臺 16VC/64GB 虛擬機器 + 500GB SSD 雲盤（3500 IOPS + 250MB/S 讀寫頻寬）

a. TiDB 版本：TiDB V7.5.0 LTS

b. TiDB 元件：TiDB/PD/TiKV/TiFlash（混合部署）

  1. 儲存資源：8C/64GB 虛擬機器 + 500GB SSD 雲盤（3500 IOPS + 250MB/S 讀寫頻寬）

● 儲存服務：NFS 服務、Minio 物件儲存

  1. 測試資源：8C/64GB 虛擬機器 + 500GB SSD 雲盤（3500 IOPS + 250MB/S 讀寫頻寬）

● datax + Dolphin 排程/java 程式/dumpling、tidb-lightning 工具以及 MySQL 客戶端

img

2 測試場景

將大批次查詢結果快速寫入到目標表，既考驗查詢效能，同時也考驗批次寫入的效能。

2.1 查詢部分：多表關聯+聚合

基於 TPCH 100GB 資料，擴充套件 Q10 查詢中的欄位和查詢範圍，返回 8344700 行資料。

select  c_custkey,c_name,sum(l_extendedprice * (1 - l_discount)) as revenue,
        c_acctbal,n_name,c_address,c_phone,c_comment,min(C_MKTSEGMENT),min(L_PARTKEY), 
        min(L_SUPPKEY,min(L_LINENUMBER),min(L_QUANTITY), max(L_TAX), max(L_LINESTATUS), 
        min(L_SHIPDATE), min(L_COMMITDATE), min(L_RECEIPTDATE), min(L_SHIPINSTRUCT), 
        max(L_SHIPMODE), max(O_ORDERSTATUS), min(O_TOTALPRICE), min(O_ORDERDATE), 
        max(O_ORDERPRIORITY), min(O_CLERK), max(O_SHIPPRIORITY), 
        @@hostname as etl_host,current_user() as etl_user,current_date() as etl_date
from
        tpch.customer,tpch.orders,tpch.lineitem,tpch.nation
where
        c_custkey = o_custkey and l_orderkey = o_orderkey
        and o_orderdate >= date '1993-10-01' and o_orderdate < date '1994-10-01'
        and l_returnflag = 'R' and c_nationkey = n_nationkey
group by
        c_custkey,c_name,c_acctbal,c_phone,n_name,c_address,c_comment
order by c_custkey;

源表資料量

img

2.2 寫入：29 列，1 個主鍵+2 個索引

CREATE TABLE `tpch_q10` (
  `c_custkey` bigint(20) NOT NULL,
  `c_name` varchar(25) DEFAULT NULL,
  `revenue` decimal(15,4) DEFAULT NULL,
   ...
  `etl_host` varchar(64) DEFAULT NULL,
  `etl_user` varchar(64) DEFAULT NULL,
  `etl_date` date DEFAULT NULL,
  PRIMARY KEY (`c_custkey`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_orderdate` (`o_orderdate`),
  KEY `idx_phone` (`c_phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

3 測試結果

img

img

4 測試分析

4.1 JAVA 程式使用 SQL 進行批處理

使用 JAVA 處理時，StreamingResult 流式讀取+多併發寫入方式能夠獲得非常好的效能。 強烈不建議使用 limit 分頁這種形式拆批 ，這種邏輯資料庫將執行 844 條查詢 SQL，效率極低，消耗的資源極高。同時 StreamingResult 這種流式讀取還可以使用於資料匯出的場景，對比使用 limit 分頁處理，效率也更高。

在程式 4 中，將原本查詢 SQL 裡的 order by c_custkey 換成了 order by revenue desc 後，對效能也有一定影響，原因主要是多執行緒寫入時 RPC 開銷嚴重放大。

在程式 5 中，將原本查詢 SQL 中的 c_phone 換成 '132-0399-0111' as c_phone，模擬索引熱點。

img

4.2 LOAD DATA 方式

如果使用 LOAD DATA 要獲得比較高的效能，建議對單個檔案進行拆分，同時 csv 中檔案的順序建議與目標表主鍵順序一致，如一個 CSV 檔案儲存 20000 行，再透過多執行緒並行來寫入，此時寫入效能也比較高。

如果僅 LOAD DATA 匯入單個大檔案，那麼效能較低，且消耗記憶體較高。

4.3 ETL+排程平臺方式

● 作業型別：datax(mysqlreader + mysqlwriter)，簡單，效率一般

排程平臺執行 datax 作業：使用 mysqlreader 方式讀取時，預設就使用流式讀取，但是對於多表查詢的 query 時，寫入時無法併發

● 作業型別：shell + datax(txtfileread + mysqlwriter)，較複雜，效率較高

○ 排程平臺執行 shell：使用 dumpling 匯出成多個 csv 檔案

○ 再排程 datax 作業：使用 txtfilereader + mysqlwriter，此時可以多執行緒併發寫入，效率較高

● 作業型別：SQL，簡單高效

○ 排程平臺執行 SQL：select ... into outfile

○ 排程平臺執行 SQL：import into

4.4 SELECT ... INTO OUTFILE 匯出查詢結果（當前僅支援匯出到檔案系統）

該功能大家平時可能使用比較少，但該功能非常有價值，它可以高效的將資料一批匯出、並且資料是完全一致的狀態，可以用於：

a. 批次資料處理：JAVA 程式可直接執行該 SQL 完成結果的匯出

b. 在簡單的資料匯出場景，使用匯出 csv 替換原本 limit 處理邏輯，應用將查詢結果匯出到一個共享 NFS/S3 物件儲存中，再讀取 NFS/S3 物件儲存中的 CSV，進行結果的處理，極大的降低了資料庫的壓力，同時效能將比之前使用 limit 分批處理更高。

4.5 IMPORT INTO 匯入 CSV（當前支援 S3 協議物件儲存以及檔案系統）

該功能 7.5.0 引入，極大的簡化了資料匯入的難度，JAVA 程式可直接執行該 SQL 完成 CSV 資料的匯入，在進行批處理時應用節點幾乎不需要消耗 CPU/記憶體資源。以下是使用示例：

IMPORT INTO test.tpch_q10 FROM '/mnt/nfs/test.tpch_q10.csv' with FIELDS_TERMINATED_BY='\t',split_file,thread=8;

需要注意的是：IMPORT INTO 匯入過程中，不會產生日誌，所以針對需要 CDC 同步或 Kafka 分發的場景，該方案不適用。

5 測試小結

img

部分測試程式碼示例 ： https://github.com/Bowen-Tang/batch-samples

總結與展望

TiDB 7.5.0 引入的 IMPORT INTO 功能，結合 SELECT ... INTO OUTFILE、以及 NFS/物件儲存，讓 TiDB 上增加了一種更加簡單且非常高效的批處理方案，JAVA 應用程式處理時更加簡單，ETL 排程也更簡單。

以下是 TiDB 使用 IMPORT INTO、SELECT ... INTO OUTFILE 的架構示例：

img

I MPORT INTO 功能當前僅支援 CSV 匯入，未來 TiDB 8.x 版本中 IMPORT INTO 將直接整合 IMPORT INTO ... SELECT ... 功能，極致簡化批處理操作，效能也更進一步提升（187 秒），敬請大家期待 ：

img

相關文章